Press "Enter" to skip to content

Curated SQL Posts

MergeUi Update

Published 2015-11-13 by Kevin Feasel

Ed Elliott has a new update to his MergeUi tool:

The way MergeUi used to work was that it enumerated the schemas and tables in a project and let you create a merge statement in the post-deploy script. The problem with that is that you may want to have different versions of the table for different environments or you may want to put the merge in a different script and either reference it using :r imports or not reference it at all.

The new way it works is that instead of enumerating tables it now enumerates script files (pre, post, included, not included etc) and lets you choose which table to add whether or not the table has been added before.

I’ve not used this tool before, but it’s good to know that it’s available via Github.

Comments closed

Always Encrypted

Published 2015-11-13 by Kevin Feasel

Kenneth Nielsen takes a look at Always Encrypted:

The way Microsoft have implemented this always encrypted feature, is to let all the data in the tables be encrypted. The application that needs to look at data will have to use the new Enhanced ADO.net library, which will give your application the methods to de/encrypt data.

This way, the only way to insert data into a table, which contains encrypted columns, is to use parameterized insert statements from your application. It is not even possible to insert data from SQL Server Management Studio, if we try, the statement will fail.

This way we ensure that only the persons using the application will be looking at un-encrypted data, thus reducing the number of people with a direct access to sensitive data.

If you go down this route, it looks like the only method available for modifying data is going through ADO.NET, although that could change later.  My biggest concern here is how much of a performance hit—if any—systems will take.

Comments closed

Connect To Azure VM Via SSMS

Published 2015-11-13 by Kevin Feasel

Derik Hammer shows you how to connect to an Azure VM hosting SQL Server:

After you provision a Microsoft Azure VM with SQL Server there are a few more steps that you need to take to make remote connections. The procedure below starts with a fresh Azure VM provisioned and walks through the process of establishing a connection via SQL Server Management Studio, installed on an on-premises work station.

Note that this is Azure IaaS, not Azure SQL Database.

Comments closed

DBA Morning Checklist

Published 2015-11-13 by Kevin Feasel

Pieter Vanhove has published his Policy-Based Management-based DBA Morning Checklist and has some post-Summit additions:

Optimize for Ad Hoc Workloads

The policy is going to check if the server setting Optimize for Ad Hoc Workloads, is set to True. By default, this setting is set to False.
The optimize for ad hoc workloads option is used to improve the efficiency of the plan cache for workloads that contain many single use ad hoc batches. More information can be found on https://msdn.microsoft.com/en-us/library/cc645587.aspx

I don’t see any downside by enabling this setting.

Not many shops use PBM, so I’m happy to see Pieter contributing this to the general community.

Comments closed

SQL Server 2016 IFI

Published 2015-11-12 by Kevin Feasel

Nic Cain has an outstanding blog post on enabling Instant File Initialization in SQL Server 2016, specifically wondering what happens when group policy explicitly prohibits setting Perform Volume Maintenance Tasks privileges:

Much to my surprise the virtual SQL account showed up in the PVMT secpol setting. I had no idea how it got there. Reviewing the setting I was able to confirm that the account I used for install was not able to make any adjustments and yet somehow the permissions were set.

I’m happy to hear why I’m wrong, but I’d consider this a reasonable instance of privilege escalation:  I may not want the DBA to be able to perform volume maintenance tasks on just any server, but I do want him to do it on SQL Server instances.

Comments closed

Stop Using Datetime(?)

Published 2015-11-12 by Kevin Feasel

Kenneth Fisher says to stop using datetime and start using date, time, and datetime2(x):

Why is everyone still using the DateTime datatype exclusively?

Back in SQL 2008 we gained a whole new range of date/time datatypes. Isn’t it about time we started to use them?

In my experience, most of the issue is supporting legacy app code which chokes on these types.  You’d think people would have updated that .NET 2.0 code, but not always.

Comments closed

Why Virtualize?

Published 2015-11-12 by Kevin Feasel

David Klee answers why you might still want to virtualize a single-instance SQL Server which resides on a single host:

