The Purpose of a Database

A great quote from a blog on machine learning in SQL Server: “…nobody buys a DBMS for the sake of DBMS. People buy it for what it enables you to do”. The post is from Rimma Nehme, who has given a few keynotes at the PASS Summit. While the focus of the post is how you can implement deep neural network learning with R Services in SQL Server, I thought that quote stands out for any database, relational or NoSQL, from Microsoft, another vendor, or open source.

I think it’s easy to get caught up in the debate over which features are better than others, or which database might perform better for the money spent. Pehaps we want to debate how easy or difficult it can be to build an application with the platform. We can look at the ROI, the ability to easily implement HA, DR, or some particular subsystem that we need. Those are all good questions, and certainly part of the decision to use a particular platform.

At the end of the day, it doesn’t often matter which database platform you choose. Whether a JSON file, a relational platform like SQL Server, or the Neo4J graph database. The people that will use the database to query information, make decisions, or just store information need the system to work for them. The system needs to do something that helps their organization in some way. Often that’s based on the capabilities of the software that connects with the database, the capabilities and performance of the platform, and certainly the abilities and execution of the staff that work on the system.

There’s plenty to debate about using SQL Server with the R language. We can make some determination about whether or not there’s value in spending licensing dollars on expensive SQL Server licenses and using those cores for analytics rather than some other, cheaper hardware. Microsoft R Server (another some other service) might be a better choice. Ultimately, the value to the end user is in getting the data processed and returned to them, whether this is through a query, a report or some recommendation from a machine learning algorithms.

My view is that more complex processing, whether through machine learning or other types of data analysis, are going to be more important for data professionals in the future. As we build new applications, or even seek to keep older ones viable for a long time, we need to keep in mind that the DBMS isn’t the reason we have a project or job. It’s because we can somehow extract information from the DBMS and process it in a way that adds value to an organization. Whether we do this in a database or application is up for discussion and debate for each individual situation, but we need to ensure we are providing value for our customers.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 3.8MB) podcast or subscribe to the feed at iTunes and Libsyn.

Posted in Editorial | Tagged | Leave a comment

Installing dbatools on WS2012 R2

This is actually a simple, short post, but writing helps me remember things, and since I had to learn this, I decided to write something short.

I have a number of Windows Server 2012 R2 VMs. I need to get rid of these and upgrade to WS2016, since installing SQL 2016 is a pain on these VMs. However, for the time being, I’m stuck with them.

I was looking to try some of the dbatools modules. I popped open an elevated command prompt and

2017-01-20 09_30_50-Atlas Home Lab .201 - VMware Workstation

This is because that module isn’t in this version of PoSh. I checked, and I could see this is v4, and Install-Module was added in v5.

2017-01-20 09_31_06-Atlas Home Lab .201 - VMware Workstation

I had to go to the download page to see what my options were. One of these is to download the modules myself, but that seems like a pain. Instead, I used this command:

