Press "Enter" to skip to content

Category: SQL Server Data Tools

Excluding Schema Comparison Sections

Published 2017-01-24 by Kevin Feasel

Angela Henry shows how to exclude entire sections of a schema comparison in Visual Studio:

When using VS for database projects I typically use my environment specific Publish xml file to deploy changes to my local database when experimenting with code changes.  However, every once in a while I will have to use the New Schema Compare tool from the Tools | SQL Server menu when I have a “one-off” database that I need to synch to my database project.

Quite frequently because these are one-off databases there will be a ton of junk items that are in the database, but not in my database project or lots of objects in my project that aren’t in my database.  Either way I want to ignore those changes.  In the past, I’ve always manually unchecked each item, tedious when you have more than two items to uncheck.

Click through for more details.

Comments closed

New SSMS And SSDT

Published 2016-11-17 by Kevin Feasel

In this blast of new things, Andy Leonard makes mention that there are new versions of Management Studio and Data Tools available:

It’s Release Day! 🙂

New versions of SQL Server Data Tools (SSDT) are available here. SSDT 16.5 and 17.0 (RC1) are available. Also available are Data-Tier Application Framework (DacFx) versions 16.5 and 17.0 (RC1).

New versions of SQL Server Management Studio (SSMS) are available here. SSMS 16.5 and 17.0 (RC1) versions are available for SSMS.

It’s going to be a busy couple of days for some people…

Comments closed

Deployment Contributors

Published 2016-10-25 by Kevin Feasel

Richie Lee discusses an alternative to pre-model scripts:

According to the blurb, deployment contributors can perform custom actions when deploying a SQL script. And one such use of deployment contributors would be to alter index builds to be an online operation. Microsoft also have a Github DACExtensions repo, and this is very useful because, and in the interests of full disclosure, I have never written a deployment contributor myself. This is partly because the repo has some very good examples, including the online index issue (this post nicely covers how to make use of deployment contributors.) I know those that have and have explained how they work very well. But I think there are a few challenges w/r/t deployment contributors:

  • No one has ever heard of them

  • You have to use C#

  • They’re not entirely straightforward.

This is a good discussion of deployment contributors, including why we don’t see them more frequently.

Comments closed

Pre-Model Scripts

Published 2016-10-24 by Kevin Feasel

Richie Lee discusses what makes for a good pre-model script in an SSDT project:

Idempotent: big word, and is used in maths. But don’t let that put you off. What it means in this context is that a script can be re-run and the same result happens. So in the context of altering a primary key, the pseudo-code would look something like this:

if database exists then
if table exists then
if primary key name eq “oldname” then
drop primary key then
add primary key with “newname”

I’m a huge fan of idempotent scripts.  Releases become so much easier when you can guarantee that you can re-run the scripts, and idempotence is a necessary property of continuous integration.

Comments closed

Unit Testing With SSDT And tSQLt

Published 2016-10-13 by Kevin Feasel

Steph Locke looks at adding tSQLt to an existing SQL Server Data Tools database project:

Whilst I won’t be showing code in this, there is a companion sample database project. This is on GitHub and each key stage is shown by a branch of work. This means you can jump in at most stages and work from there. If you need some git advice, check out my quick git in 5 presentation.

The core DB is a super hero themed database.

Database unit testing involves a lot of compromises (because most of what we care about as database professionals tends to be integration tests rather than unit tests), but having imperfect tests generally beats having no tests, so check this out.

Comments closed

SQL Server Connectivity In VS Code

Published 2016-10-04 by Kevin Feasel

Max Trinidad looks at the VS Code plugin for SQL Server connectivity:

After installing, we need to customized their setting by creating connection(s) to our SQL Server. We do this by opening VS Code “User Preferences” and under “Default Settings.json” we search for the “vscode-mssql” settings to be copied over to our working folder “settings.json” file.

I played with this very early on and would like to see it continue to be developed, but it’s no replacement for Management Studio.

Comments closed

Refactoring In SSDT

Published 2016-09-28 by Kevin Feasel

Ed Elliott has an introductory-level article on refactoring code within SQL Server Data Tools:

SSDT helps us to refactor code by automating the actions of:

  • Expanding wildcards
  • Fully qualifying object names
  • Moving objects to a different schema
  • Renaming objects

Aside from this list SSDT also, of course, helps us to refactor code manually with its general editing facilities.

If you aren’t familiar with what SSDT can do, check out this article.

Comments closed

Uncontrolled Environments

Published 2016-09-20 by Kevin Feasel

Ed Elliott discusses database deployments in uncontrolled environments:

There have been a few discussions on stack overflow recently about how to manage deployments in uncontrolled environments, specifically data migrations. The questions were from an SSDT perspective, I don’t think that SSDT is a great choice for these uncontrolled environments and there are some additional requirements for these uncontrolled environments that need some additional thought and care when creating release scripts (whether manually or using a tool).

Ed has some interesting thoughts here, and I agree with the idea that SQL Server Data Tools deployment scripts are not the best choice when you have people changing schema all around you in unexpected ways.

Comments closed

Deploying SSDT Scripts

Published 2016-09-06 by Kevin Feasel

Richie Lee has concerns with database deployments:

At any rate, the script is generated and maybe reviewed….. so then what? In SSDT there is no way to create and deploy script in one step; they are two distinct steps. And even if they were one step, this would still not resolve the issue that troubles me. So what is this issue?

The issue is that by creating a script, and then running the deploy, you cannot be sure that the database is in the exact same state that it was when the initial script was generated. If you don’t already know, SSDT runs a deploy entirely in memory, so as mentioned there is no script created. You have to explicitly create the script as part of the process. Or, if you have already created one, you have to re-create the script.

I’m on the fence here.  In simpler environments, I think Richie has a good point.  But in a complex environment, I wouldn’t even use auto-generated deployment scripts; when you’re changing hundreds of database objects (including adding and modifying columns, backfilling data, modifying indexes, etc.), that automated deployment script is almost guaranteed to fail.  And if it does fail, it could leave you in a state of irreparable harm.

Comments closed

SSDT Supports Always Encrypted

Published 2016-07-07 by Kevin Feasel

Jakub Szymaszek reports that you can now build SQL Server database projects which support Always Encrypted columns:

Always Encrypted uses two types of cryptographic keys: column encryption keys (CEKs) and column master keys (CMKs). A column encryption key is used to encrypt data in an encrypted column. A column master key is a key-protecting key that encrypts one or more column encryption keys. A column master key is stored in a key store that is external the database and is accessible to a client application, for example Windows Certificate Store or Azure Key Vault. The database server does not store the keys of either type in plaintext – it only contains metadata about the keys in column master key and column encryption key metadata objects.

Given that they’re supporting database projects, I wonder if Integration Services is far behind.

Comments closed