That’s a wonderful question, and I get asked this all the time.

I can justify the desire for virtualization in the scenario you described. There are a number of reasons to consider virtualization given those constraints.

Virtualize everything, as Klee suggests.  The worst case is that administration gets slightly more complex, but the advantages are worth it.

Comments closed

Installing And Using SQL Server R Services

Published 2015-11-11 by Kevin Feasel

I have three blog posts on installing and using R in SQL Server.

First, installing SQL Server R Services:

I’m excited that CTP 3 of SQL Server 2016 is publicly available, in no small part because it is our first look at SQL Server R Services.  In this post, I’m going to walk through installing Don’t-Call-It-SSRS on a machine.

Then, using RODBC to connect a Linux machine with RStudio installed to a SQL Server instance:

Getting a Linux machine to talk to a SQL Server instance is harder than it should be.  Yes, Microsoft has a Linux ODBC driver and some easy setup instructions…if you’re using Red Hat or SuSE.  Hopefully this helps you get connected.

If you’re using RStudio on Windows, it’s a lot easier:  create a DSN using your ODBC Data Sources.

Finally, using SQL Server R Services:

So, what’s the major use of SQL Server R Services?  Early on, I see batch processing as the main driver here.  The whole point of getting involved with Revolution R is to create sever-quality R, so imagine a SQL Agent job which runs this procedure once a night against some raw data set.  The R job could build a model, process that data, and return a result set.  You take that result set and feed it into a table for reporting purposes.  I’d like to see more uses, but this is probably the first one we’ll see in the wild.

It’s a preview of a V1 product.  Keep that in mind.

The first and third posts are for CTP 3, so beware the time-sensitive material warnings.

Comments closed

T-SQL Tuesday #72

Published 2015-11-11 by Kevin Feasel

Mickey Stuewe hosted T-SQL Tuesday this month.  Her topic:  data modeling gone wrong.  A few choice posts on the topic follow.

Mickey herself looks at a case in which surrogate keys didn’t quite do the trick:

One of the problems I’ve seen with careless use of surrogate keys are the duplication of natural keys. Quite often it’s overlooked that the natural key still needs to have a unique constraint. Without it, the reporting team ends up having to use MAX or DISTINCT to get the latest instance of the natural key, or SSIS packages are needed to clean up the duplicates. This can be compounded with many-to-many tables.

Surrogate keys are not replacements for natural keys; they are physical implementation mechanisms to make your life easier.

Rob Farley wants you to think about design and whether your warehouse is built in a way that helps the business:

Many data professionals look at a data warehouse as a platform for reporting, built according to the available data sources. I disagree with this.

The models within a data warehouse should describe the business. If it doesn’t, it’s a data model gone wrong.

What is the central thing that your business does? What is the main interest point? What do you need to look after? For me, this forms the core of the warehouse.

Thomas Rushton says name your stuff right.  Picking the right name can be difficult.  “Field1” probably isn’t the right name, though.

2 Comments

Back Those Things Up

Published 2015-11-11 by Kevin Feasel

Brian Krebs reminds us to back up all the things:

The tools for securely backing up computers, Web sites, data, and even entire hard drives have never been more affordable and ubiquitous. So there is zero excuse for not developing and sticking with a good backup strategy, whether you’re a home user or a Web site administrator.

PC World last year published a decent guide for Windows users who wish to take advantage of the the OS’s built-in backup capabilities. I’ve personally used Acronis and Macrium products, and find both do a good job making it easy to back up your rig. The main thing is to get into a habit of doing regular backups.

There are good guides all over the Internet showing users how to securely back up Linux systems (here’s one). Others tutorials are more OS-specific. For example, here’s a sensible backup approach for Debian servers. I’d like to hear from readers about their backup strategies — what works — particularly from those who maintain Linux-based Web servers like Apache and Nginx.

This article doesn’t directly relate to SQL Server, but it does act as a nice reminder:  go make sure you have good backups.  Of everything.

Comments closed