Author: way0utwest

  • Get Your SQL Server Product Key with Powershell #dbatools

    I really like the dbatools project. This is a series of PowerShell cmdlets that are built by the community and incredibly useful for migrations between SQL Servers, but also for various administrative actions. I have a short series on these items.

    There are lots of tasks that are easily accomplished with both PowerShell and T-SQL.

    UPDATE: The cmdlet was renamed to Get-DbaProductKey

    Get-SqlServerKey is a command that will find your product keys. It’s not often you might need this, but it’s a pain to track them down from the registry and be sure you get them for all your instances.

    Here’s the view from one of my development machines:

    2017-01-27 15_31_08-powershell

    There is a limitation here that appears to have issues with SQL Server 2016, but it’s still helpful for other versions. You can also specify a list of servers in a file for this to use, as well as using credentials to connect.

    A quick, handy item, and one that should make auditing (pre-2016) SQL Servers easy.

  • Your Favorite Feature that Needs Work

    The SQL Server platform has grown tremendously since I started working with SQL Server v4.2. We’ve had various subsystems added, lots of enhancements, and even new languages. There have also been a few parts of the platform that have been discontinued, such as Notification Services and English Query. There are items that are deprecated, though far fewer that have actually been discontinued from the product. I’m not sure if I think that’s better or worse for all of us.

    Apart from the deprecated, discontinued, and removed features, there are also lots of features in the platform that are stale. They receive very little development, and perhaps are very lightly featured on the What’s New pages. Or they never get any development from version to version.

    This week, I’m curious what features you use or like that need help. SQL Server v.Next is in development, and while I don’t think major changes will be made between now and RTM, perhaps there are improvements that could be made in some areas. I’m sure there are a few SQL Server developers at Microsoft that have time to ask for a few more pull requests from their particular area of development. With that in mind…

    What feature in SQL Server do you really like and wish would be improved in the future?

    Your idea would be large or small. Perhaps you have some language enhancement. Maybe you run setup regularly and there is a change that might streamline that process. It may be that replication conflict resolution is a constant problem for you and you’d like a few development hours spent on this. It could be anything that you use, or would like to use. Perhaps you have an idea for an improvement, or maybe you just want something done. Let us know today.

    The place to log bugs or feature requests is at the SQL Server Connect site. Post your idea there, then put a link in the discussion and make a case for why people should vote for it. Who knows? Perhaps you’ll get some improvement for the feature you like in the next version of SQL Server.

    Steve Jones

    The Voice of the DBA Podcast

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

  • Using a DMK in TDE

    When you setup TDE, you need to create a DMK (Database Master Key) in the master database if one doesn’t exist. This key forms the basis of a chain of protection for your data inside of the SQL Server instance. The hierarchy is:

    • The DMK exists in the master database. This protects a certificate or asymmetric key by encrypting it.
    • The certificate in turn encrypts the DEK in a database using TDE. This certificate is required to open the DEK in the database, or open the DEK during a restore operation.
    • The DEK encrypts the data.

    There is only one DMK in each database, so the DMK in master for TDE could be used to protect multiple certificates, asymmetric keys, or symmetric keys that exist in master for use by TDE or any other encryption mechanism.

    A particular DMK is not needed to restore a TDE database on another instance. Rather the new instance of SQL Server should have a DMK created in the master database. Then the backup of the certificate is restored on the other instance, protected by the DMK there. Once this is complete, the TDE database can be restored.

    If you’d like to know more about what a DMK is, I’ve written about it in a previous post.

  • The Great and Powerful Internet of Things

    Will we have the IoUT (Internet of Useful Things? As some have said, the IoS (Internet of Sh**e) is really what we have because of the poorly built, poorly secured hardware and software devices. It seems that almost every month I see new devices introduced in the consumer space, most of which aren’t well designed for widespread use, and certainly aren’t very durable. There are, of course, exceptions, including a few I’d like, but for the most part, the rush to market means that many of the IoT devices sold aren’t much better than most of the home built Kickstarter projects. That’s not to say Kickstarter (or Indiegogo or any other site) doesn’t produce good products, just that some aren’t.

    We’ve got an infographic from Website Guide today that you can examine. It’s an interesting item, which is why I published it. There are a lot of great possible applications for IoT style devices. Plenty of businesses and industries are seeing the benefits of using sensors and devices that are (semi-) smart and connected to a network. Using well written applications, companies can come up with new ways of gathering and using information. When these systems are well designed, this can result in lower costs, higher sales (or usage), happier customers, or all three.

    As you scroll down the infographic, you’ll see the dark side of IoT, which is getting more press all the time. Security is the number one concern, as far, far too many devices don’t include the necessary security to protect either the device or the end user. I did watch an IoT demo at Live!360 last year using the Azure IoT hub, and was glad there was some authentication and ability to shut down devices that might be compromised or misbehaving, but far too many IoT systems aren’t well secured.

    That’s disconcerting for me as a database professional. I expect that more and more companies will use IoT in some way. I’d even like to find time for some IoT sensors around the ranch, tracking horses and ensuring they’re all walking around every day. The thing that concerns me is what happens when someone finds ways to hijack or hack a device? Even sensors could be hijacked. What if you’re processing temperature information in dynamic SQL and someone decides to heat and cool the sensor to create certain values? Are you sure your database isn’t vulnerable?

    These concerns leave out the potential for the misuse of any data that’s collected about users, especially if users aren’t aware of what’s being collected and its use. With so many ways of collecting data now, I’m not even sure that anyone has a good handle on all the data collected about them, not to mention how it’s being stored, secured, and used.

    I think there’s incredible potential for some great IoT applications and data in the future. All the forecasts and spending seem to point towards the next 5-10 years being a period where there will be an explosion of the number of types devices built and deployed. Those devices will produce a tremendous amount of data for us to manage in databases. We will have big challenges in storing, indexing, managing streams, and certainly, the pruning of old data.

    Steve Jones

    The Voice of the DBA Podcast

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