When developing or administering SQL Server databases, it is important to think about how code is deployed. Typically, and sadly, code is developed either in SSMS or another IDE on a shared SQL Server. When it comes time to do a release, deployment scripts are manual created. The release scripts are then manually run in another environment, whether that be test, QA, Production, etc., with varying degrees of success. Empirical evidence from a large number of people doing this has shown, in the majority of cases, that the process is slow and error prone.

In an ideal world we would have a process that looks like:

• Developers write code on their own SQL Server instance
• Developers check changes into a source code repository
• A build server watches for changes, downloads the code, builds tests and either generates scripts for deployment to other environments or updates other environments.

If you have this sort of process, then I guarantee that you will spend less time creating releases. You will also potentially have less errors than manually collating changes and creating alter statements. The problem is that today the thought of using source control and setting up a complete CI process seems like a challenge. It is too tempting to say that it isn't possible or will take too much effort and ignore it.

What I would suggest is that you start in small stages with the first step being getting your database into a version control system (VCS). The only real difficulty in getting your database into version control is what VCS you will use, but with offerings like Visual Studio Online and Github, it should be really simple to get a repository somewhere quickly and cheaply.

Once you have a source code repository, it takes a matter of minutes to export the code from a database and check it in. Getting code from a SQL Server database into .sql files is very simple to do, either using the SSDT Database import wizard or the SSMS "Script All Objects" wizard. I understand that this does not account for everything, however it is enough to start with . For example any SQLCLR objects will require the .dll's to be manually added, and static or reference data will not be included in the scripts.  But anything is progress over nothing.

Once the code is checked into you VCS,it can be shared between developers, and this is where the interesting bits begin. When you are sharing code you should also have an instance for each developer so each does not interfere with the others. If you are licensed users of SQL Server developer edition, then you can have as many instances per developer as you like. If you are not licensed, then you can use localdb or SQL Server Express for free. Once everyone has their own instance, you will need a way to deploy the code from .sql files to the instance.

If you use SSDT, you can use sqlpackage.exe to do it for free. If you have a Redgate SQL Compare license, then you can use that (Note,  the pro version includes the command line version). Manually deploying .sql files gets boring quite quickly, so you will need to think about having something that you can run to deploy your changes to your developer specific instances or databases.

If you have neither SSDT or SQL Compare, then you can use sqlcmd. Sure it is harder to deploy the changes, but you could use create scripts to create the database and then write upgrade scripts that you store in version control and have a system of running them via sqlcmd.exe such as:

For a new DB, run all the create scripts

• Run upgrade script 1
• Run upgrade script 2
• Run upgrade script 3

This really isn't ideal but at least it would get your code from the VCS onto every developer’s instance when needed. It would get you to start sharing the code between developers instead of databases and get you to start thinking and working on improving how you deploy your SQL Server code.

Once you have the ability to deploy to your private instances, you can start thinking about how you would deploy everyone’s changes to a shared server. Even though we want developers to have their own instances, the end goal is to deploy to shared instances.

Getting to the stage of having your code in a VCS and a basic system of sharing code between developers shouldn’t be too much work. There will likely be some stress for a team, but if you have been holding off on using version control or starting a Continuous Integration (CI) process, don’t. Just do it. If it all goes wrong, and you get stuck, ask for help. If you genuinely can’t get it to work for you, then you can always go back to the way of working that you had before. This is not a case of moving forward with no return.

Continuous Integration for SQL Server databases can be done without any special tools. You can build your own CI process using sqlcmd if you like, but using tools like SSDT and sqlpackage.exe or Redgate's SQL Compare, makes a CI Process a lot easier.

Please do not put off getting your database into version control or starting a CI process because it is hard or it is not a perfect solution, instead get something working that shows some value and work on improving it over time.