Deborah's Developer MindScape






         Tips and Techniques for Web and .NET developers.

Archive for SQL Server Data Tools (SSDT)

August 8, 2013

SSDT Data Compare

Visual Studio 2010 had a data compare feature that was removed in Visual Studio 2012, which drew much criticism. Now its back!

If you don’t see the Data Compare option in the SQL menu of Visual Studio, see this prior post to get the update.

See this prior post for more information on SSDT.

Use the Data Compare option to compare the data in two databases.

This example compares the data in the current development database against the data in the test database.

1) SQL | Data Compare | New Data Comparison…

image

If you have used the Visual Studio 2010 data compare, you will recognize this dialog.

2) Select the Source Database

image

The current development database is selected

3) Select the Target Database

image

The current test database is selected.

4) Click Next and select the Tables and/or Views to include.

image

By default, all tables are included.

5) Click Finish.

The results appear in a tab:

image

Click any cell to view the detail in the bottom grid. For example, selecting the Customer row, Only in Source column displays the following in the bottom grid:

image

Uncheck any row that should not be updated. This gives you fine control over which data to change.

6) Click Update Target.

This updates the target database with the selected changes.

Alternatively, you can click Generate Script to create a changes script with all of the data changes.

NOTE: I have a development database with about 100 tables. When I do the data compare, two of the tables are never included in the list of tables (step #4 above). I had this same problem in the Visual Studio 2010 data compare. The two tables it skips are simple tables. I provide this note as a warning to check your results when using this feature.

Use this feature any time you need to compare data between databases, generate data scripts, or update a database with data from another database.

Enjoy!

For more information on this and other Visual Studio 2012 features, see my Pluralsight course: Mastering Visual Studio 2012.

August 7, 2013

SSDT Schema Compare

As part of the Visual Studio SQL Server Data Tools (SSDT), the Schema Compare option compares the structure of the database tables, stored procedures, views, and other database objects. It shows you how two data sources are the same and how they are different.

See this prior post for more information on SSDT.

Use the Schema Compare option to:

  • Compare one database against another
    • Development vs Test or Test vs Production, for example
  • Compare a database against a SQL Server Database Project
    • Development database vs updated SQL Server Database Project, for example
  • Compare a database against a DACPAC
    • Production database vs packaged schema, for example

In this example, the current task added a DisplayOrder field to the CustomerType table. You want to compare your current development database to the current test database to confirm your changes.

1) SQL | Schema Compare | New Schema Comparison …

image

2) Select the Source

image

For the source, you can select:

  • A SQL Server Database Project from your solution.
  • A SQL Server database.
  • A packaged data-tier application file (DACPAC)

For this example, the current development database is selected.

3) Select the Target

image

You have the same choices for the target:

  • A SQL Server Database Project from your solution.
  • A SQL Server database.
  • A packaged data-tier application file (DACPAC)

For this example, the current test database is selected.

4) Click the Compare button in the Schema Compare toolbar.

image

The top portion of the result displays the changes that were found. Drill down to see the detail. In this example, the schema compare found a table change. Drilling down, you can see that the DisplayOrder column has a "+" action, meaning it was added.

Click on any change to see the Object Definitions, which shows the source and target scripts.

From here you can use the toolbar to perform other actions such as:

  • Click the Update button to update the target from the source.
  • Click the Generate Script button to generate an update script file.
  • Click Options to set options for the script
    • Block on possible data loss is a key option you may want to set or allow depending on the change.

Comparing the current database project to the current Test database yields the same result:

image

Use the schema compare any time you want to compare a database to another database, SQL Server Database Project, or DACPAC (or any other combination of these).

Enjoy!

For more information on this and other Visual Studio 2012 features, see my Pluralsight course: Mastering Visual Studio 2012.

SSDT SQL Menu Options

The SQL Server Data Tools (SSDT) in Visual Studio include features in three areas of Visual Studio:

Later posts cover the key options in the Visual Studio SQL menu:

  • Schema Compare
  • Data Compare
  • Transact-SQL Editor

But arguably the most important SQL menu option is: Check for Updates …

Microsoft provides updates to the SSDT separate from the Visual Studio updates. To ensure you have the most recent updates, select SQL | Check for Updates…

This option displays a dialog with the SSDT components and their installed versions and gives you the option to update if you don’t have the current version.

image

NOTE: The Data Compare option, for example, was added in the June 2013 release of SQL Server Data Tools. If you don’t see a Data Compare feature in your SQL menu, give the Check for Updates option a try.

Enjoy!

For more information on this and other Visual Studio 2012 features, see my Pluralsight course: Mastering Visual Studio 2012.

August 6, 2013

Publishing Your Data Scripts

An earlier post detailed how to publish the schema and stored procedure scripts in your SQL Server Database Project. Two other recent posts (one using Insert statements and the other the Merge command) covered how to add a reference data script to your database project. But just adding the reference data script to the database project won’t include it in the publishing process.

See this link for an introduction to the 2012 SQL Server Database Projects.

So how do you include your reference data scripts in the publishing process?

1) Create a new script in the same directory as your data scripts.

Right-click on the folder containing your data scripts and select Add| Script.

image

2) Select a Post-Deployment Script

Visual Studio 2012 comes with several script templates:

image

Pick the Post-Deployment Script to ensure that the data is populated after all of the tables and stored procedure scripts are executed.

The generated script appears as follows:

image

3) Include each data script in the post-deployment script.

image

Notice that the entered command shows syntax errors. That is because the suggested syntax for the post-deployment script is SQLCMD syntax. When using SQLCMD syntax, you need to let the editor know by turning on SQLCMD mode:

image

Include each data script in this post-deployment script in an appropriate order. If a data script contains foreign keys to data in another script, the other script must be before it in the list.

For example, say you want to populate the Customer table with a sample customer row. The Customer table has a foreign key to the Customer Type table. So populating the Customer table must be *after* populating the Customer Type table. Otherwise the foreign key relationship will fail.

In this example post-deployment script, the Customer data script must then be included *after* the Customer Type data script.

Use this technique any time you want to include data scripts when publishing your database project.

Enjoy!

For more information on this and other Visual Studio 2012 features, see my Pluralsight course: Mastering Visual Studio 2012.

August 5, 2013

Creating a Merge Script for your Reference Data

When you create a database project data script using the SQL Server Object Explorer, it provides a set of SQL Insert statements. So it requires that you delete all of the existing table data and insert the new rows. But what if you don’t want to delete all of the existing data? That is where a Merge script can help.

See this link for an introduction to SQL Server Object Explorer.

See this link for an introduction to the 2012 SQL Server Database Projects.

In this prior post, we used the SQL Server Object Explorer to generate a database project data script which looks like this:

image

But there are many cases where you don’t want to delete all of the existing data. Here are some examples:

  • You allow the user to change the display order, but not the Id or type name.
  • You allow the user to add new types, but not change the existing types.
  • You want to provide some initial data, but allow the user to change it in any way they want.

In these cases, executing a script that first deletes all of the rows and inserts all new one just won’t do. That is where a Merge script can help.

NOTE: To use a Merge script, your database must have its compatibility level set to a minimum of SQL Server 2008.

image

A MERGE script looks like this:

— Reference Data for the CustomerType table
SET IDENTITY_INSERT [CustomerType] ON
 
MERGE INTO [CustomerType] AS Target
USING (VALUES
            (1, N’Corporation’, 2),
            (2, N’Individual’, 1),
            (3, N’Educator’, 3),
            (4, N’Government’, 4))
AS Source ([CustomerTypeId],
            [TypeName],
            [DisplayOrder])
ON (Target.[CustomerTypeId] = Source.[CustomerTypeId])

WHEN MATCHED AND
            (Target.[TypeName] <> Source.[TypeName]) THEN
UPDATE SET
            [TypeName] = Source.[TypeName],
            [DisplayOrder] = Source.[DisplayOrder]

WHEN NOT MATCHED BY TARGET THEN
INSERT([CustomerTypeId],
            [TypeName],
            [DisplayOrder])
VALUES(Source.[CustomerTypeId],
        Source.[TypeName],
        Source.[DisplayOrder])

— delete rows that are in the target but not the source
WHEN NOT MATCHED BY SOURCE THEN
DELETE;
 
GO
 
SET IDENTITY_INSERT [CustomerType] OFF
GO

Let’s talk through what this script does:

  • MERGE INTO: Defines the target table name.
  • USING: Defines the default set of data.
  • AS Source: Defines the set of fields.
    • These must match the fields included in the default set of data.
    • They do not have to include all fields in the table.
  • ON: Defines how the Target (sample data) is to be compared to the Source (existing table data).
    • Normally this is matched up on the primary key.
  • WHEN MATCHED: Defines the script to execute when there is a match.
    • Since the ON in this example defined the primary keys, there is a match when the primary keys match.
  • AND: You can optionally add criteria to the match.
    • In this example, it will only execute the script if there is a primary key match AND the type name does not match.
    • You can add any other clauses here.
  • UPDATE SET: Defines the script to execute when the "WHEN" clause is true.
    • In this case, it will update both the TypeName and the DisplayOrder.
  • WHEN NOT MATCHED BY TARGET: Defines the script to execute when there is no match on the target (sample data).
    • So if there is no existing table data that matches the sample data, the script is executed.
    • In this case, if there is no matching primary key in the existing table data, it inserts the values.
  • WHEN NOT MATCHED BY SOURCE: Defines the script to execute when there is no match on the source (existing table data).
    • So if there is no sample data that matches the existing table data, the script is executed.
    • In this case, any rows not in the sample data are deleted.
  • ;: The MERGE script must end in a semicolon (;)

If you want to allow the user to add rows, remove the "WHEN NOT MATCHED BY SOURCE". Be sure to retain the ending semicolon.

Use a Merge script any time you want more control over how your data script is processed.

Enjoy!

For more information on these and other Visual Studio 2012 features, see my Pluralsight course: Mastering Visual Studio 2012.

Scripting Reference Data with SQL Server Object Explorer

Database projects are great for scripting the tables and stored procedures for a database, but what about data? There are often times that you need to pre-populate tables with reference data.

See this link for an introduction to SQL Server Object Explorer.

See this link for an introduction to the 2012 SQL Server Database Projects.

For example, you may have a type table that contains the set of valid types. In this case, we have a CustomerType table. We want to retain the valid set of data for that table in a data script.

image

To script the data from a table into a database project.

1) Create a folder in the database project for your data scripts.

Right-click on the Database Project and select Add | New Folder. I named my folder: "Reference Data"

This step is optional, but it helps to keep all of your data scripts together in one place.

image

2) Create a script for the new data.

Right-click on the new folder and select Add | Script. Select the "Script (not in build)" template. I name my data scripts the name of the table.data.sql.

image

Visual Studio then opens a nice empty window for you to type in all of your data. Not Fun!

image

3) Instead of typing in all of your data, script the data from the SQL Server Object Explorer.

Open SQL Server Object Explorer, right-click on the table, and select "View Data".

image

The table data is then displayed:

image

4) Select the Script icon (see red square in the above diagram).

The script to create the data is generated:

 

5) Copy the data and paste it into the script you defined in your database project.

NOTE: There are several different ways to accomplish this same task.

  • Instead of creating the database project script, generating the data script, and copying the contents of the generated data script into the database project script, you can just generate the data script and save it into your project.
  • Alternatively, you can click the Script to File icon to the right of the Script icon to generate the script to a specific script file.

6) The final step is to add a Delete statement to delete any existing rows.

This ensures that no rows existing before attempting to insert the pre-defined rows:

image

Use one of these techniques to script any reference data into your database project.

Enjoy!

For more information on this and other Visual Studio 2012 features, see my Pluralsight course: Mastering Visual Studio 2012.

August 2, 2013

Database Projects and Refactoring

There are often times that you need to update a database schema. But if that database is already in production, some changes, such as simply changing a column name, are difficult because of the potential data loss.

For an introduction to Database Projects in Visual Studio 2012, please see this post.

Let’s look at an example.

The Customer table is defined as follows:

image

In a later iteration, a feature is implemented to maintain customer types, so we need to change the CustomerType field to a CustomerTypeId.

image

Seems easy enough. But click the Update button and you get this:

image

The column is just being renamed, why would data loss occur?

The old SQL Server tools didn’t understand the concept of just changing a column name. Rather, they saw the change as a DROP and ADD. If you click Generate Script on the above dialog, you can see the DROP and ADD in the script:

ALTER TABLE [dbo].[Customer] DROP COLUMN [CustomerType];

GO
ALTER TABLE [dbo].[Customer]
    ADD [CustomerTypeId] INT NULL;

So if the data looks like this before you run the update script:

image

After, it looks like this:

image

Bummer! That won’t due when updating the production database!

The latest SQL Server Data Tools includes the ability to refactor the database, including changing a column name, without data loss.

Instead of updating the database directly, edit the column name using the Database project table script as shown below:

image

Visual Studio then creates a refactor file:

image

When you publish the database, the DACPAC includes this refactor file. So the generated script to update the production database looks like this:

GO
PRINT N’The following operation was generated from a refactoring log file 07117693-7a12-450c-b0a0-bc5ad854d94e’;

PRINT N’Rename [dbo].[Customer].[CustomerType] to CustomerTypeId’;

GO
EXECUTE sp_rename @objname = N'[dbo].[Customer].[CustomerType]’, @newname = N’CustomerTypeId’, @objtype = N’COLUMN’;
GO

No data loss!

Use the refactoring features of the Database Project in Visual Studio 2012 any time you need to rename a column or perform other schema changes that could result in a data loss.

Enjoy!

For more information on this and other Visual Studio 2012 features, see my Pluralsight course: Mastering Visual Studio 2012.

July 31, 2013

Publishing a Database Project

Visual Studio 2012 introduced a new SQL Server Database Project. This post first covers what happens when you publish a Database Project. It then details how to publish your Database Project.

See this link for an introduction to the Visual Studio 2012 SQL Server Database Project.

What Does Publish Do?

  • When publishing a Database Project, you define a connection and a database name specifying the "target" of the publish operation.
  • The SQL Server Data Tools (SSDT) looks at the schema of the selected (target) database.
  • If the selected database does not exist, SSDT creates a database using the table, stored procedure, data, and other scripts in the Database Project.
  • If the database does exist, SSDT compares the selected database schema against the information in the DACPAC and builds an update script. It then runs the update script against the selected database.
  • In either case, the selected (target) database schema then matches the schema as defined by the Database Project.

WOW! When you think about this, it is amazing! Especially if you have been doing this manually. No more schema compares in an attempt to figure out the database changes. No more manually developed update scripts. No more issues from running an update script on a database that is a version behind.

With the new Database Project publish feature, each target database is separately compared and a unique update script is generated and executed to ensure *that* target database is updated to the desired schema.

There are a large number of publish settings that you can set to tailor this process. For example:

  • Block incremental deployment if data loss might occur.
    • If this setting is selected, a change defined in the generated update script may not execute successfully if the change could cause data loss.
    • For example, if changing a column from nvarchar(20) to nvarchar(15), data loss could occur.
    • If this setting is set, the column change will not be applied.
  • DROP objects in target by not in project.
    • If this setting is selected, any tables, stored procedures, or other database objects that are in the selected database but not in the project will be deleted.
    • For example, if you change the name of a stored procedure from CustRetrieve to CustomerRetrieve, the selected database will still have the CustRetrieve stored procedure in addition to the new one.
    • If this setting is set, then the CustRetrieve stored procedure will be deleted.

Now that you know what it does, let’s look at how to do it.

How Do I Publish?

To publish a Database Project:

1) Right click on the Database Project in Solution Explorer and select Publish.

2) A dialog appears allowing you to enter a publish profile, which are settings to use during the publish operation.

image

3) Click the Edit button to specify the SQL Server instance you wish to publish to (target).

image

In this case, we are publishing to our development SQL Express database called ACM.

4) The publish script name is defaulted, but you can change it. The publish script file is the file that SQL Server Data Tools (SSDT) create during the publish operation.

image

