SQL Compare

How to build multiple database versions from the same source: pre-deploy migration scripts

This is the third post in a three-part series that explains how to maintain a single declarative source of truth in version control for a SQL Server database, which can be deployed to multiple environments, despite the fact that some environments have known customizations. fl In part 1, I explained how to manage objects that Read more

SQL Doc

Getting started with SQL Doc and PowerShell

SQL Doc keeps everyone in the development team informed about a database. It provides more information than what is in source control, including server settings, and it formats and color-codes the information so that it is as easy to read as possible. It generates the source of all of the metadata of a database, along Read more

SQL Clone

SQL Clone and backups protected with TDE – part 2

My last article demonstrated that you can use SQL Clone to make a copy of a live database, even when that database is protected with Transparent Data Encryption (TDE). However, not everyone will want to use SQL Clone against a live database, especially a production database. Fortunately, SQL Clone allows you to build an image Read more

SQL Clone

SQL Clone and databases protected with TDE – part 1

SQL Clone is designed to make it easy to provision copies of a database for development and test environments in seconds. Often, these ‘clones’ are copies of production databases, which contain sensitive data that needs to be protected from unauthorized access. Transparent Data Encryption (TDE) is one technique that many organizations have implemented in order Read more

SQL Compare

Automating DacPac deployments using the SQL Compare command line and PowerShell

My previous article, How to compare two DacPacs using SQL Compare, described how to use the SQL Compare GUI to compare two DacPacs, and generate a synchronization script. This article takes it one step further, showing how to use PowerShell and the SQL Compare command line to automate the generation of a synchronization script, to Read more

SQL Compare

Eliminating ‘noise’ from database deployment scripts using SQL Compare project options

A database comparison tool like SQL Compare removes a lot of the pain behind the task of comparing SQL Server database schemas, generating a synchronization script, and then deploying the changes from the source to the target database. However, depending on how you have SQL Compare configured, there may still be some manual work to Read more

SQL Prompt

Quick SQL Prompt tip – restore your tabs, even if you closed them by mistake

It’s sometimes too easy to close SQL Server Management Studio (SSMS) without saving your work. It’s also easy to get rid of tabs and scripts that you later wish you had back. SQL Prompt can help with both these problems. Take a look at the Options for SQL Prompt and you’ll find a set of Read more

SQL Prompt

Quick SQL Prompt tip – picking the columns you need for speedy results

One of the poor practices that I see so many people doing in code is using SELECT *. This is poor practice because it takes more time to complete for clients, it’s an unnecessary use of resources on the client, server and network, and it can fill the buffer cache with rarely-used data. For example, Read more

SQL Compare

How to build multiple database versions from the same source: post-deploy scripts

This is the second post in a three-part series that explains how to use SQL Compare to maintain a single source of truth in version control for a SQL Server database which can be deployed to multiple production instances, despite the fact that some production instances have customizations. My previous article explained how to use SQL Read more

SQL Prompt

Quick SQL Prompt tip – script objects as ALTER in two clicks

Working in a large database can be difficult at times. While many of us might learn the meanings and definitions of most objects, it’s easy to forget the exact ways in which some objects work, or what the behavior is in certain calls. This is one place where having tools that assist you like SQL Read more