Press "Enter" to skip to content

Day: April 21, 2016

ENCRYPTION_SCAN Locks

Published 2016-04-21 by Kevin Feasel

Suresh Kandoth explains ENCRYPTION_SCAN in a non-TDE scenario:

There are three types of operations that acquire lock with the resource_type of DATABASE and resource_subtype of ENCRYPTION_SCAN:

– Encryption scan performed during TDE enable/disable

– Bulk Allocations that happen as part of bcp/bulk insert/select-into/index operations, etc

– Sort spills that are done as part of sort operators in the query plan

These locks are taken to serialize operations like bulk allocations and sorts with encryption scan.

Read the whole thing.  It looks like this isn’t by itself a significant issue, but it is interesting to see this lock type against a database without TDE.

Comments closed

Field And Record

Published 2016-04-21 by Kevin Feasel

Michael Swart introduces Field & Record magazine:

I’m definitely a descriptivist. Language is always changing and if a word or phrase gets adopted widely enough, it is no longer “wrong” (whatever that means).

So when I hear “Field” and “Record” they’re acceptable to me. But if I’m explaining something, I don’t want to distract from the thing I’m saying. And from that point of view, I try to use “Row” and “Column” because I don’t know anyone who blinks at those terms.

Entity and Attribute or bust.  That’s my philosophy.

Comments closed

Installing ODBC Drivers

Published 2016-04-21 by Kevin Feasel

Steph Locke shows how to install the SQL Server ODBC drivers in Ubuntu:

Did you know you can now get SQL Server ODBC drivers for Ubuntu? Yes, no, maybe? It’s ok even if you haven’t since it’s pretty new! Anyway, this presents me with an ideal opportunity to standardise my SQL Server ODBC connections across the operating systems I use R on i.e. Windows and Ubuntu. My first trial was to get it working on Travis-CI since that’s where all my training magic happens and if it can’t work on a clean build like Travis, then where can it work?

Now I can create R functionality that can reliably depend on SQL Server without having to fallback to JDBC. A definite woohoo moment!

Thanks to Steph for putting together this script.

Comments closed

Restoration Options

Published 2016-04-21 by Kevin Feasel

Richie Lee covers the WITH RECOVERY, WITH NORECOVERY, and WITH STANDBY database restoration options:

Similar to NORECOVERY except that the database will accept read only connections. To do this any uncommitted transactions in the backup will be rolled back and stored in a transaction undo file (tuf.) Whilst users are running queries against the database no further restores can continue until all queries are complete (though this is not the case with log shipping.) When the next restore occurs, those uncommitted transactions in the tuf file will be rolled forward and the next log is restored.

Diving into STANDBY mode was quite helpful.  I’ve never needed to restore a database into standby mode, but could see it being useful for bringing back deleted records.

Comments closed

Migrating TFS

Published 2016-04-21 by Kevin Feasel

Dave Mason has notes on migrating TFS from one server to another:

If you are migrating, but want to keep the databases on SQL 2012 Express, then you can skip this part. I wanted them moved to my SQL 2014 instance. So I did a traditional backup/restore from SQL 2012 Express to SQL 2014. I took new backups of the SQL 2014 databases, and then uninstalled SQL 2012 Express. Then I had to configure TFS to connect to a different SQL instance. Within the web.config file (%ProgramFiles%\Microsoft Team Foundation Server 12.0\Application Tier\Web Services\web.config), I found an application setting named “applicationDatabase”. I made a backup copy of web.config first, then I changed the “applicationDatabase” value. It should be in a recognizable format if you’re familiar with SQL Server connection strings. You can also make this change within IIS. It was there that I noticed a few other settings that contained SQL connection strings. Check out the following in IIS and change settings as needed:

Dave has lots of screen shots to make the process easier to understand, but my main takeaway is that for the most part, migrating TFS  is a huge pain…

Comments closed