March 14, 2018 at 3:08 pm
I am investigating setting up log shipping for sql server 2014 for a primary and single secondary. I have a script that will synchronize logins on the secondary and a job to script out jobs on the primary so in event of a failover the script can be run on the secondary. But in my scenario with the log shipping secondary in recovering mode I am concerned about what will happen when we fail over to secondary for the database users and permissions which will grow more and more different as users are added and permissions added/changed. I have a script to script out database users and their permissions which I can run on the primary every day. In the event of a failover and the secondary is brought online, is it reasonable to drop all the users in the secondary database and run the scripted permissions from the primary database. I could take a backup of the secondary before running the script. I would like some advice from dbas who have practical experience with log shipping failovers, ie how do most people achieve a successful failover with log shipping.
March 15, 2018 at 6:02 am
ceciliarenebaker - Wednesday, March 14, 2018 3:08 PMI am investigating setting up log shipping for sql server 2014 for a primary and single secondary. I have a script that will synchronize logins on the secondary and a job to script out jobs on the primary so in event of a failover the script can be run on the secondary. But in my scenario with the log shipping secondary in recovering mode I am concerned about what will happen when we fail over to secondary for the database users and permissions which will grow more and more different as users are added and permissions added/changed. I have a script to script out database users and their permissions which I can run on the primary every day. In the event of a failover and the secondary is brought online, is it reasonable to drop all the users in the secondary database and run the scripted permissions from the primary database. I could take a backup of the secondary before running the script. I would like some advice from dbas who have practical experience with log shipping failovers, ie how do most people achieve a successful failover with log shipping.
the database users and their permissions are stored in the database, these are transferred to the secondary via the log restores.
It's only the server level login and server level permission that's not synchronised
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
March 15, 2018 at 2:43 pm
thank you.
March 15, 2018 at 4:21 pm
The only thing you need to worry about is creating the SQL logins on the secondary with the same SID. If you grab the SID from the primary - create the login on the secondary with that SID, then when you recover the database(s) on the secondary the users will be associated with the login without any further effort.
You can create a script that runs in SSMS in SQLCMD Mode that creates the login on the primary, grabs the SID from the new (or existing) login - then connects to the secondary and creates the login on the secondary.
The only issues you have to worry about then are server level permissions that need to be granted like serveradmin, sysadmin, backupadmin, etc...
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
March 19, 2018 at 8:32 am
thank you
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply