New DB project with SSDT and import existing database

DevOps & databases with SSDT in practice

With this episode I would like to commence long-awaited new series
It’s about DevOps with databases. I’ll start with SSDT, but not exclude other tools (like Redgate ReadyRoll) in the future.
Starting from the very basic things, we will travel through entire process to see and understand how to work, develop, tests and maintenance database project within company.
In this very first episode I’ll introduce SSDT and show up how to create empty database project and how to import existing databases from physical server with 3 methods:
1) Import from database (connect to server)
2) Import from DACPAC
3) Import from script

Let’s get the ball rolling

What is SSDT?

SSDT – SQL Server Data Tools.
It is a free add-in? from Microsoft to Visual Studio which allows you create a database project (like a Desktop Application or Console project in VB/C# but for database).

Why do we need SSDT?

Have you ever written a code in VB(.NET), C#/++ or other to build application? Imagine that you need to write entire application only in your mind or using notepad. Skipping the fact, that a code was being written in that way (C) and writing HTML code is a different kettle of fish and doesn’t count. Hence, using SSDT you have a tool to maintain a database project and it allows you:

• verify query statements (T-SQL)
• validate data/column types
• validate compatibility with specific version of SQL Server (SQL Server 2005! – Azure SQL Database v12)
• prepare repeatable build process
• compare and find the differences (i.e. between project and database)
• apply standards & best practices onto your enterprise or customer
• and eventually, create an automated process of deployment

Certainly, there are much more than those mentioned. If you know another one that I’ve missed – leave it in comments below, please.

How to get it?

You have two options:
1) Choose it from Visual Studio installer. Now it’s one of the modules you can simply select during installation.
Have Visual Studio already installed? No worries. Just run the installer again, do add required module.
2) Go to the website and download appropriate extension depending from your Visual Studio. Afterwards install it and run Visual Studio.

How to check whether I have SSDT on board?
Run Visual Studio and go to the menu: “Help” -> “About Microsoft Visual Studio” Opened window will reveal what modules you’ve installed.
Do find “SQL Server Data Tools” on the list and you’ll find out the version as well.

Other way is to create new project trying to find “SQL Server Database Project” node. Can you see it? Good. You can use SSDT within VS.

New (empty) database project

Once you open Visual Studio – go to the menu: File  -> New -> Project.

New window will be opened and then find the project type: SQL Server Database Project.
Having an empty database project you can either create new objects and create new database from the scratch or import existing one.
You can import the databases following one of 3 methods.

Import database from Data-Tier Application (dacpac file)

One of less popular option, in my opinion. You can create dacpac file using SSMS, for example.
DACPAC file is a zipped file which contains all information about database model. I’ll describe it more in separate post.
Anyways, it’s not a perfect method as export DACPAC file might be very tough, especially when your database has got errors, i.e. unresolved references.
But let’s assume that you already have one.
Right-click on database node in your new project and select Import -> Data-tier application.
Find needed *.dacpac file, decide whether you want to import permissions and/or database settings and click Start.
New window will be opened and the process commences. The summary of import will be created in a log file.

Import database from server

This kind of import is useful when you have access to the SQL server with database to import. The limitation is that you can do it only once.
If you already have some object in the project – this option is disabled. To re-enabled it, you must delete all objects (files) from the project.
Anyways, I use this option whenever I can. Why? You do not miss any objects or properties and you do not rely on other people who might have done the dacpac file (or script) wrongly. Thus, if I can do something faster and without additional risk, why not?
Right-click on database node in your new project and select Import -> Database….
In the Import Database window you need to choose a connection to source database. Even if you don’t have any – click Select Connection…

Pick up one of the existing connections you’ve used previously (tab History) or create new one (tab Browse).
Put a server name (including its instance name when required), select appropriate authentication method and database name (list should be populated when all above information are correct and your access is valid).
Then do click Connect and you’ll be ready to click Start to commence the import process.

Import database from script (sql file)

When you have no access to source server – this is option is for you. The script can be generated by someone else and you can use it to import a model. This option is very handy also in a case when part of a databases has been imported already or if you want to import only selected objects. In other words – this option is reusable.
Further more, one or more files might be imported during one pass. All files are being interpreted and the database objects defined in those scripts are added to your project.
What happens once your objects you’re importing already exist? Don’t worry. SSDT will not overwrite them entirely with a new version. Objects will be merged. Cool, isn’t it?

How create SQL script of database?

Simple. Use our favorite tool – SSMS.
Open SQL Server Management Studio, do right-click on the database name, Task -> Generate scripts.
Then you’ll be able to select specific database objects you want to script. Or simply select Script entire database and all database objects option. In the next step (“Set Scripting Options”) choose between:

1. Save script to file and
   a) create one (big) file or single file per object
   b) Target directory
   c) Encoding of file(s)
2. Save (copy) script to Clipboard
3. Save to new query window – Open new window with generated script in SSMS

Also, you have more control over what the script containing. There is small, shy button Advanced, but very underestimated and powerful.
Before you click Next and generate the script, I would suggest to check all options behind the scenes.
The following options are very important in our case, so set them as TRUE:

• Script Owner
• Script Indexes
• Script Triggers

These options also can be important – depends on your database:

• Script Collation
• Script Change Tracking
• Script Data Compression Options

Why did I say that the Advanced Scripting Options are underestimated?
Due to “Types of data to script”. The property, by default, has value “Schema only”, but you might select one out of:

• Data only
• Schema and data
• Schema only

Data. Yes, you can see it correctly. SSMS CAN script DATA for you. Maybe not in a very efficient way, as it’s scripted as many INSERT statements (one per row), but it’s free. Bear in mind that the script with data is completely useless in our case. Why? SSDT cannot keep data in a project. How to workaround this, I will explain in a future post in this series.

Which an environment should I do import?

The best source of the database would be a production server. In most cases, enterprises which didn’t adopt DevOps culture – the databases between environments are slightly different. In many places, for many reasons. It’s a common scenario, don’t worry. But production is production; the environment which the customers, stakeholders work on. If that works (because it works, right?) – this should be your image, your only version of the truth. Afterwards, you can create branches and import rest of environments to reflect their changes.

Import complete

When the whole process comes to the end you will see the summary of import and copy of that information will be created in a log file. All the objects from the source (depend on which way did you choose) have been created in the project under the folders (see below).

Any questions?

That’s all in this episode. Do you have any question about above or another topic around DevOps and/or databases? Put them in comments or send via a contact form. And please do remember that next episode of the series will appear soon.

Thanks for reading.

Updated @ 08/11/2019
This post is already part of the whole series about SSDT.