Slides and Demos for SQLSupper August meeting

Last night I had a lot of fun presenting on one of my favorite topics, MARS and SQL Server protocols including a rather fun live demo. The slides and demo apps are available: https://github.com/GoEddie/SQLSupper-WhenMarsAttacks If anyone wants to know more about MARS or TDS feel free to contact me as always :)

I dont use the Visual Studio SSDT Publish

I see questions pretty regularly on stack overflow and the ssdt msdn forum that is some variation on “I am doing a publish through visual studio and it doesn’t quite do what I want” or “I want to publish the database when I press F5 except when I don’t want it to publish”. The second variation I particularly enjoy because I have always though that what I want is a computer that I can think something and it will do it - I guess we are a little way off but one day hopefully that will be possible.

Ignore a warning on a dacpac reference at your peril

I worked on a project where we had two SSDT projects with different versions of SQL, one 2012 and one 2008. The 2012 project referenced the 2008 project as a “this database” reference and while this seemed like it worked, the reference in solution explorer had a little warning sign and it caused nothing but problems. The first thing was that the reference didn’t work on anyone else’s machine when they checked out the solution, the other developers had to delete and re-add the reference once.

Shared databases or private databases?

Traditionally database developers have shared a database and while this certainly made sense when everyone had a limited amount of ram and a few hundred megabytes of hard disk space it has been a while since we have had a development machines without enough resources to easily run a few copies of visual studio and SQL Server - if this isn’t the case then you really need to re-evaluate the cost of a new machine versus the cost of developers time.

AdventureworksCI Step 5 Adding tSQLt to the solution

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.

AdventureworksCI Step 4 Pushing to GitHub

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.

Keep your environments in-sync and your upgrade scripts fresh

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.

Pre-Compare & Pre-Deployment Scripts to SSDT

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.

WaitForSql (to start)

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.

Layer a database into a SQL Server container

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.