Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

How to sync linked server, jobs, alerts, logins, etc to a secondary serveur Expand / Collapse
Author
Message
Posted Wednesday, April 2, 2014 8:53 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Yesterday @ 6:56 AM
Points: 11, Visits: 142
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!
Post #1557534
Posted Thursday, April 3, 2014 1:28 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, September 11, 2014 2:28 PM
Points: 1,194, Visits: 2,219
What's the purpose of this actually ? Is it for HA or DR or something else ..

--
SQLBuddy
Post #1558222
Posted Friday, April 4, 2014 8:01 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Yesterday @ 6:56 AM
Points: 11, Visits: 142
it is for DR
Post #1558521
Posted Friday, April 4, 2014 8:15 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, September 11, 2014 2:28 PM
Points: 1,194, Visits: 2,219
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
Post #1558525
Posted Friday, April 4, 2014 8:30 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Yesterday @ 6:56 AM
Points: 11, Visits: 142
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!

Post #1558532
Posted Friday, April 4, 2014 8:39 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, September 18, 2014 10:38 PM
Points: 3,931, Visits: 7,160
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; They'll drag you down to their level and beat you with experience"
Post #1558542
Posted Friday, April 4, 2014 8:49 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Yesterday @ 6:56 AM
Points: 11, Visits: 142
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?
Post #1558551
Posted Friday, April 4, 2014 8:52 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, September 18, 2014 10:38 PM
Points: 3,931, Visits: 7,160
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; They'll drag you down to their level and beat you with experience"
Post #1558554
Posted Friday, April 4, 2014 10:49 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:07 AM
Points: 12,899, Visits: 32,111
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1558596
Posted Friday, April 4, 2014 11:26 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, September 11, 2014 2:28 PM
Points: 1,194, Visits: 2,219
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
Post #1558602
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse