Post reply

Pause Log Shipping

  • October 21, 2009 at 12:01 pm

    #139697

    Hello Experts

    I am new to SQL and I am setting up log shipping in one of our production servers. In the process of setting up, I have sucessfully executed the log shipping, all the backups, copy and restore is running good. Now, i am trying to create a ssis package for transfer logins, it shows an error "Database cannot be opened "___", it is in the middle of restore"

    Someone please help me out. It is very urgent.

    By the way, I am using sql 2005 sp3

    ALI

  • October 21, 2009 at 12:13 pm

    #1069036

    You will need to recreate the logins on the secondary log shipped server so the sids of those login match the users in the database.

    The users in the database (which will be read-only ) will need to match the logins on the server.

    Also the users will need to exist in the primary database too...and the shipped to the secondary standby server through the log ship process

    this article may help you.

    http://support.microsoft.com/kb/246133

    It talks you through recreating logins with the same sids on new servers

    Gethyn Elliswww.gethynellis.com

  • October 21, 2009 at 12:19 pm

    #1069042

    Thanks for the reply. My secodnary server is in no recovery mode.

  • October 21, 2009 at 12:22 pm

    #1069044

    Also, I have same versions of SQL and same editions

    ALI

  • October 21, 2009 at 12:30 pm

    #1069048

    Do I have any disadvantages of Disabling the log shipping job both in primary and secondary and execute the SSIS Pacakage. Actually, I need to schedule this as a maintenance task to update logins in the secondary server. So, how can a weekly maintenance plan get through. Please help me out. i feel like I am lost.

    ALI

  • October 21, 2009 at 2:27 pm

    #1069121

    If I'm not mistaken the logins will be created on the master database. I don't understand why you are having problems with creating logins.



    My blog

  • October 21, 2009 at 2:39 pm

    #1069129

    I have my database in no recovery mode. So, while running the ssis pacakage, it says, the database is currently being restored.

    Anyways, actually, I can run this pacakage at the time of disaster/Failover right? Can I just make sure that pacakage is running correctly and save it and document it in a detailed manner. Will it be OK.

  • October 21, 2009 at 2:46 pm

    #1069136

    As I told you earlier the logins will be created on hte master database and you should not get any errors by that. At least that is what I think. I will tested tomorrow just to be sure.



    My blog

  • October 21, 2009 at 3:36 pm

    #1069158

    Logins are not logged shipped. They live in the master database.

    User are located in the user database and will be logged shipped....Thus creating orphaned users.

    so in this case i guess we have database users with no associate logins.

    You can't create a login and map it to the read-only or recovering databases because the database is not accessible...you can not make it accessible and add the users to the login because that would break log shipping and the LSNs would be out of sync.

    Once log shipping is broken the only way to fix it is to re-configure and we end up back at square one.

    The only solution to this i can think of is thus.

    Ensure the users and permissions are set on the primary database. (read access will be all they get on the secondary database)

    The database users will then be log shipped to the secondary database

    You then need to re-create the logins (that already exist on the primary server) that map to the users on the secondary node following the instructions that are included in the link i sent earlier.

    That way the login are created on the secondary server with same SIDs as the users in the secondary databases...hence the logins map to the users in the secondary database. The users should then be able to access the read-only secondary database.

    I think...

    Hope this helps

    Gethyn Elliswww.gethynellis.com

  • October 21, 2009 at 3:40 pm

    #1069160

    PS I'm guessing you will struggle to run the SSIS copy logins ssis package at the time of failover...I'm guessing that the reason for the failover recovery will be because the primary server is unavailable...the ssis package to copy the logins will not be able to access the primary server.

    If you want to go down this route I would recreate the logins now on the secondary server and when the secondary server is brought online you could use the sp_change_users_login stored proc to fix the sids

    Gethyn Elliswww.gethynellis.com

  • October 21, 2009 at 3:45 pm

    #1069161

    I agree with GRE! Just create the users with the same sids on the master DB.



    My blog

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply