Generating SSDT Solutions From Templates

Standard

Consider the following scenario, you’re a database developer and your company has just decided that they want to implement DevOps for databases.You have multiple databases that need to be put source control and each database needs it’s own database project.

The first thing you’ll need to do is decide whether or not you want to use migration based or state based deployments.

This post is not going to discuss the pros and cons for these different methods, instead we’re going to use state based migrations using SQL Server Data Tools (SSDT) solutions.

If you want to know more about state vs migration based deployments, follow this link: https://lmgtfy.com/?q=database+state+vs+migration

Having to create multiple solutions for multiple databases can become a tedious task fast. Besides it’s being repetitive, there is a chance to make mistakes.

That is where templates come in.

Templates?!

Yes, templates. But how are we’re going to create a template for an SSDT solution in such a way that it can be reused?

That’s where the PowerShell module called “PSModuleDevelopment” comes in. PSModuleDevelopment is part of the PSFramework PowerShell module.

The PSModuleDevelopment module enables you to create templates for files but also entire directories. Using placeholders you can replace values in the template making is possible to have the name and other variables set.

This is where the SSDT template comes in. I have created a template for SSDT that containes two projects. One project is meant for the data model and the other project is meant for the unit tests.

I did not yet tell you about that yet, the template enables you to use tSQLt to create your unit tests. In the next blog post I will demonstrate how to generate basic unit tests using the PStSQLTTestGenerator PowerShell module.

The template can be downloaded from this GitHub repository.

Generate the SSDT solution

But to make things easier for you, I created a script that downloads that template from Github, installs it for you and creates the SSDT solution in one go.

Replace the value for the “projectName” variable to t he name of your database and run the script.

After running the script you should see something like this

The result

After the script ran successfully, it will open an explorer window showing the just generated SSDT solution.

As you can see the solution has the name you gave it in the script. This is done throughout the entire solution.

Opening up the solution with Visual Studio we can see the following in the Solution Explorer

As you can see it has two projects:

  1. YOURDATABASENAME-Data; Meant for the data model
  2. YOURDATABASENAME-Tests: Meant for the unit tests

Importing the current data model

The next step will be to import your current database into the solution.

Right-click the “-Data” project, go to “Import” and click on “Database”.

Then click on the “Select Connection”, select the database and click on “Ok”.

For smaller databases with the same schema I set the “Folder Structure” to “Object Type”. If you have many different schemas then selecting “Schema\Object Type” may be better.

Click on “Start” and the result should look something like this:

Now the only thing that rests is to put your database in source control. Preferably you’re going to use Git, because Git……. is awesome.

You are now done creating the initial project. You can now do the same thing for the next database.

I hope this helps you and any comment is appreciated.

Searching For SMO Objects With Certain Properties

Standard

The problem

In some situations I want to search through lots of objects to look for certain properties in SMO (SQL Server Management Objects)

This is also the case in this situation. I wanted to know all the different objects that had a property called “Schema”.

But what to do with all those different properties and methods we could look up. I mean, there are hundreds of objects in there and each of them have many methods and properties.

Getting the objects

Counting all the stuff we got back we have a count of 284. Going through each of the items is not going to work.

The first thing we have to do is filter out all the properties that are actual objects. We want to exclude all the properties that would return values like boolean, string etc.

Let’s change the object selection

That only leaves us with 82 objects which makes things a lot easier.

Now for the last part we’ll iterate through the objects and get the properties and check for the name “Schema”

The result of the objects that have that property

  1. ExtendedStoredProcedures
  2. SecurityPolicies
  3. Sequences
  4. StoredProcedures
  5. Tables
  6. UserDefinedFunctions
  7. UserDefinedTableTypes
  8. Views

Cleaning up the script

I can’t help myself and I always want my scripts to be able to have parameters and have some error handling in them.

The script uses the Connect-DbaInstance command from dbatools.

The end result:

Just run the command like this

Making it public

For anyone who wants to do something similar, here is the code

https://github.com/sanderstad/SMOProperties

 

Help needed for new PII command

together we create
Standard

Update

The location of the JSON files has been changed to development branch

I’ll cut right to it, I need your help.

I’m developing a new command for dbatools to scan for PII.

I already have a wide variety of different patterns and ways to check on possible personal information but I want to be as thorough and complete as possible.

