April 2, 2014 at 8:53 am
hi everyone!
I guest that this question as been asked many times in the past but i havent been able to find my answer yet.
I have set up LogShipping for each of my databases on a secondary server. I have read here and there articles on how to sync logins but nobody talk about the other objects that need to be synced.
Is there a script, ssis package, etc, that can sync these objects?
- server logins
- linked server objects
- jobs
- alerts
- etc
would it be a good idea to configure LogShipping for all the System Databases since that a few of the objects above are stored in the System Databases?
thank you for the help!
April 3, 2014 at 1:28 pm
What's the purpose of this actually ? Is it for HA or DR or something else ..
--
SQLBuddy
April 4, 2014 at 8:01 am
it is for DR
April 4, 2014 at 8:15 am
Those objects have to created only once. Just script them out and create them on the DR server.
Regarding system dbs, just copy any user objects that were created in those DBs.
--
SQLBuddy
April 4, 2014 at 8:30 am
its true that i can create everything manually on the DR server but each time i do a modification on the primary server, i need to be sure to not forget the DR server. Changing password, new logins, new authorizations, new linked servers, etc.
if there's something to synchronize the work between the 2 servers, it would be great!
April 4, 2014 at 8:39 am
If you don't mind using SSIS - You can also use the transfer jobs, transfer logins, transfer SQL objects, and transfer master stored procedures tasks in SSIS to keep the 2 servers in sync
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
April 4, 2014 at 8:49 am
what do you mean by "If you don't mind using SSIS"? we have just finished to convert from Oracle so we havent had the chance to play with everything in the world of SQL Server. We do know what's the purpose of SSIS but if you ask this question, maybe it is because the tool is not loved by everyone. Is there something i should know? Is it good or is it bad?
and is there any fullproof ssis package that is ready to transfert everything to the DR server or i should start from scratch?
April 4, 2014 at 8:52 am
Not everyone "enjoys" using it, but it is pretty powerful - and I guess you could tell by my tone that I'm not particularly fond of it 😀
You would need to start from scratch with a new project.
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
April 4, 2014 at 10:49 am
MyDoggieJessie (4/4/2014)
If you don't mind using SSIS - You can also use the transfer jobs, transfer logins, transfer SQL objects, and transfer master stored procedures tasks in SSIS to keep the 2 servers in sync
i think there is a caveat about the ssis transfer logins task, right? i believe it disables sql logns and assigns a random password. That's why sp_help_revlogin is preffered.
I just did an upgrade from one server to another, and i scripted out every item, including mail setttings and stuff.
Lowell
April 4, 2014 at 11:26 am
alexandre.jobin (4/4/2014)
its true that i can create everything manually on the DR server but each time i do a modification on the primary server, i need to be sure to not forget the DR server. Changing password, new logins, new authorizations, new linked servers, etc.if there's something to synchronize the work between the 2 servers, it would be great!
I know it's a pain to do. But those server level objects don't change frequently. Just note the changes and replicate them to the DR server manually.
I don't rely on SSIS transfer Object\Login task as it gives lot of issues.
--
SQLBuddy
April 7, 2014 at 7:24 am
sqlbuddy123 (4/4/2014)
I don't rely on SSIS transfer Object\Login task as it gives lot of issues.
alright, i will do it manually. Maybe it should be another topic but what kind of issues can you have with SSIS? Are you talking about issues to transfert objects/logins with ssis or the issues is with ssis in general?
we are planning to use ssis for night jobs. What should i know about ssis? is it time consuming or i can rely on it to be maintenance free?
April 7, 2014 at 7:43 am
I've actually never had an issue with the SSIS task, from my perspective it works perfectly. What kinds of issues have you encountered sqlBuddy?
I set up the task:
LoginsToTransfer = SelectedLogins
LoginList = (Collection)
IfObjectExists = Skip
CopySids = True
Works like a charm. Albeit, I've never tested the accounts further than connecting to the DB and running a simple SELECT query, but it did work.
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
April 7, 2014 at 12:09 pm
alexandre.jobin (4/7/2014)
sqlbuddy123 (4/4/2014)
I don't rely on SSIS transfer Object\Login task as it gives lot of issues.alright, i will do it manually. Maybe it should be another topic but what kind of issues can you have with SSIS? Are you talking about issues to transfert objects/logins with ssis or the issues is with ssis in general?
we are planning to use ssis for night jobs. What should i know about ssis? is it time consuming or i can rely on it to be maintenance free?
SSIS is a pretty good tool when used for ETL tasks\jobs. You can definitely make a good use of it.
The only issue is with the Transfer Object\Login tasks which doesn't work as expected.
--
SQLBuddy
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply