Changes to AutoDeploy SQL with TeamCity (an alternative to Redgate?)

Question

I create CI and Auto Deployment for .NET applications (source controlled by SVN) for our test environment with TeamCity, and everything is going well. I am at the point where I want to automate the execution of db scripts. I did some searches and read several articles, such as http://www.troyhunt.com/2011/02/automated-database-releases-with.html (troyhunt blogs were priceless!), And many seem to point to using RedGate change management and deployment software. I am a little afraid that the new tool will handle the generation of the script, so I wonder if there are other accepted methods that are worth exploring.

Our current solution design standards include a project containing database-related files, such as our orm db. It also contains a number of folders containing our change scripts, as shown below.

MySolution > MySolution.Data > 3.0 Scripts * 01 - UpdateUserTable.sql * 02 - UpdateRoleTable.sql > 3.1 Scripts * 01 - CreateJobTable.sql * 02 - InsertJobTypes.sql * 03 - AndSoOn.sql 

I believe this framework is important because, although we want automatic deployment in our test environment after verification, we deploy the actual release much less frequently, and I'm not sure if RedGate comparison tools can handle the level of changes that sometimes happen. That's why I find it much more convenient to use version folders for scripts, and then increase the minor at release to contain versions of the script.

Having this pre-existing structure, I was inclined to save it and added a TeamCity assembly definition that could use db scripts for successful deployments. I suggested that I could force TeamCity to identify the newly added .sql files for SVN in the expected path and then execute them on the server, but maybe I expected too much. In the past, I implemented such a solution using TFS and MSBUILD and custom build steps, but completely new to TeamCity.

Is there a process to achieve something like this? It seems to me that this is a completely normal implementation, I have not yet been able to find out how to do this. Or is RedGate's solution the standard we should go over?

Answer 1

I read the same article by Troy Hunt 2 years ago. It was a revelation, and I quickly got us and worked with SQL Source Control + SQL Compare + TC. It was a great solution, but there were some things that didn't work the way I wanted. Chief among them was that SQL Source Control does not (or not so well) play well with industry / mergers. In addition, SQL Source Control was an out-of-band process that required developer training, acceptance and understanding of its features. He never took root. Our team eventually moved away from RedGate in favor of TeamCity-managed Entity Framework migration, and we did not look back.

To your immediate question: I think that what you are trying to do here is possible, although I do not think it is necessary for TeamCity to search for newly added SQL scripts. The Red Gate and Entity Framework identify the most recent script / migration deployed in the database itself and refer to it when they determine where it should start applying the changes. You can save the same value ("3.0") in your database or as a TC build parameter, and then use powershell to repeat newer folders and apply the latest scripts using sqlcmd.

You might consider the possibility of EF-based migration, especially if you adopted the first code model. If you're interested, I can update this answer to talk more about how we do it.

UPDATE:

Primary migrations of the EF code are performed by writing a discrete set of database schema changes in the migration class. This class has an Up and Down method that can apply / undo this particular batch of schema changes. This is more or less equivalent to how Ruby and others do it.

When changing the Entity model (adding a table, deleting a column, changing constraints, etc.), you create a new migration class by calling Add-Migration <DescriptionOfSchemaChange> in the package manager console. This will create a new class called <timestamp>_<DescriptionOfSchemaChange>.cs . You can then apply this migration locally by calling Update-Database . The Entity Framework manages nuts and bolts for you, but you can add an arbitrary SQL step to the migration step if necessary. PluralSight offers an excellent course covering these concepts.

Migration is controlled using the migrate.exe , which is deployed as part of the NuGet Entity Framework. In TeamCity, you can use migrate.exe to start the migration with the specified database. First, you must copy migrate.exe local to the assembly that contains your migration classes. Then you simply run migrate.exe against this assembly with the DB connection string as a parameter. The working directory is the migration directory.

copy ..\..\packages\EntityFramework.5.0.0\tools\migrate.exe .

migrate.exe ASSEMBLY.dll /connectionString="CONNECTION_STRING" /connectionProviderName="System.Data.SqlClient"

Answer 2

After a long search and learning how I could achieve my goal, I decided to just go and write my own console application that does this for me. Here I will describe the main stages and tell you more about them later.

DatabaseRevisionUpdater (console application)

• Using SVNSharp, DatabaseRevisionUpdater connects to our SVN server and reads SQL Script files into memory from the specified SVN Revision range.
• In .NET created by Transaction, the application then reads the Script files one at a time. If any errors are detected when running these scripts, the transaction is rolled back and the application exits with error code -1.
• The target database should contain several new logging tables that are recorded when updates are created. The version table keeps track of which changes have been made, successful and unsuccessful. A separate table contains information about each Script run for this change request (with the status "Success", "Failure" and "Missed").
• When future updates are made, the database version is determined from the tables in step 3. If the request crosses any previous successful updates, the update will not continue because it assumes that it has already been applied. The version of SVN Update Revision From can also be determined from these tables.

TeamCity Integration

• Build Type Added: Command Line
• The working directory is the directory on the build server containing my console application.
• Run: Custom Script (Do not use an executable file with parameters, as it does not seem to handle double-quoted arguments like my dbConnectionString)
• User script: executable file followed by arguments intended for my db. Arguments include svnUsername, svnPassword, svnPath, databaseConnectionString, and most importantly, RevisionTo. I use% build.vcs.number.APP_ID% to get the current build version number for this.

Using Bonuses - Updating Disabled Databases

All this works fine for our test environment, but our live environment is on an isolated network that does not have access to svn or the Internet (for security reasons). I use several different modes with my console application. The mode described above is a Direct Update, which is all in one solution. It determines the current version of the database, looking at its log tables, it checks the SVN for all updates, because before the revision you specify, it runs them against the target database and accordingly updates its log tables. For our live environment, I added some additional modes. Export and import.

• Export is used to get scripts from SVN within the revision range. He then saves them in an XML file. It doesn't care about the target db at all.
• Import is used to import the XML data file and apply updates in it to the target database. It takes care of version compatibility, but has nothing to do with SVN.

This is essentially my decision. Nothing out of the box, but it works like a charm. I can get TeamCity to interact well with it and use the Console output to describe the process in detail, making it as transparent as possible. TeamCity reports all this output in its build logs, and also fails or fails depending on whether my return code is 0 or something else.

Being a week-long journey, I decided to make a short blog post in which I will tell in detail how I got here if someone else is interested: https://andrekriegler.wordpress.com/2013/09/12/auto-deploy-database /

Answer 3

At Red Gate, we are currently working on what we call "migrations v2." This is a database deployment technology that uses both a schema comparison methodology and allows users to specify their own scenarios, for example, in data movement situations.

There is a series of web pages that describe it better, as well as a GoogleGroup forum .

Let me know if you have any questions about how this works.