Home
Do you save each script as you go?
  v6.0 Posted at 20/10/2016 10:39 PM by Igor Goldobin
Every time a change is made to your product's SQL Server Database, script out the change. You can use SQL Management Studio or VS.NET (you can find old guys that still use Enterprise Manager or Query Analyzer), but every time you make changes you must save the change as a .sql script file so any alterations are scripted.

Everything you do on your database will be done at least three times (once on development, once test and once on production). Change control is one of the most important processes to ensuring a stable database system. 

Keep the scripts in a separate directory with only .sql files
eg.  C:\Program Files\SSW Time PRO.NET\SQLScripts           (32 bit)
 or  C:\Program Files (x86)\SSW Time PRO.NET\SQLScripts  (64 bit)

Later on you will get these 7 benefits:
  1. ​When you have an error you can see exactly which script introduced it
  2. You don't have to use a compare tool like Red-Gate SQL Compare at the end of your development cycle
  3. Your application can automatically make schema changes
  4. The application can have a "Create" database button when installed for the first time
  5. The application can have an "Upgrade" button and work out itself if this new version needs scripts to be run
  6. The application can tell if it is an old version (as a newer version may have upgraded the schema), so you only use the latest clients
  7. The application can have a "Reconcile" feature that compares the current schema to what it should be

 
Figure: A list of change SQL scripts, each file name is in the correct format

Is there a file naming convention to follow?
The script file naming convention should be XXXXX_ObjectType_ObjectName_ColumnName_Description_SchemaMasterInitials.sql

eg.  00089_Table_Employee_Gender_ChangeFromBitToChar_AC.sql

What are the rules for Entity Framework Code Fi​​rst?
Similar principles apply when using Entity Framework Code First. Every change you do to the schema must be either saved in code or scripted out as per above. We recommend using Migrations feature of Entity Framework 6. It allows you to keep track of all the changes in the similar fashion as SQL Deploy. Watch this video to learn more. We also recommend using SSW SQL Validate tool to make sure your schema hasn't been manually modified.​

Related rules

    Do you feel this rule needs an update?

    If you want to be notified when this rule is updated, please enter your email address:

    Comments: