Measure Your Database Table Schema Synchronization Safety While Using VSTS 2008
Microsoft Visual Studio Team System 2008 provides an excellent data synchronization tool, synchronize data and schema between two database. It saves lots of developer time to sync database objects. Among two possible synchronization techniques, VSTS 2008 uses the unidirectional synchronization technique. In this consideration, as safety measurement will be helpful for developers before start synchronization.
The security measurement is considered whether it would create any loss of data or not. Here few useful cases with respect to database table schema synchronization has been discussed one by one.
- New Table in Source Database: Safe. The new table will be added to the destination database.
- Remove Table in Source Database: Safe. The removed tabled in source database will also be removed from destination database.
- Modify Table in Source Database, Add New Field: Safe. However the new field needs to have the ‘allow null’ property as true.
- Modify Table in Source Database, Remove Old Field: Safe. The old column as well as the corresponding data in the destination database will be removed. However, as the old column has been removed, so the removal of corresponding data is expected.
- Modify Table in Source Database, Modify Old Field: Not safe. Data Loose in VSTS, as it does alter table add + drop field. To retain existing data due to sync process, external script would be useful. Below is some sample sql statement in this regard:
-- sql script to change data type of a table field, without data loss
alter table Contact
alter column Comment nvarchar(10) null
-- sql script to rename a table field 'Address' of a table 'Contact', to 'FullAddress' -- without data loss
EXEC sp_rename 'Contact.Address', 'FullAddress', 'COLUMN';