5) Click Create Profile to save the entered settings as a named profile. That way you won’t have to enter the settings again next time.

Saving a profile adds a publish.xml file into your Database project with the entered settings.

image

6) Click Publish to start the publishing process.

The publish processing displays in the Data Tools Operations window with links to view the script or results.

image

You can create any number of publish profiles. For example, you can create one to publish to your local development database, one to publish to a local test database, and one to publish to the master test database. Just be sure to give each one a clear name.

image

Double-click on a publish profile to open the Publish dialog using the selected profile.

If there is a publish profile that you use often, such as the profile you use to update your development database, there is a way to set a default.

To set a default publish profile:

1) Right-click on the publish profile file in Solution Explorer and select Set as Default Publish Profile.

image

image

2) Then when you select to publish your Database Project, it will default to the setting defined in your default profile:

image

Use the publish feature any time you want to update (or create) a target database using the database schema defined in your Database Project.

Enjoy!

DAC and DACPAC

When you work with a SQL Server Database Project in Visual Studio 2012, two terms you will come across are "Data-tier application" or DAC, and "Data-tier application package" or DACPAC.

Data-tier application (DAC)

A data-tier application defines the database schema and associated database objects that are required to support an application. When you create a SQL Server Database Project, you have the beginning of the DAC for your application.

By the time you are ready to deploy the application, all of the database schema, data, and other scripts that are needed to support the application should be in the Database Project. Then the Database Project is the DAC.

Data-tier application package (DACPAC)

When you compile (build) a Database Project, the Data-tier application (Database Project) is packaged into a DAC package or DACPAC that is stored in the bin\Debug or bin\Release folder.

image

The DACPAC is basically a zip file that contains the database schema, any data or refactoring scripts, metadata, and anything else that SSDT would need to understand the desired end shape of the database.

If you change the .dacpac extension to .zip and open it, it will look something like this:

image

It’s just a set of XML files containing information on your database structure.

So the .dacpac basically contains THE definition of your database.

You can then use features of the SQL Server Data Tools (SSDT) to create a database with this DACPAC or update an existing database to match this definition (say to update a production database with your latest set of database changes).

The general idea behind the DAC is that when you are ready to deploy your application, you have two pieces:

  • The .exe and associated .dlls for the operations of the application. (The result of compiling all of your other projects that are part of your solution.)
  • The .dacpac for the database structure and stored procedures required for the application (The result of compiling the database projects that are part of your solution.)

You can then use these two pieces to deploy your application to test and then to production.

NOTE: It is also possible to generate a DACPAC directly from a database without using a Database Project, but I highly recommend using a Database Project if you are using Visual Studio and SQL Server and you are responsible for updating the database schema and/or stored procedures for your application.

For more information on database projects, see this link.

Enjoy!

July 30, 2013

Creating a Table Script in a Database Project

Visual Studio 2012 introduced a new SQL Server Database Project. This post details how to create a new table script in your Database Project.

See this link for an introduction to the Visual Studio 2012 SQL Server Database Project.

To create a new table in a SQL Server Database Project:

1) In Solution Explorer, drill down into your SQL Server Database Project to dbo then to Tables.

image

2) Right-click on Tables and select Add | Table.

image

3) Enter the desired table name and click Add.

4) A script is added to the project for the new table:

image

5) The Table Designer is opened for entry of the table detail:

image

6) Use the Table Designer pane at the top OR the T-SQL pane on the bottom to define the columns for the table.

The two panes are kept in sync automatically.

image

7) Use the Properties window (View | Properties Window) to set additional properties for the fields on the table, such as the Identity information.

image

8) The defined properties are added to the script in the T-SQL pane:

image

9) When you have finished, click Save to save the script to the project.

Notice that there is no Update button on this designer. The table is not added to a database at this point. To update the database with the details from the new script, you need to publish the Database Project.

See this link for details on publishing a Database Project.

Use the Database Project to add table scripts instead of adding a table directly to your development database.

Enjoy!

« Previous PageNext Page »

© 2022 Deborah's Developer MindScape   Provided by WPMU DEV -The WordPress Experts   Hosted by Microsoft MVPs