Voice of the DBA

Tag: sql server

  • Default Data Masking

    Dynamic Data Masking is a neat new feature in SQL Server 2016. I didn’t think much of it when it was introduced in Azure SQL Database, but since then I realize there is some value here. Even if it’s just making life simpler for developers.

    I’ve been experimenting with this a bit, learning how it works, and one of the options we have for masking data is to use the default option. However, what seems misleading to me here is that this doesn’t use a default from the column. Instead it replaces the values with

    • 4 x’s (xxxx) if the column size is > 4 characters (same for numerals)
    • the number of x’s that fit in the column if the size is < 4.
    • 0 for numbers

    This makes some sense, but not completely. I think I’d prefer to set a default mask for all types, so that I don’t disclose a value is a number or string (or date or anything).  I also see that NULLs are disclosed, another potential area I’d prefer to keep hidden.

    I also think the name is misleading. I’ d prefer to see this called something like xmask, or defaultmask, not default.

    If you want to learn more, you can look at a piece I’ve written to cover how this works, details on the default mask, or check out our list of resources at SQLServerCentral.

  • SQL Server on Linux

    Years ago I wrote an April Fools story about SQL Server running on Linux. For years, this was one of the most popular referrals at SQLServerCentral from Google as many people apparently wanted to get SQL Server on Linux. Well, my joke is now reality. On the official Microsoft blog, there was an announcement of SQL Server running on Linux in preview last week.

    Apparently substantial work has been done in this area if there’s a preview available, with an aim to release a product in mid-2017. That’s somewhat amazing, and I’m very curious about this in two different ways. One is how they technically made this work.  With different threading and schedulers, this is fascinating from a technical perspective.This makes me wonder how much porting work was done, and will continue to be, necessary. Will there be issues keeping the Linux version up to date with the Windows one?

    However I also wonder what the business model is. Are there that many people who want to, or would, run SQL Server that don’t want a Windows OS? I know that the people managing the OS might see SQL Server as just another database application, but I haven’t run into many places that would refuse to install Windows. Maybe this capitalizes on the Azure Data Lake work on Ubuntu? Or the desire to integrate SQL Server more tightly with Hadoop/HDInsight, many installs of which run on Linux? I suspect there are groups more comfortable with Linux and the LAMP stack than Windows, and this gives them the chance to use the incredible SQL Server platform on the OS they are comfortable with.

    This is exciting as a SQL Server professional, giving us more potential opportunities for employment and new challenges for the platform. I expect to see SQL Server continue to grow and prosper as one of the premier relational database platforms in the world. As we move to a more cloud based, hosted model for our software, having our platform be independent of the OS is, I think, a good thing.

    Steve Jones

    The Voice of the DBA Podcast

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

  • Converting SQL Backup Files to MTF

    I got a copy of some backup files recently and needed to restore them. However, I don’t have SQL Backup installed on all my instances. It’s not a big deal to install it, but since this is a one-off, I decided to just decompress the files.

    The documentation for SQL Backup Pro notes that you will have the file converter installed with your SQL Backup installation. The path given is the default, but if you’re like me, you need to track down the actual path. Mine is actually on my e: drive, which means I need to use the full path to call the program.

    The converter is a command line tool, so I need to open a command prompt and then type the path to the file. I could to this many ways, but I started by going to the folder with my .sqb files. I then typed this:

    2016-02-16 11_45_50-Netflix

    This actually worked, but it then put the decrompressed files in the folder with the SQL Backup utility. I then adjusted the command to this:

    “E:\Program Files\Red Gate\SQL Backup 7″\sqbconverter FULL_
    INS1_SQLServerCentral_20160210_000500.sqb H:\SQL Server\Backup\sqlservercentral_20160209.bak MyPassword
    This command actually is seen in the help if you type sqbconverter with no parameters. The format is the .exe, then the input file (the .sqb), the output file (the .bak) and the password.

    The utility gives you the progress as the files are decompressed.

    2016-02-16 10_12_23-Netflix

    Depending on the threads used, you’ll end up with multiple files. In my case, 7 files.

    Now I can restore each of these as a normal, striped backup.

  • The Data Driven Event

    This Thursday, March 10, 2016, at 10am, EST, MIcrosoft will host Data Driven, a live virtual conference. This is slated to be the launch event for SQL Server 2016, though there has been no information released about whether this is also when the platform will RTM.  To date we have had a number of CTPs (CTP 3.3 is the latest), but no Release Candidates (RC) (as of the time of this writing), which we usually see before the final product is released.

    However the CTP has been running well for me. The areas I’ve been testing and looking at seem to work well and I’m rather excited about this version of SQL Server. I don’t think I’ve looked forward to a new version this much since 2008. The in between versions have been somewhat meh, without a substantial number of changes. SQL Server 2012 was better than the R2 and 2014 releases, but still, a limited number of changes.

    However SQL Server 2016 really feels like Microsoft has refocused on the platform, made substantial engineering investments, and is really changing the capabilities of the product. From the Query Store to Always Encrypted to the Stretch Database, we’ve got quite a list of thing to learn about SQL Server 2016. I know I’ve been working through some of these areas and each time it seems I’ve caught up with the changes, there are more items to learn.

    This event is scheduled for two hours on Thursday, so it’s not an all day commitment. I’m sure many of you aren’t looking to upgrade to SQL Server 2016 right away, but this is a chance to learn a bit about what you may look forward to in the future, as well as an opportunity to get excited about the new Data Platform changes from Microsoft. Satya Nadella, the CEO will speak, as well as other executives, and I’m sure, a number of demos. So consider scheduling a meeting for yourself this Thursday and getting excited about the future of data on the Microsoft platform.

    Steve Jones

    The Voice of the DBA Podcast

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