Whether we use a tool to generate upgrade scripts or we write manual test scripts for database changes there are a few things that we should bear in mind to ensure we do not break existing databases. Why is it important? When you upgrade an application you just compile and then deploy the code in a single operation, the old code is removed and the new code put in place, simple.
When you use SSDT to deploy database changes you can include a pre/post deploy script which is run after the dacpac and database has been deployed. The process looks like: 1. sqlpackage.exe compares dacpac to database 2. sqlpackage.exe generates a deploy script 3. sqlpackage.exe runs pre-deployment script 4. sqlpackage.exe runs deploy script (generated in step 2) 5. sqlpackage.exe runs post-deploy script This happens whether you get sqlpackage.exe to do the deployment or whether you generate a script for later deployment.
In the spirit of having apps that do just one thing I have put a really small .net app on github that tries to create a connection to a SQL Server database and just keeps retrying until it is available: https://github.com/GoEddie/WaitForSQL What? Huh? If you have a CI build and you start a new instance or database or azure db etc it can take different amounts of time to be available so this just sits trying until it can get a valid connection and you can continue your CI loveliness.
In my previous post on running SQL Server in a container I showed how you could use the Spoon (https://spoon.net) products to start an instance of SQL Server without having to install it: https://the.agilesql.club/Blog/Ed-Elliott/Containers/Run-SQL-Server-In-A… In this post I am going to talk about images and how to layer them together and why SQL Server is perfect for this sort of container technology! What is an image An image is the files and registry keys that make up an application, the sqlserver/sqlserver2012-express image was created by taking taking all of the files and registry keys that were added to a machine during the SQL Server install.
This is part of a series on how to take the Adventureworks database and bring it in line with modern standards. How do we put a legacy SQL Server database into source control? What issues do we face and how hard is it? Then how do we build a continuous integration pipeline and possibly move towards continuous deployment. This series takes a look at how we achieve this whilst also implementing some coding standards and making an imperfect database a little bit better.
Containers are one of the big things at the moment, Microsoft has recently announced that they will be bringing official support to Windows. While this is great news it is unlikely to be something most IT departments can use in the near term mainly because it takes time for everyone to get up to date with the latest version of windows, which in this case, is still to be written.
It is a constant source of frustration for some people that it is hard to download SSDT or SSDT-BI or you download the wrong thing. This is an example of some frustration: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/c82354d0-2f88-4… Anyway, this is now sorted as there is a single page to download SSMS or SSDT: https://msdn.microsoft.com/en-us/library/mt238365.aspx?f=255&MSPPError=-… and SSDT and SSDT-BI: https://msdn.microsoft.com/en-us/library/mt204009.aspx Feel free to grab Preview or RTM versions!
It isn’t a Pre compare script When you use SSDT to deploy your dacpac, a pre deployment script lets you run some T-SQL scripts before the script which brings your database up to date with your dacpac. The important thing to note is that the process is: sqlpackage compares the dacpac to the database sqlpackage generates a list of changes to make sqlpackage generates the T-SQL statements to make those changes sql package either creates a script for later execution or deploys the changes The script that is created *or* is executed contains, 0 or 1 pre deployment scripts, the statements to generate the required changes and then 0 or 1 post deployment scripts.
This is part of a series on how to take the Adventureworks database and bring it in line with modern standards. How do we put a legacy SQL Server database into source control? What issues do we face and how hard is it? Then how do we build a continuous integration pipeline and possibly move towards continuous deployment. This series takes a look at how we achieve this whilst also implementing some coding standards and making an imperfect database a little bit better.
This is part of a series on how to take the Adventureworks database and bring it inline with modern standards. How do we put a legacy SQL Server database into source control? What issues do we face and how hard is it? Then how do we build a continuous integration pipeline and possibly move towards continuous deployment. This series takes a look at how we achieve this whilst also implementing some coding standards and making an imperfect database a little bit better.
