Linked Servers
bhopenw
Posts: 39
I am currently in the mist of using SQL Compare to automate the database syncs through our test regions to Production. I have successfully got the syncs working with TFS, Team build, and TFS Deployer (powershell) the only issue we are having is we are forced to excluded around 10 stored procs b/c they reference linked servers meaning the checked in versions point to Dev linked servers. Which are not accessible in Test, therefore, If we do not exclude them the sync fails.
Does anyone have any ideas on best way to deal with linked server when automating syncs between regions.
I have toyed w/ the idea of doing a replace before the sync starts via powershell b/c the .sql files really are only text based files but I see that as being a bit dirty.
thanks a million,
BJHop
Does anyone have any ideas on best way to deal with linked server when automating syncs between regions.
I have toyed w/ the idea of doing a replace before the sync starts via powershell b/c the .sql files really are only text based files but I see that as being a bit dirty.
thanks a million,
BJHop
Comments
One thing that we're adding in the next version of SQL Compare is the ability to specify inclusion/exclusion rules based on object types, object names and schemas. Currently, it is only possible to use exclusion rules using regular expressions with the command line.
Do you need a mechanism to parameterise these database names and replace with values on synchronization, depending on what the target database is?
David Atkinson
Red Gate Software
Product Manager
Redgate Software
You are right on when you say:
That is what I am looking for, I know I can write this pretty easily in powershell using a config xml that holds the linked servers for each test region. Currently, we exclude any stored proc that uses a linked server, this scares me b/c if any of these procs need to be changed it will be a manual action in each test region. (FYI we have 7 test regions here) Ideally, there should not be a need for manual actions once our automation is set up.
By the way I am very impressed with power of the cmd-line for SQLCompare, combo it with powershell and you got a very powerful Database migration automation platform.
I believe I will start coding up the search and replace pre-deployment scripts b/c I think this will be best way to achieve my goal of total automation.
I am still open to any and all ideas out there please don't be shy
BJHop