Invoke-Expression (Invoke-WebRequest -UseBasicParsing

That worked great, and I had the modules installed.

2017-01-20 09_44_05-Atlas Home Lab .201 - VMware Workstation

Nothing much new in this post that isn’t in the dbatools download page, but I did learn a bit more about PoSh and its versions. Since I’ve gotten used to working on Windows 10, it’s good to remember that previous versions of the OS will have different capabilities.

In this case, the pain of using WS2012R2 means I’ll try to find time and upgrade to WS2016 as soon as I can.

Posted in Blog | Tagged , , | Leave a comment

Legacy Limits

We are seeing changes to the SQL Server platform every month in Azure. Since that’s the same codebase used to product SQL Server on premise, that means the enhancements are not only being tested in the cloud, but they are available for release on a regular basis. SQL Server has been on a two year release cycle for major versions, but things are speeding up and I expect a new version (SQL Server 2017?) around a year after the last version (SQL Server 2016) was released.

My thoughts are that this cadence will mean that many of us with more than a dozen servers will end up supporting more versions of SQL Server in the future. New applications will want newer versions, as do the employees, but there isn’t always a business case to upgrade all the older instances. If we were to see new versions every 18 months, given a 5-7 year life cycle (the standard support time frame) for database servers, I would expect that many of us would be always supporting the last 4-5 versions of SQL Server. If we go to a ten year life cycle (may be more realistic to me), then we would be looking at 6-8 versions.

If you think ten years is too long, SQL Server 2005 is just over 11 years old. How many of those instances do you support? SQL Server 2008 is almost 9 years old and I bet a few of you have those instances around. Certainly if you have a support agreement and you have applications that use fairly core SQL Server features you can upgrade, but certainly keyword and language behavior changes might limit your flexibility.

I read a piece recently noting that many organizations still use Windows XP for various systems. A number of the reasons given are to support legacy hardware or software. Some don’t have replacement versions of the hardware/software, or can’t find any value in managing an upgrade. The same issues hold true for SQL Server. I did some work for a company in 2008 that was still running a SQL Server 6.5 instance to support their keycard system. The database was virtualized, worked fine with Internet connectivity, and essentially cost a few hundred dollars a year for consulting fees. An upgrade to newer software, which would support SQL Server 2005+ would have cost over $50,000. Plus support.

For no new useful features. No wonder they didn’t want to upgrade.

This week I’m wondering how many of you are tied to older versions of SQL Server because of compatibility issues with software (or hardware). Are there reasons you maintain old database platforms? Any plans to upgrade, or is the cost not worth the benefits?

I find legacy software and hardware to be a problem with the technology paradigm. While vendors want to move to new versions and reduce their support burden, many people feel that the cost of regular upgrades every few years is too high. Perhaps we could move to renting software, and getting constant development, but developers seem to be loathe to just constantly develop one version of the software for decades, preferring to fundamentally change the architecture at some point. Certainly that may make sense for some customers, but others who don’t need new features might wish for software to just work.

I don’t have a great solution, but I would like to see options for software like we have with many other products. An example might be autos, where there are companies manufacturing parts that repair or upgrade older models, without the need to purchase a new vehicle. Maybe older software can be licensed in some way to allow independent developers to produce patches, security or otherwise. The vendor could continue to sell new versions, but a licensee could support old ones. I know there are times I’d prefer to have an older version of software work well, and continue to work for the foreseeable future.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 3.8MB) podcast or subscribe to the feed at iTunes and Libsyn.

Posted in Editorial | Tagged | Leave a comment

What is a Database Master Key?

The encryption mechanisms in SQL Server are interesting, and they work well, but they are somewhat poorly named. I ran across a few people struggling to understand, so I decided to cover the concepts in a series of posts. This one looks at the Database Master Key (DMK).

Not the Master Database Master Key

This is one of the more poorly named objects in the SQL Server platform. Or perhaps the “master” database is the one that is not named well. In any case, the DMK has nothing to do with the master database. Instead, the DMK is the base encryption key inside of a database. This is the key that secures all other keys

There can be a DMK in each database that you have, including master. For some features, such as TDE, you must create a DMK in the master database. For others, you would create a DMK inside of the user database.

Protecting a DMK

By default the DMK is encrypted and protected by the Service Master Key (SMK), which is the key that protects the instance. This means when a database is opened and used, the service account can decrypt the SMK and use that key to decrypt the DMK. You can optionally also protect the DMK with a password.

Even more optionally, you can break the encryption link between the SMK and DMK. In this way, you could need a password on the DMK, which would have to be entered each time a user wanted to use a key protected by the DMK.

You need to ensure the password for the DMK is protected and available, as you will need it if you restore the database to another instance.

To use the DMK, an account needs the CONTROL permission on the database.

The DMK is a symmetric key. It is uses the AES_256 algorithm in SQL 2012+. Prior to that it was with Triple DES.

Posted in Blog | Tagged , , | Leave a comment

The (Former) Complexity of PowerShell

When I first looked at PowerShell, it was v1.0, and I was in a TechEd presentation. The language seemed a mile past the VBScript I was using when T-SQL didn’t function well. The ability to access the .NET namespace, work with objects, and program with error handling was exciting. I played with the language a little, but didn’t find that many places to use it at the time.

Certainly file operations were much easier with PowerShell, and I built scripts to copy backup files around the network. AD operations were easy in PoSh. However, when I tried accessing SQL Server, I thought the code was complex. In fact, whether I was running a stored procedure, or performing a restore, the PowerShell code required was cumbersome. A good example is shown at the beginning of Aaron Nelson’s recently updated post on querying with PoSh. The complexity shown to just make a connection to PoSh made me think I should just write that kind of code in C#, with all the debugging and other software support available in Visual Studio. Building quick utilities with PoSh was something I’d like to avoid.

When I heard that SQL Server Powershell was being updated, I wasn’t too excited. I had visions of there being newer versions of cmdlets, but a similar level of effort to work with SQL Server. However, as I’ve played with the new cmdlets, along with the dbatools module, I see PoSh becoming easier to use than T-SQL in some cases. In fact, I’ve started to keep a command line window open (with ConEmu) and access that to perform lots of quick tasks that aren’t based on simple queries of data. Even a backup with Backup-SqlDatabase seems as simple as it might be in T-SQL, perhaps more so if I don’t have an SSMS connection open.

