SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
alexandre.jobin
alexandre.jobin
Grasshopper
Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)

Group: General Forum Members
Points: 23 Visits: 192
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!
sqlbuddy123
sqlbuddy123
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2422 Visits: 2243
What's the purpose of this actually ? Is it for HA or DR or something else ..

--
SQLBuddy
alexandre.jobin
alexandre.jobin
Grasshopper
Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)

Group: General Forum Members
Points: 23 Visits: 192
it is for DR
sqlbuddy123
sqlbuddy123
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2422 Visits: 2243
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
alexandre.jobin
alexandre.jobin
Grasshopper
Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)

Group: General Forum Members
Points: 23 Visits: 192
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!
MyDoggieJessie
MyDoggieJessie
SSCertifiable
SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)

Group: General Forum Members
Points: 6728 Visits: 7394
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" ;-)
alexandre.jobin
alexandre.jobin
Grasshopper
Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)

Group: General Forum Members
Points: 23 Visits: 192
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?
MyDoggieJessie
MyDoggieJessie
SSCertifiable
SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)

Group: General Forum Members
Points: 6728 Visits: 7394
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 :-D

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" ;-)
Lowell
Lowell
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28730 Visits: 39980
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!

sqlbuddy123
sqlbuddy123
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2422 Visits: 2243
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search