How To Make Changes To An Already Deployed Application – The Right Way!

Quite often in the software development world, "new" projects do not necessarily start from scratch. As developers, we may come across projects which may have already been started and require additional functionality, or the functionality that they do have doesn't work quite right. Of course, we are willing to help in whatever way we can, however it's very important need to make sure that database schemas are kept in sync with each other to avoid loss of data, which is what the focus of today's blog post will be about.

When making changes to an application that is already “out in the wild” or deployed on a server, you need to ensure that the local copy of the database you have downloaded and the server copy are in sync with each other. Otherwise, it can cause a range of different problems (all of which can be avoidable by following simple steps!):

• Data loss by missing table columns that exist in one version and not the other. 
• Error messages caused by tables/views that exist in one version and not the other. 
• Incorrect operation of code that may or may not create an error caused by tweaks to Stored Procedures/Functions/Triggers. 

So how would we go about copying across changes that we have made to a database? During the example given, views were slightly different between one version and another however it is possible to update changes for every database object. There are a few ways to copy across:

You could just open the view or table in SQL Server and copy the query section, open a new query window and paste-run/execute.

However, we tend to create scripts by right clicking on the view in the Object Explorer panel and selecting:

> Script ... As > Create To > ... (whichever option you prefer, though we tend to use New Query Editor Window as it allows for modification of the code).  

You can create scripts for almost every database object, excluding Database Diagrams though as these are stored within a master database. However, this will only work for new tables and new views. If you run a CREATE To script for a table or View that already exists and you have only changed the columns, you will need to run an ALTER To script instead.

Next, use a SQL compare tool, which allows you to compare the two databases to display any differences between the two. At my current work place, we use Red Gate Compare Tool, which you can find at:

http://www.red-gate.com/products/sql-development/sql-compare/

You should ensure that only the changes that you are expecting between the 2 database instances are listed (this should be the case, if not then there is a major issue that should be dealt with separately).  Once satisfied, use the Synchronisation Wizard to bring the copies up to the same level.

Always save updates within an Updates folder so it is easy to find them. Never save over the top of old updates and keep multiple copies just in case something goes wrong and you have to roll back to a known previous stable state. Each update should be within its own date folder as well in YY-MM-DD format and labelled individually in chronological order. 

Refresh the page and hopefully, both copies should have the same number of identical objects with no modifications.

Now when you open SQL Server, both the regular folder and the test version of the folder should be the same.