Keep synchronization of different databases
We are currently manually redirecting changes from our DEV SQL environment to TEST and production (using the Schema comparison in Visual Studio and also some script we create when we make changes to DEV), but this is very time consuming and error prone.
We were wondering if there is a better way to do this and how we need to implement it.
I read about the versioning possibilities (how does this work?) Or perhaps using the SQL RED GATES control (but can this be used to change TEST settings or is it only used to track local changes?)
We want a reliable way to update our TEST and Production servers so that no data gets corrupted / lost ... We are using SQL Server 2008 R2 and Visual Studio 2012.
We are starting a new project, so it's time for a change! Thank you for your time!
source to share
One easy way to do this is to have a simple version table in the db with one row and one column that stores the version number.
Now every time you push changes to dev, create an incremental sql script, have a master script that based on the current db version will call the necessary incremental sql scripts to update the schema to the latest version.
Be careful when dropping columns, changing column types, or shrinking columns, for example. varchar (100) to varchar (10) in your incremental scripts, as this can result in data loss if not properly planned.
The staged scripts need to be idempotent so they can be run over and over again, just in case, to handle the case when the db crashes during the upgrade.
source to share
While there are many benefits to using SQL Source Control (and I would love you to give it away as I am a product manager!), Its purpose is limited to versioning, not managing and deploying to yours in different environments. The right Red Gate tool for this would be the deployment manager.
http://www.red-gate.com/delivery/deployment-manager/
There is a blog maintained by the Deployment Manager project team that should give you an idea of where the tool is located:
source to share
Does the comparison schema in VS have a CLI? If possible, you can probably automate it several times throughout the day. If not, you can try using other third-party tools that support the CLI, such as ApexSQL Diff for schema and ApexSQL Data Diff for data synchronization.
source to share