Sorted By: Tag (sql-server)

Managing SQL Server Express LocalDB Instances Through SqlLocalDB.exe Utility Tool

I love the feeling when I discover a tiny, hidden tool and SqlLocalDB.exe, a management utility tool for Microsoft SQL Server LocalDB which allows you to manage the LocalDB instances on your machine, is one of them. Let me show you what it is.
2016-02-18 12:46
Tugberk Ugurlu


I love the feeling when I discover a tiny, hidden tool which I can put into my daily software toolbox. I started to sense this feeling more and more lately with some amazing command line tools and I want to write about those here (famous last words) like I did for http-server and WAStorageEmulator.exe a while back. Today, I want to start this by writing about SqlLocalDB.exe, a management utility tool for Microsoft SQL Server Express LocalDB which allows you to manage the LocalDB instances on your machine such as creating, starting and stopping them. This is really handy if you are after creating lightweight SQL Server instances for temporary processing like we do as part of some DLM Automation Suite PowerShell cmdlets (e.g. Invoke-DlmDatabaseSchemaValidation).

image

I am not entirely sure hot you would install this tool and I failed to find out exactly how. However, it seems like this comes with the LocalDB installation and you can acquire that through here (also check out Scott Hanselman's ironic "Download SQL Server Express" blog post).

This may depend on the version you installed but I can locate the SqlLocalDB.exe under "C:\Program Files\Microsoft SQL Server\120\Tools\Binn" on my machine and after that, it's just executing the commands. For example, I can execute the info command to see the LocalDB instances I have in my machine:

C:\Program Files\Microsoft SQL Server\120\Tools\Binn>SqlLocalDB.exe info

MSSQLLocalDB
RedGateTemp

I can also list which versions of LocalDB I have installed on my machine:

C:\Program Files\Microsoft SQL Server\120\Tools\Binn>SqlLocalDB.exe versions

Microsoft SQL Server 2014 (12.0.2000.8)

Let's create our own instance through the create command:

C:\Program Files\Microsoft SQL Server\120\Tools\Binn>SqlLocalDB.exe create tugberk

LocalDB instance "tugberk" created with version 12.0.2000.8.

You can view the status of an instance through the info command by passing the instance name as an argument:

C:\Program Files\Microsoft SQL Server\120\Tools\Binn>SqlLocalDB.exe info tugberk

Name:               tugberk
Version:            12.0.2000.8
Shared name:
Owner:              TUGBERKPC\Tugberk
Auto-create:        No
State:              Stopped
Last start time:    2/18/2016 12:06:00 PM
Instance pipe name:

Let's start the instance through the start command:

C:\Program Files\Microsoft SQL Server\120\Tools\Binn>SqlLocalDB.exe start tugberk

LocalDB instance "tugberk" started.

We can see that it has started:

C:\Program Files\Microsoft SQL Server\120\Tools\Binn>SqlLocalDB.exe info tugberk

Name:               tugberk
Version:            12.0.2000.8
Shared name:
Owner:              TUGBERKPC\Tugberk
Auto-create:        No
State:              Running
Last start time:    2/18/2016 12:09:28 PM
Instance pipe name: np:\\.\pipe\LOCALDB#7F6D2993\tsql\query

Great, now I can connect to this instance using the provided magical, special instance pipe name:

image

Or, LocalDB server name by prefixing the instance name with "(localdb)\":

image

If you dig a little deeper, you will see that an instance of "sqlservr.exe" has been started for your LocalDB instance:

image

You can also see that all LocalDB data is stored under "%LOCALAPPDATA%\Microsoft\Microsoft SQL Server Local DB\Instances".

From there, you can treat this instance as a usual SQL Server instance and perform whatever operation you want to perform on it. Be aware that LocalDB has the same limitations as SQL Server Express.

image

Finally, you can stop the instance through the stop command:

C:\Program Files\Microsoft SQL Server\120\Tools\Binn>SqlLocalDB.exe stop tugberk

LocalDB instance "tugberk" stopped.

Further Information

Slides of Introduction to Database Lifecycle Management Talk at /dev/summer 2015

Yesterday, I was at /dev/summer 2015, Cambridge and gave a talk on DLM (Database Lifecycle Management). I have uploaded the slides under my Speakerdeck account.
2015-06-28 11:57
Tugberk Ugurlu


Yesterday, I was at /dev/summer 2015, Cambridge. It was a really good event and I had a chance to attend a few talks on Go Language, Web Profiling and Open Source.

2015-06-27 16.36.26-1

2015-06-27 12.46.58

2015-06-27 11.40.33

I also gave a talk on DLM (Database Lifecycle Management), which is what we have been working at Redgate for a while to make it easy to adopt.

2015-06-27 16.31.18

I have uploaded the slides under my Speaker Deck account.

Also, here are the links I have at the end of the slides and a few more:

How to Use Octopus Deploy Step Templates for SQL Release

In this post, I will go through how you can use SQL Release Octopus Deploy step templates to make the Octopus Deploy Integration of SQL Release easier by going through one of the deployment flows.
2015-04-06 21:39
Tugberk Ugurlu


SQL Release, a set of PowerShell cmdlets from Redgate which automate deploying changes to your production databases, went out of beta and became part of DLM Automation Suite a few days ago. As part of this release, Octopus Deploy step templates for SQL Release are also included inside the suite and in this post, I will go through how you can use these step templates to make the Octopus Deploy Integration of SQL Release easier by going through one of the deployment flows (the recommended one).

If you are trying to integrate your SQL Server databases into your deployment pipeline, I strongly encourage you to try DLM Automation Suite out. At the time of this writing, it has 28-day free trial option. You can also check out the documentation page for DLM Automation Suite documentation and information about included products.

image

Installing SQL Release Step Templates

If you are not familiar with Step Templates, it’s a plugin mechanism that Octopus Deploy has which allows you to get the input from the user through a nice UI and run specific PowerShell script based on the input passed in. A step template is nothing but a structured JSON text and they are hosted inside the Octopus Deploy Step Templates library. They actually don’t need to be hosted there in order for you to use them but it makes it very convenient to find one in a central place.

SQL Release has four step templates to satisfy different flows and use cases.

image

First thing to do in my demo blog post here is to install these step templates into your Octopus server. The way to install a step template is a little different that you might expect.

  1. Go inside a step template page on Octopus Deploy Library web site.
  2. Hit "Copy to clipboard" button on the right hand side.
  3. Go to your Octopus server and navigate to Library (on the top menu)
  4. You should see the "Step templates" pane on the left side. This will open up the step templates page for your Octopus server.
  5. On that page, hit "Import", paste the step template inside the text area and click "Import".

You will end up with a look similar to the following one:

image

After I imported all the step templates for SQL Release, it is time to actually create the deployment process. We will be using only two of these in our example here.

In order to use the SQL Release step templates, you need to have SQL Release installed inside the Octopus Tentacle machine. If you install SQL Release while the Tentacle is running, you need to restart the Tentacle service (through the Tentacle Manager, for example).

Setting up the Octopus Project

Before going through each step of the deployment process, I wanted first show the end look of the Octopus project.

image

At the end, we will have four steps to complete our deployment for this example. A few more things to point out:

  • We will be only deploying the database for the purpose of this demo but you can imagine having your application deployment here as well.
  • We will deploy the database schema changes in two steps in order to allow review and approval of the script.

Download and Extract NuGet Package Step

First step will be to download and extract the NuGet package which contains the scripts folder for the database schema state. This NuGet package will be produced by another DLM Automation Suite tool named SQL CI, a plugin for your CI tool that allows continuous integration for SQL Server databases. The script folder I mentioned here can be produced by a few Redgate tools such as SQL Source Control. The script folder I am using for this sample is hosted on my GitHub repository.

In order to create a package, I fired the following SQL CI command:

sqlci Build --scriptsFolder="D:\github\Geveze\db" --outputFolder="D:\github\Geveze" --packageId="Geveze" --packageVersion="1.0.0"

image

When I have the package created, I pushed it to Octopus Deploy NuGet feed using NuGet Command Line tool:

nuget push Geveze.1.0.0.nupkg -ApiKey API-CMGMYZ1GM95FHJNLWVRQQGQRAPK -Source http://localhost:4000/nuget/packages

Typically, these steps would be performed inside your CI Server but I didn’t want to have CI integration in order not to complicate things more for this demo. Also, check out the SQL CI Documentation for more information about the command line options and other related stuff. We won’t go into details about this tool in this post.

Once I pushed the package to Octopus Deploy NuGet feed, I was able to see the package while I was configuring the step:

image

Create Database Release Step

This is probably the most important step in our process. Here, SQL Release will create the actual changes script and bunch of other artifacts that can be used later. These will be generated based on the package it will obtain from the previous package and the target database which will be used for compression.

In order to add a "Create Database Release Step", you need to hit "Add step" on the Project > Process page. From the "Choose step type" window, choose "Redgate - Create Database Release" option.

image

The step configuration will look something like below:

image

As you can see, I am using Octopus Deploy variables here. The ones that I have are as shown below:

image

Also note that I configured this step to be only run for the staging environment which will basically allow you to reuse the generated changes script to be deployed to production environment. This also means that reviewed script will be used in all deployments. As a final note: SQL Release will fail if the state of the target database is drifted from the compression state which makes the whole process safer.

The next step is "Review Database Changes" which is a standard Octopus Deploy manual intervention and approval step. I will skip that step here as the documentation is pretty straight forward on this.

Deploy from Database Release Step

The last step is for actually deploying the changes. Once the sign-off is given, the changes to the database can be deployed. In order to start configuring this step, choose the "Redgate - Deploy from Database Release" step type from the "Choose step type" window. The configuration will look something like below:

image

One big difference here is that this step will be executed on both Staging and Production environments. In fact, this step is the only step that will run against the Production environment in our example here.

Create a Release and Deploy

We are now ready to create a release (alternatively, you can take advantage of "Automatic Release Creation" feature of Octopus Deploy) and deploy to staging. When you start the deployment, it will pause on the manual intervention step as expected and we will see a few artifacts created on right hand side.

image

You can see the update script, warnings and an HTML report for the changes. If you click on the Changes.html file, it will be download and you can open it up with your choice of web browser. It will give you a nice diff report.

image

Once you approve, the deployment will go on and the last step will run to actually deploy the changes. When you are happy with the staging environment, you can deploy the changes to Production by clicking the Promote button on Octopus server.

image

As you can see, only step will run here is the last one. Remember that if the either of the database are drifted between the time of release creating and deployment, SQL Release will fail to deploy to make the process safer.

Obviously, SQL Release and its Octopus Deploy step templates make it easier to integrate with Octopus Deploy for deploying database schema changes in a safe and reliable way. If you feel that you are already struggling with making your database as part of your continuous delivery story, definitely try DLM Automation Suite and SQL Release out. You can also give feedback to SQL Release on its Uservoice page.

Tags