none
T-SQL Statements to Stop and then restart mirroring

    Question

  • Hi All,

    I'm endeavouring to create an SSIS package to transfer logins between a principal and backup database in a mirroring scenario.  Because the mirroring is enabled, it will not let me copy onto the secondary database.  So please can someone tell me the statements to stop and restart mirroring so that I can put them at the start - and end - of the package routine?

    I tried pausing / resuming mirroring, but this did not work.

    Many Thanks
    Graham
    Friday, November 27, 2009 11:53 AM

All replies

  • Logins exist in the master database, so there's no need to do anything with mirroring. Just create the desired logins on the mirrored server using ALTER LOGIN (search for sp_help_revlogin and you will find how to include original SID and password for SQL logins).
    Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi
    Friday, November 27, 2009 3:55 PM
    Moderator
  • Hi Tibork,

    As you say , he can only create login in the master database and WILL not be able to map them as USERS in the mirrored databases as the databases in recovering mode will not be shown up in USERMAPPING page of LOGIN.

    Hi Graham,

    To stop the mirroring with TSQL , connect to either of the partner and run
    ALTER DATABASE <database_name> SET PARTNER OFF
    Now the mirrored database will also be online and you can create logins and map them to the mirrored database . Logins migration can be performed by whichever way you like (ssis package , sp_help_revlogin ).

    Restarting / reconfiguring mirroring has to be done manually though.

    Thanks, Leks
    Friday, November 27, 2009 8:06 PM
  • Hi Leks,

    <<As you say , he can only create login in the master database and WILL not be able to map them as USERS>>

    My point was to create the logins hving the same sid as in the originating SQL Server, so the mapping is already done. that was why I referred to sp_help_revlogin.


    <<To stop the mirroring with TSQL >>

    Once you stop the mirroring, you can't start it again (unless you restore a new backup and all work in that database is lost). I was under the impression that Graham want to add the logins and keep the mirroring running. In order to do that, Graham need to no stop the mirroring, but add the logins having the right SID in the first place.
    Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi
    Saturday, November 28, 2009 10:40 AM
    Moderator
  • Hi Tibork,

    Thanks for your explanations and i wasnt trying to point that your reply was wrong , so dont take it personal (I am always your blog's fan)..
    I was explaining him in a point of view when logins are created and usermapping are done for the first time b/w principal and mirror.

    Thanks, Leks
    Saturday, November 28, 2009 5:26 PM
  • Hi Leks,

    Nothing personal. I've found it help to try to be as precise as possible in forums. Partly because we deal with computer stuff, and partly because we don't have body language, voice pitch etc to assist in the communication. That means, however, that a comment might come out a bit dry, from time to time. :-)
    Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi
    Sunday, November 29, 2009 8:06 AM
    Moderator
  • One of the pre-requisites of setting up mirroring is to have the same logins that are used by the mirrored database created on the mirror instance as well. When you restore the database backup from the pimary instance, the database users would be present in the backup. So when you fail over, the mirror database (which is now the principal) should have the database users and the logins on the server mapped automatically.

    In case you are using SQL Authentication, then you would need to make sure that the SIDs for the SQL login are the same on both the servers. Please refer blog post below:

    http://blogs.msdn.com/sqlserverfaq/archive/2009/04/13/orphaned-users-with-database-mirroring-and-log-shipping.aspx


    This posting is provided "AS IS" with no warranties, and confers no rights. My Blog: Troubleshooting SQL
    Monday, November 30, 2009 2:39 PM
  • Hello All,

    Thanks All For your posts.  I've been unable to get on whilst out of the office over the weekend. What I will find most effective to use is Lekss's "ALTER DATABASE <database_name> SET PARTNER OFF" statement, but is there a reciprocal ALTER DATABASE <database_name> SET PARTNER ON - or similar, that I can issue to restart mirroring?

    This is only so I can issue the SSIS Package I have containing the SSIS "Transfer Logins" Task, to keep the logins list synchronised between principal and mirror.

    Many Thanks
    Graham
    Monday, November 30, 2009 4:02 PM
  • you cannot re-start mirroring once you stopped it. Make sure yo create the *logins* on the mirror server so they have the same SID as on the original server and you don't have to do anything in the database. See the URL that Amit posted.
    Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi
    Monday, November 30, 2009 4:08 PM
    Moderator
  • Thanks TiborK,

    I've read Amit's blog, but perhaps I'm missing something here.  Server Logins (windows Authentication in our case) are created at a Server Level, whilst mirroring occours at a database level.  So how do the logins transfer autonomously?  I understand that Roles, rights and permissions are at a database level - but not the logins. I'm really confused now!! :o)

    Many Thanks
    Graham
    Monday, November 30, 2009 4:37 PM
  • Logins are at the server (instance, acually) level. In the master database. See sys.server_principals. Users, however, are inside the database. See sys.database_principals. The are "connected" using the SID number.  Since you are on Windows authentication, you do't need to worry about the SID (assuming same domain), since it will be the same - it comes from Windows. And there's no pawword either. So, all you need to do is to create the logins (in the master database). Again, search for sp_help_revlogins - and then it is up to you how you want to (semi)-automate this.

    Mirroring isn't a set-it-and-forget-it technology. You are responsible for handling whatever dependecies the database has to the "outside world", including logins in the master database.
    Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi
    Monday, November 30, 2009 6:48 PM
    Moderator
  • I vote to TiborK. Just try the steps below to add the user.

    For windows authentication
    *) Add the domain login to the principal server
    *) Add the domain login to the principal database as user (this will be automatically replicated to the mirror database)
    *) Add the domain login to the mirror server and provide the same privilege

    For SQL authentication
    *) Create the SQL login in principal server
    *) Add SQL login to principal database as user (this will be automatically replicated to the mirror database)
    *) Using sp_helprevlogin procedure, script that particular login in principal server (this will script the login with same SID and password)
    *) Run the create login script in the mirror server to create the login with same SID

    That's it.


    Vidhya Sagar. Mark as Answer if it helps!
    Tuesday, December 01, 2009 11:48 AM
    Moderator
  • " *) Add the domain login to the mirror server and provide the same privilege
     "

    Hi All,

    I've quoted from above, the problem with this and the solution tabled by TiborK is that when creating a server login with either Windows or SQL Authentication you Have to provide a default database.  This poses me a problem as the mirroring process dis-allows writes to the secondary database, so I can create the user on the primary without problems, BUT when I try and create the user on the secondary, using either GUI, or SSIS, or SP_RevLogin script, I get the errors that I've already discussed.  And this is all because I can't define the user login's default database as the secondary mirror - which Is what I need to be able to do.

    Many Thanks
    Graham
    Tuesday, December 01, 2009 3:10 PM
  • This would be a one time task. A quick solution that I can think is:

    Before setting up mirroring, restore the database backup and bring the database online. Create all the logins whose default database is the mirrored database. After that restore the database with norecovery and setup mirroring.

    This exercise wouldn't need to repeated once mirroring is setup or after any failover.

    If anyone can think of a less cumbersome way of doing so, please reply.


    This posting is provided "AS IS" with no warranties, and confers no rights. My Blog: Troubleshooting SQL
    Tuesday, December 01, 2009 6:18 PM
  • <<when creating a server login with either Windows or SQL Authentication you Have to provide a default database.>>

    No, that is incorrect. There is no need to specify a default database when you create a login. If you don't spppecify a default database, then the default db will be master. But there is no problem to specify a default database, as long as you specify the one acting as a mirror database.

    So, apparently you try to specify the mirror database as a default database and this is what is causing your problem. Why do you need to set this database as default database for your logins? Are you saying that your application don't specify this database in the connection string? If so, you have very very badly behaving application. An application should *never* rely on the default database.

    I currently have mirroring running and I have no problems whatsoever to create the logins on the mirror server (as long as I don't specify the  mirror database as default database, of course).

    So, in short: Talk to the developers of your application and see whether you really nned to specify this as default database for the login. If that is the case, then slap them and tell them to fix the app!


    Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi
    Thursday, December 03, 2009 4:12 PM
    Moderator
  • I know this isn't really an answer for the question but Idera has a nice free utility called SQL permissions that is useful when you need to move logins from one server to another. http://www.idera.com/Products/Free-Tools/SQL-permissions/
    Friday, December 04, 2009 8:21 AM
  • One way to stop and start a mirroring using t-SQL (in somehow equivalent to what you needed of "SET PARTNER ON"), is to SET PARTNERS on both, the mirror and on the principal.

    First on the mirror:

    ALTER DATABASE DatabaseName SET PARTNER = N'TCP://ServerNamePrincipal:PortNumber'

    Then on the principal

    ALTER DATABASE DatabaseName SET PARTNER = N'TCP://ServerNameMirror:PortNumber'

    That should start the mirroring sesion.

    Or, suspend/resume on the principal

    ALTER DATABASE DatabaseName SET PARTNER SUSPEND

    ALTER DATABASE DatabaseName SET PARTNER RESUME

    The problem is you wanted to do that using a SSIC package. But, if you had acccess on the mirror and you can run a SET PARTNER command, you should be able to start the mirror sesion. This is just about what you needed, something equivalent "SET PARTNER ON.' About transferring logins, that is a different subject. I am not seeing you are asking about that.

    Monday, January 31, 2011 3:40 PM
  • To copy the logins do the following:

    1. Get the logins from the operational database and create a script to add them to the mirror
    2. Do a failover (this will activate the mirror)
    3. Run the created script to add the logins to the mirror database
    4. Do a failover again (this will restore the original mirror configuration)

    Regards
    Theo Verweij
    • Proposed as answer by atverweij Wednesday, August 29, 2012 9:47 AM
    Wednesday, August 29, 2012 9:47 AM