Author: way0utwest

  • A View of the Cloud

    Amazon has been an amazing digital company in the last twenty years. I remember making my first order from them, unsure of whether the online bookstore would work better than browsing locally, or if I’d even get my books. I watched them transform into a great shipping company that could sell anything, to a digital reading company with the Kindle, and even an amazing cloud hosting provider with world class programming platforms. At each stage, Amazon has grown to become even more efficient and seductive, slowly gaining more of my business over the years.

    Whether you like the business model and practices of Amazon or not, part of the reason Amazon has become a successful company is that they have an incredible software development process and great developers, both of which have produced a software stack that is very impressive. Apart from their web site, which is impressive, they host their Amazon Web Services, which is a dizzying array of services that can be purchased by anyone, at any time, and get some software up and running quickly. Even if you look at their free tier, it’s very impressive in the number and type of services, including database access.

    I ran across a piece on the 10 year anniversary of AWS that contains some software development lessons from Werner Vogels, CTO of Amazon. It’s a good list, and while some might not apply to your particular environment in the same way, it’s a good list of principles to follow. Certainly planning to evolve and to handle failures is something that most of us need to consider. Including security from the ground up, however, is one thing that many of us know, but don’t have good patterns, practices, or habits to follow. Even those companies that work in security conscious areas often leave security concerns until they are well into, or even finished, with their core software development.

    The one area that I think is going to become more important for all of us in the future is understanding resource usage. Certainly we’ve needed to know some baselines and expected growth in the past to plan for upgrades and future needs. However, with the move to VMs and cloud services, how many of us really know what resource usage we would require for peak workloads and when. I suspect most people don’t really know how to map their instance resource usage across different virtual systems, much less evaluate whether their system would be cost effective in a cloud scenario. A better understanding of what resources we really use is something that more and more organizations are going to expect from their technical professionals. I only hope we get some tools to help us here as this is a complex topic.

    The move to cloud services is going to happen for many of us. We might not move all our data, or even much of it, but as we experiment with new platforms, as companies work to avoid capital expenditures, and as the platforms and services mature, more of us will end up with some cloud platform in a portion of our organization. During your journey, or even while you still work with on-premise systems, I’d keep the principles in Mr. Vogels’ article in mind. They’ll help you to build better software in the future.

    Steve Jones

    The Voice of the DBA Podcast

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

  • 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.