The command is called Invoke-DbaDbPiiScan and it does two things:

  1. it scans the columns in the tables and sees if it is named in such a way that it could contain personal information
  2. it retrieves a given amount of rows and goes through the rows to do pattern recognition

pii scan result

How does it work

The command uses two files:

  1. pii-knownnames.json; Used for the column name recognition
  2. pii-patterns.json; Used for the pattern recognition

You can find the files here in the GitHub repository.

The patterns and known names are setup using regex to make the scan really fast.
Also, using regex this way with the JSON files makes the solution modular and easy to extend.

pii-knownnames.json

An example of a known name regex is this:

What this does is, it tries to match anything with “name”.

pii-patterns.json

The pattern regexes tend to be more complex than the know names. This is because we have to deal with more complex data.

An example of a pattern:

This particular pattern is used to find any MasterCard credit card numbers.

How can you help

What I need from you is to see if you can come up with more patterns that could lead to a more exact result.

I opened an issue in the Github repository where you can leave a comment with the pattern.

If this pattern is only used in a certain country, make sure you include which country this applies to.

I want to thank beforehand for any input.

If you have any questions leave a comment here, contact me through SQL Community Slack Channel or Twitter both as @SQLStad.

 

CI/CD for databases: Setting Up The Project

Standard

This series has been a long time coming. I have been struggling with continuous integration and continuous development for a while and I want to share my process, techniques and tips and tricks with you.

I will publish several blog posts about this process because we cannot go through all of it at once. It would lead to a TL;DR article that nobody will read.

  • The first part will be setting up the project/solution for your database.
  • The second part will be about creating unit tests for your database objects.
  • The third part will be to put the project through a build server and eventually a deployment server

Why this series

I never had to do a lot of work with source control, visual studio and all the related parts because I would get a script, deploy it in test. If it worked fine, if not I would have a backup and restore that.

That was my previous process and works on some level. But when you have to deal with rapid development, things start to break.

In my current job there is another problem. I’m both the DBA and, for most database projects, also the developer. That means I wear two hats when developing and deploying my solutions.

That’s like I’m marking my own paper and it’s always approved. I don’t want that responsibility and reliability so things have to change.

The last reason I think this is really important is that I like to automate everything. Automation prevents human errors and makes out lives easier.

In the end I only want to work on a project, push the changes to source control, let some service build the objects and push it along without me having to interfere.

Setting up the project

This part has taken a considerable amount of time for me to switch over to. For me to switch over from the SQL Server Management Studio (SSMS) to having to deal with Visual Studio (VS) was like night and day.

The thing is that when you’ve never worked with VS a lot you kind of have to find your way around it. The SSDT projects are different from say a C# project and it all feels a bit big.

I assure you, if you’re going to switch over, that feeling is only going to be there in the beginning. I’m going to help you setup your project in mere minutes instead of the hours I had to put into it.

The first thing I wanted to do is setup my project. I wanted to separate the model from the tests in separate projects but within the same solution. The following post helped me a lot and was glad with the framework. I did change a couple of things to make things run smooth in the end.

Setting up this framework every time is going to be a paint in the but and I was not going to spend all that time setting up database projects.

Fortunately my good friend Friedrich/Fred Weinmann (t | g) has created a few commands that make it possible to create templates. not just of files, but also entire folders.

This was the solution to my problem. I setup the solution in such a way that it would automatically generate the right files with the right names. Fred helped make the final changes under the hood to dot all the i’s.

Generating the solution

There are a couple of things you need to have installed before you can generate the SSDT solution.

  1. Install the PowerShell module PSModuleDevelopment
  2. Download the SSDT solution
  3. Generate the template locally

Install PSModuleDevelopment

Open a PowerShell window and enter the following command

Download the SSDT solution

I have created the SSDT solution for you so you don’t have to. Go to the repository and download the files.

If you have Git installed, go to your preferred location to download the content and execute the following command

You can also download the zip file manually from the Github repository. When you download the zip file, unpack it in your preferred location.

Generate the template

For you to be able to generate the solution you have to create the template on your computer first. This only needs to be done once and you can reuse it every time.

Execute the following command to create the template

For example

After that you no longer need the files and you can remove them if you like.

The last step is the most exciting one, generating the solution.

Execute the following command to generate the solution based on your just created template

For example

Navigating to the solution it will look something like this:

SSDT solution generation result

Opening the solution this is what you’ll have

SSDT solution explorer

Now you’ll be able to generate your SSDT projects, including all the content needed for unit testing in mere minutes.