How to sync linked server, jobs, alerts, logins, etc to a secondary serveur

  • 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!

  • What's the purpose of this actually ? Is it for HA or DR or something else ..

    --

    SQLBuddy

  • it is for DR

  • 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

  • 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!

  • 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

  • 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?

  • 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

  • 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


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • 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

  • 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?

  • 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

  • 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