I know there can be a debate over whether you need PoSh as a DBA or you can get by with just T-SQL. I don’t want to take a side here, and no matter how you feel, if you can get work done, then you are successful. Your job depends on you being able to reliably write code to perform some task over and over. The language doesn’t matter.

My view is that I think there are cases where PoSh seems a better fit and places where T-SQL works really well. I want to improve my skills in both so that I can decide what works best in any particular situation, and feel comfortable in building a solution either way.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 3.6MB) podcast or subscribe to the feed at iTunes and Libsyn.

Posted in Editorial | Tagged , | 2 Comments

Using Out-GridView To Pick Parameters–#SQLNewBlogger

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

I was editing an article on PoSh and learned something cool. I can use Grid-View to surface an object in a pipeline and then change the items passed through to the next command.

Here’s how it works. Suppose I have a simple comment, like Get-SqlDatabase. I can run this and get a series of database. (command and output shown here).

Get-SqlDatabase –ServerInstance .\SQL2014

2017-01-03 12_50_20-powershell

I get a bunch of data, but I don’t want all the items. For example, maybe I just want the “SimpleTalk” databases.

Get-SqlDatabase -ServerInstance .\SQL2014 | Where-Object { $_.Name -like ‘SimpleTalk*’}

2017-01-03 12_51_42-powershell

I don’t want to edit this Where-Object clause all the time. I want something simple to easily fix this.

Enter Out-GridView

Instead of changing my filter, I can do this graphically. I can pipe my output to Out-Gridview, and I’ll get this.

2017-01-03 12_54_44-Get-SqlDatabase -ServerInstance ._SQL2014 _ Out-GridView

All of my output data appears. No big deal, right? Is this useful? It sure is. Look at the filter item above. I can type in there, and my data is filtered.

2017-01-03 12_55_29-Get-SqlDatabase -ServerInstance ._SQL2014 _ Out-GridView

How can I use this? Suppose my code is this:

PS SQLSERVER:\SQL\Plato\SQL2016\Databases> Get-SqlDatabase -ServerInstance .\SQL2014 | Out-GridView -PassThru | Backup-SqlDatabase -CompressionOption Default –Script

Here I’m getting a list of databases, passing them to Out-GridView, and then sending the results to Backup-SqlDatabase, which will return a script to back up all my databases.

I’ll run this, and then enter a filter in the grid.

2017-01-03 12_57_47-Get-SqlDatabase -ServerInstance ._SQL2014 _ Out-GridView -PassThru _ Backup-SqlD

I can now highlight all these rows (if that’s what I want).

2017-01-03 12_59_11-Get-SqlDatabase -ServerInstance ._SQL2014 _ Out-GridView -PassThru _ Backup-SqlD

This time I have an “OK” button in the lower right corner of the grid.

2017-01-03 12_58_23-Movies & TV

When I click “OK”, my filtered list is returned to the pipeline and send to the backup command.

2017-01-03 12_59_32-powershell

This is a quick way to work with the parameters in your pipeline in an ad hoc way. Use Out-GridView to filter and select the rows you want to return to the rest of your script.

Posted in Blog | Tagged , , | Leave a comment

Quick SQL CLone Support

I have been playing with SQL Clone for a few weeks now and liking the product. I’m looking forward to using this more and more, especially for testing ideas in development. However, there are times I have questions or am unsure of how to use things.

I work for Redgate Software, so I have an in. I can certainly email developers if I need to, but we also have this:

2017-01-16 14_29_03-sqlclone _ Redgate Slack

There’s a great Slack channel where I can quickly reach developers. This comes in handy as it seems various people at Redgate pay more attention to Slack than email or other communication methods.

No, this post won’t get you on our Slack channel.

However, when opening the SQL Clone beta, I see this dashboard.

2017-01-16 14_26_55-SQL Clone

Notice the little circle in the lower right? Click it and you get this:

2017-01-16 14_32_14-SQL Clone

Apparently there’s a semi-live chat system that our developers are using. Chris is actually one of the SQL Clone developers, and he helped me solve the registry issue a few weeks ago.

That’s pretty cool. If you type something in there, it will get sent to the developers, and if they’re around, they will respond. I sent a test, with a semi-legitimate question, and see this below my text:

2017-01-16 14_34_39-SQL Clone

Given I sent the note late in the afternoon, I’m sure all developers are gone for the day. I can hide the popup, and then will see when someone responds.

