4

At the moment we manually push changes from our DEV SQL environment to the TEST and production (using Schema compare in Visual studio, plus some script we create while making changes to the DEV), but this is very time consuming and error prone.

We were wondering if there was a better way of doing this and how would we need to implement this.

I've read about maybe using versioning (how would this work?), or maybe using RED GATES' SQL Source control (but can this be used to push changes to the TEST, or is it only used to keep track of local changes?)

We want a reliable way to update our TEST & Production servers so that data won't be corrupted/lost... We use 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!

Team-JoKi
  • 1,766
  • 3
  • 15
  • 23
  • You can also look at SQL Compare from Red Gate. From their website "Compare database schemas and deploy differences" – Adrian Fâciu Feb 14 '13 at 08:37
  • There's a good article on this subject at [The daily WTF](http://thedailywtf.com/Articles/Database-Changes-Done-Right.aspx). No kidding, go read! – user1429080 Feb 14 '13 at 11:48

3 Answers3

2

One simple way to do this would be to have simple version table in the db with one row and one column which stores the version number.

Now everytime you are pushing changes to dev, create the incremental sql script, Have a master script which based on the current version of the db, will call the necessary incremental sql scripts to upgrade the schema to the latest version.

Be careful of dropping columns, changing column types, or reducing columns sizes e.g. varchar(100) to varchar(10) in your incremental scripts, as that could result in data loss if not planned properly.

You incremental scripts should be idempotent, that they could be run over and over, just in case to handle the case when db crashes during upgrade.

ibondre
  • 63
  • 7
1

Although there are many benefits in using SQL Source Control (and I'd love for you to give it a go, as I'm the product manager!), its purpose is limited to versioning and not managing and deploying to your various environments. The correct Red Gate tool for this would be Deployment Manager.

http://www.red-gate.com/delivery/deployment-manager/

There is a blog maintained by the Deployment Manager project team here, which should give you an idea of where the tool is headed:

http://thefutureofdeployment.com/

David Atkinson
  • 5,759
  • 2
  • 28
  • 35
0

Does Schema Compare in VS have CLI? If so you can probably automate it to run several times during the day. If not you can try using some other 3rd party tools that support CLI such as ApexSQL Diff for schema and ApexSQL Data Diff for synchronizing data.

JdMR
  • 1,268
  • 14
  • 9