The Bit Bucket

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

In an earlier post, I discussed the design of EAV (Entity Attribute Value) tables, and looked at why they get used. I’d like to spend a few moments now looking at the pros and cons of these designs.

Let’s use the same table as the last time as an example:

Pros

The main positive that’s typically described is that the schema is “flexible”. By this, the developers usually mean “I don’t have to change the database schema (or worse, have someone else change it) when my needs change”.

2018-02-19

If you have ever tried to write scripts to configure operating systems, you’d realize just how hard that is to get correct.

For example, if you need to ensure that your virtual machine has two network adapters, and they need to have a specific set of IP addresses, how do you do that?

[caption id=“attachment_2743” align=“alignnone” width=“461”] Image by Markus Spiske[/caption]

The traditional approach for PowerShell (and similar tools) was to try to write a step-by-step script to configure the network adapters the way you want. But where do you start? Do you write a script to check for any existing adapters and loop through them to try to remove them? Do you try to add the ones you want, and then remove the others?

2018-02-16

I’ve been very lucky over the years because I haven’t needed to wear glasses. Every now and then I’ve got some because I thought it might help with reading but then I find them more inconvenient than helpful. I’ve had one eye long-sighted and the other short-sighted. That’s been a really useful thing in day to day life.

However, where this comes unstuck is on modern laptops. There seems to be a current trend to pushing more and more pixels into the same size laptop screens but the applications aren’t helping to deal with that.

2018-02-15

Prior to SQL Server 2012: IDENTITY Columns

When we needed to automatically number rows in SQL Server prior to SQL Server 2012, one option was to use IDENTITY columns.

(Apologies to purists who think these were the spawn of the devil but real people did use them).

One of the challenges with IDENTITY columns was when you wanted to move data from table to table but needed to retain the same value from the IDENTITY column. To do that, we used SET IDENTITY_INSERT tablename ON; That was fine but an issue that arises, is that if the next IDENTITY value was going to be 245001 and you just inserted a row with 245001, what would happen with you turned IDENTITY_INSERT back off and tried to insert another row.

2018-02-14

I’ve concluded that many software vendors (particularly large ones) don’t understand how much support users of their software provide to each other, and how critical that support is.

The SQL and data communities are a good example of this. When someone has a problem and are wondering how to solve it, they don’t call Microsoft or Google or Oracle (or whichever vendor) first. If they’re lucky, they ask a colleague for help. But most will simply make a Google search (or yes a Bing search) to try to find an answer.

2018-02-13

If you’ve been working with databases for any length of time, you will have come across implementations of Entity-Attribute-Value (EAV) data models (or non-models as some of my friends would call them).

Instead of storing details of an entity as a standard relational table, rows are stored for each attribute.

For example, let’s create a table of people:

When we query it, all is as expected:

And for a long time, this has been how we expect to create tables.

2018-02-12