Continuous integration with SQL Source Control, SQL Automation Pack and Jenkins
Steps are the same.The first step in Jenkins job is creating NuGet package:
sqlCI.exe Build /scriptsFolder=<value1> /packageId=<value2> /packageVersion= <value3> /temporaryDatabaseServer=<value4> /temporaryDatabaseUserName=** /temporaryDatabasePassword="*****" /outputFolder=<value5> /additionalCompareArgs="<options>"
The second step is updating necessary DB according to the latest virsion in the repository using an existing NuGet package:
sqlci.exe Sync /databaseName=<value1> /databaseServer=<value2> /databaseUserName=***** /databasePassword="*****" /package=<value3> /additionalCompareArgs="<options>"
For borth steps it's better to set up such option as /include:Identical in order to avoid build mistakes, if there're no changes.
Also we should remember that:
- for foreign key should be determined the default value;
- if the table is renamed, all data will be lost;
- if static data are committed, data will be overwritten on stage;
- for using linked servers DTC should be configured (or you can use dynamic queries)