Automating SQL Data Compare Operation.
JohnES
Posts: 10 Bronze 1
I'm working on a system to automate the routine synchronization (or partial synchronization) of two MS SQL databases using a project file. I'm pretty sure I can do what I want to, but I worry about the usual unexpected nuances that pop up.
Can anybody tell me if these assumptions are correct....
* We'll use a project file on a single command line to compare 2 databases and deploy the differences to the target database.
* We'll use Where clauses on certain tables to exclude some data from comparison. Basically, we only want to move new data, based on one of our date fields, and NOT remove older data from the second database.
* This operation will be run as a scheduled Windows task under the AD account of a special user.
* The special user will need a licensed copy of SQL Data Compare Pro edition.
* The process does not need access to the external internet.
Can anybody tell me if these assumptions are correct....
* We'll use a project file on a single command line to compare 2 databases and deploy the differences to the target database.
* We'll use Where clauses on certain tables to exclude some data from comparison. Basically, we only want to move new data, based on one of our date fields, and NOT remove older data from the second database.
* This operation will be run as a scheduled Windows task under the AD account of a special user.
* The special user will need a licensed copy of SQL Data Compare Pro edition.
* The process does not need access to the external internet.
Answers
That all sounds possible to me, with regards to point two, make sure to exclude in target to avoid losing data that only exists in the target database, I would advise doing a test by running the project in the GUI.
With regards to the license, Service Accounts can't use SQL Data Compare pro license as it requires interactive activation (unless you login interactively)
If you're unable to login interactively you will need a SQL Toolbelt license.
Hope this helps!
Kind regards
Dan Calver | Redgate Software
Have you visited our Help Center?
On the data, I'll keep an eye out for things going away that I want to keep. I believe I've proved out that how I have things works, using the UI. But, there's another check I can run to make sure.
On the license. I was afraid of something like that, and it's a major part of why I wrote this note.
I was hoping I could create a Windows task with a Run As for my special user. Or, worst case, have it run under the account of a user who has the required rights. Either way, it sounds like I really need to spend some time sorting things out.
And, how does the RG Data Compare command line app handle the situation where it can't phone home to check the license?
I assumed I would need to add a SN parameter or file somewhere. I've been running the RG UI on a server that recently had external internet access suspended. It complains about it but so far it's still worked.
John...
With regards to the license, you would use the SQL Data Compare command line from the SQL Change Automation folder from the SQL Toolbelt installer.
Here would be the example path:
C:\Program Files (x86)\Red Gate\SQL Change Automation PowerShell\SDC\SQLDataCompare.exe
Kind regards
Dan Calver | Redgate Software
Have you visited our Help Center?