Update: in the am, I got an email from the system, at the same time, I got a reply in my SQL Clone dashboard. As you can see, my question was answered.

2017-01-17 08_29_47-SQL Clone

If I click the arrow in the upper left, I can see all my conversations, which is handy if I’m trying to report or work on a few issues.

2017-01-17 08_29_53-SQL Clone

That’s pretty cool, and it’s an neat way to get quick feedback. Assuming it doesn’t take too much time away from developers actually developing the product.

I suspect we’ll get support involved here, with escalations to developers. At least, that’s what I’d do, but for now, it’s good that our developers are responding to beta users and helping them use and test the product.

Posted in Blog | Tagged , | Leave a comment

Getting My Alias in #SQLPrompt

I got a tweet after my SQL Prompt formatting piece that said a user would be interested in upgrading from v5 if “as ‘Alias’” was transformed into “’Alias’ = “.

Well, in v7.3, this is in there, and I didn’t realize it. You can clearly see this in the formatting actions options, which is a place I didn’t think to look.


Does this work? It sure does. Let’s examine a query you might get from a VCS or a colleague. Suppose you’re working in the new WideWorldImporters database.


You prefer to see the actual columns returned in the result set at the front of the query, with an alias = format. That’s what I prefer as well, and so I want to have the column list shown as:

PrimaryContactFullName = p.FullName,
PrimaryContactPreferredName = p.PreferredName

A quick CTRL+K, Y and I see this.


My aliases have moved.

This is a great feature for those of you that need consistent formatting. There are other alias options if you prefer the alias at the end.

Update: I originally had this as single quoted aliases, but as pointed out, that format is deprecated, so I wouldn’t use it.

And if you want to change this back, you can have a second formatting preference that you choose to reformat code before sending it back to someone else.

Give SQL Prompt a try today.

Posted in Blog | Tagged , , | Leave a comment

Delaying Patches is Problematic

I was listening to a DevOps podcast from Josh Corman, of Rugged Software. Rugged Software aims to improve security by asking developers and sysadmins to adhere to their manifesto, which recognizes both the importance of software in the modern world, as well as the problems associated by not properly securing and patching software. These are duties that both developers and administrators should perform in their respective roles.

The goal of the Rugged Software is to promote best practices and constant vigilence to create and deploy secure code, and ensure that software installations remain that way. This isn’t a security state, but rather a process. A way to approach software development, deployment, administration, and maintenance to improve the security of our computer systems. This maps nicely to a DevOps approach to software development, with a focus on security. The ideas here include not just patching, but fixing underlying issues, learning from mistakes and from other organizations, improving the skills of their developers and administrators. This is a holistic approach to ensuring security.

This not only includes the software we write, but also the software products, platforms, libraries, etc. that we use. For many of us, this means the Windows host OS, the networking software and connections, http servers, vendor .NET libraries, and more. This also includes database software of all sorts, whether you use an RDBMS like SQL Server, perhaps a search service like ElasticSearch, a caching database like Redis, or some other NoSQL data store.

The idea is that we not only code securely, but also make sure we patch our software. That’s a problem in many cases. In fact, MongoDB has had a security issue for the past few years that was well publicized but still affects many installations. Far too few people have patched their MongoDB databases, and it’s a problem. Data is exposed to anyone on the Internet, and it’s likely someone is taking advantage of this to access the bits. In fact, many hackers exploit flaws in software for which patches are often available. The updates just haven’t been deployed.

Patching software is a challenge, and it seems that there is no shortage of ways in which companies deploy updates. Microsoft is pushing automatic patches. Some applications make these optional. Some apply changes in the background, some require downloads. If you use a service, then patches are seamless, but the changes may break functionality or force a new workflow on the user.

Ultimately I think that we, as an industry, need to mature with the ways in which we patch software. Customers and clients should demand better, and the impact of applying patches should go down. Quality should go up, and while that has happened with some companies, I think we need better patterns and practices that are taught and adopted by more developers. Perhaps more people sharing their techniques, templates, and practices will help others become better at building and managing our software updates.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 5.5MB) podcast or subscribe to the feed at iTunes and Libsyn.

Posted in Editorial | Tagged , | Leave a comment

Reminder: Extending DevOps Practices to SQL Server Databases with Redgate

I’m hosting a  new webinar on the DLM tools from Redgate Software and database DevOps. Tomorrow, Tues, Jan 17, at noon EST, Arneh Eskandari, a great sales engineer, will be demoing the tools and techniques to deploy changes.

You can register here and I’ll talk to you on tomorrow.

Posted in Blog | Tagged , , | Leave a comment