Press "Enter" to skip to content

Day: December 22, 2015

SQL Server And Mercurial

Published 2015-12-22 by Kevin Feasel

Joshua Feierman talks Mercurial:

While the choice of version control system is a very subjective and personal one, there are a number of reasons why I think Mercurial is an excellent choice, especially for someone just venturing into the world of version control. Here are a number of those reasons.

I prefer Mercurial to Git because I think the Mercurial commands are a bit easier to understand (although the proliferation of non-terrible Git GUIs has mitigated this somewhat).  Nevertheless, if you love Git, use Git; if you love Mercurial, use Mercurial; if you love SVN, use SVN; if you love Visual Source Safe…use Mercurial or Git…

Comments closed

SSIS And Always Encrypted

Published 2015-12-22 by Kevin Feasel

Jakub Szymaszek links to two articles on using SSIS with an Always Encrypted database.

Using Always Encrypted:

The SQL Server 2016 Always-Encrypted feature is only supported by the ADO.NET  provider currently. It is not supported by the OleDB provider and therefore any OleDB-provider-related transformation tasks such as Fuzzy Lookup will not support Always Encrypted feature.

In the “Execute SQL Task”, parameter binding for some encrypted SQL types is not supported, because of data type conversion limitations in Always Encrypted. The
unsupported types are money, smallmoney, smalldatetime, UniqueIndentifier, DatatimeOffset, time and date.

Lookup Transformations

Add an ADO NET source connect to the table “Customers” (please ref to here get more detail about how to use ADO NET Source to connect encrypted table).

Then create a cache connection manager “Customer Cache” and set the column information as below:

Based on article #2, it looks like you can’t simply use a Lookup transformation on an Always Encrypted column; you need to pull the results into cache first and then query the cache.  That’s not exactly difficult, but if you have an encrypted column, make sure you’re not writing those columns out in plaintext because of the cache option you selected.

Comments closed

Optimizing Update Queries

Published 2015-12-22 by Kevin Feasel

Paul White has an article.  Read it:

The point is that there is an awful lot more going on inside SQL Server than is exposed in execution plans. Hopefully some of the details discussed in this rather long article will be interesting or even useful to some people.

It is good to have expectations of performance, and to know what plan shapes and properties are generally beneficial. That sort of experience and knowledge will serve you well for 99% or more of the queries you will ever be asked to tune. Sometimes, though, it is good try something a little weird or unusual just to see what happens, and to validate those expectations.

Optimizing update queries seems trivial at first, but as Paul shows, we have a few more tools at our disposal than is apparent at first glance.

Comments closed

Identifying Blocked Processes

Published 2015-12-22 by Kevin Feasel

Priyanka Chouhan talks about identifying and handling blocked processes:

In order to maintain data integrity within the database, locks are used on resources like tables, rows, pages etc. by any process that wishes to use them. This is done to ensure multiple process don’t alter the same resources at one time leading to data inconsistency. When a process wishes to lock a resource, it sends a request to the server and the server grants it. However, when a process requests lock on a resource that has already been locked by another process, the request is denied. The requesting process is thus placed on “hold” until the resource it is requesting for isn’t released. In this situation, the requesting process is called a blocked process, and such a process could put a halt on other subsequent processes and activities scheduled on the server.

Thus identifying a blocked process and releasing it requires a DBA team to check the application database blocking. Additionally, here are some other techniques that may be used to find out which processes are creating a block on the server:

My favorite method, not mentioned, is Adam Machanic’s sp_whoisactive.

Comments closed

Get Backup History

Published 2015-12-22 by Kevin Feasel

David Alcook grabs backup history:

It’s a very common task that we have to query backup timings and other bits of info from msdb.

Now its pretty straight forward to select this data for a particular database or use the MAX function for example to return the last backup but how do we get the last 5 FULL backups per database?

MSDB has a lot of useful backup information, so if you’ve never dug into it, I recommend taking your time and seeing what is available.

Comments closed

Git Support In SQL Source Control

Published 2015-12-22 by Kevin Feasel

Mickey Stuewe looks at Git support in Red Gate’s SQL Source Control tool:

At the beginning of October 2015, everything changed. That was when Redgate announced SQL Source Control users could now push to and pull from remote Git repositories.

And not just from within SQL Source Control – from within SQL Server Management Studio (SSMS).

Your developers are probably using Git, so you should too.  If your developers are using Mercurial, I applaud them.  If they’re using SVN, CVS, TFVC, Vault, or anything else (Visual SourceSafe?), flip a coin and decide to use Git/Mercurial or the generally accepted tool…

Comments closed