The Bit Bucket

In my last DevOps-related post, I discussed some of the issues that arise when using migration-based deployments.

Migration-based deployments are based on a series of T-SQL scripts that apply changes to your database from its current state to a desired final state.

One of the questions that arises though, is whether or not these T-SQL scripts should be idempotent ie:

Should the script be written so that you get the same outcome if you run the scripts multiple times?

2018-03-02

Whenever I need to work on a new laptop or server, or whenever I change versions of SQL Server Management Studio, I kick myself for not remembering to export my settings, so I can import them again.

I spend quite a bit of effort getting SSMS configured the way I want, so it only makes sense to save the settings. Saving them isn’t perfect but it’s far better than not having done it.

2018-03-01

I’m really happy with the additions we made to our free SDU Tools for developers and DBAs that are going out tonight:

DatesBetween - table-valued function to return dates between two dates DateDimensionColumns - table-valued function to return typical date dimension columns for a date (useful for data warehouses)

JulianDayNumberToDate - converts a Julian day number to a date DateToJulianDayNumber - converts a date to a Julian day number

SystemConfigurationOptionDefaults - view that shows system configuration options and their default values (and many other values) NonDefaultSystemConfigurationOptions - view that shows system configuration options that are not at their default values

2018-02-28

If you’ve ever used SQL Server Reporting Services, you’ll notice that when you drag a database column into a table, it auto-magically converts the name of the column into a more English-readable name.

[caption id=“attachment_2791” align=“alignnone” width=“429”] Image from MSDN[/caption]

Notice how when the LineTotal column has been dragged into the table, the heading has been set to Line Total with a space. What it is doing is taking a Pascal-cased or camel-cased name and separating the words with spaces, based upon where the capital letters are.

2018-02-28

Many of my customers are software houses (ISVs). In almost all of them, I hear people complaining that they can’t find enough good staff. I think they are trying to tackle the wrong problem.

Most of the staff members they are trying to find are developers, and there are so many great developers out there, but you have to accept that they might not be in the location that you hope they’re in.

2018-02-27

I’ve done a lot of Microsoft exams over the years, mostly SQL Server ones but plenty of others too. And one thing that I really don’t like is when the questions are:

• Purely academic (ie: would never happen)
• Memory based (ie: who cares what the DTU limit is for a P3 today?)
• Clearly not written by someone who actually uses the product

Today, I want to mention an item in the last category.

2018-02-26

In an earlier post, I described the difference between state-based deployments and migration-based deployments. What I want to talk about today are two of the main issues that can arise in a migration-based deployment and what to do about one of them.

When you are using a migration-based deployment technique, you are storing all the scripts that take a database from its current state to the desired final state. While tools like Ready Roll make this is a relatively easy way to perform migrations, it suffers from a few key problems:

2018-02-23

The status bar at the bottom of a query window in SQL Server Management Studio contains a wealth of information in its default configuration.

The bottom left shows the connection state:

I’ll talk about connection management more in a later post. The bottom right also shows quite a bit:

In this case, it’s showing me that I’m connected to a server called SDUPROD; it’s running v14 of SQL Server (ie: SQL Server 2017); I’m logged on as GREGP50\Greg; my spid (or session ID) is 68; and I’m connected to the master database.

2018-02-22

I’m not a great fan of dynamic SQL (where you create a T-SQL command in code, then execute the command) but there are times when you just need to do it.

When creating dynamic SQL, it can get very painful trying to work out which strings are quoted and how many quotes you need.

To make this a bit easier, in our free SDU Tools, we added a function for QuoteString.

2018-02-21

A current trend that I can’t say that I love is constant churn within software applications. I have no interest to go back to the days where we got a new version of SQL Server or Power BI, etc. every few years.

It’s also not a case of who moved my cheese?

In fact, I thrive on change. However, I’ve now become really concerned about how anyone:

• Learns to use a complex application
• Remembers how to use a complex application when they don’t use it daily

I first really struck this issue with Azure. If I was teaching a class that used Azure, I could check every single lab on Sunday night, then Monday morning, the students would find it had all changed. That’s OK for an experienced person, but not OK for a learner.

2018-02-20