What are the challenges you face when working across database platforms? Take the survey
Options

Linked Servers

bhopenwbhopenw Posts: 39
edited October 21, 2008 9:50AM in SQL Compare Previous Versions
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

Comments

  • Options
    Out of interest, how would you ideally like for this problem to be solved?

    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
    David Atkinson
    Product Manager
    Redgate Software
  • Options
    David,

    You are right on when you say:
    Do you need a mechanism to parameterise these database names and replace with values on synchronization, depending on what the target database is?

    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
Sign In or Register to comment.