Voice of the DBA

Tag: Redgate

Installing FlywayDB

I’ve been working on a demo for a customer. Part of the demo uses a new Redgate product, but Flyway is a part of that. In testing a couple things, I realized that I didn’t have FlywayDB installed on this new machine, so I did a quick walkthrough.

Installation

This is actually simple, or fairly simple. First, download the .zip file from Flyway.

Next, put this somewhere. For me, I wanted to be organized, so I put this in C:\Program Files\Red Gate. This did require some UAC approval to unzip the download into this spot.

Once I did this, I saw the flyway.cmd file in the subfolder. The instructions note I need to add this to my path.

There are different ways to add things to the path, but the quickest for me on Windows 10, is to get to the properties of “This PC”. There is an advanced system settings here.

This let’s me see properties, including the “Environment Variables” at the bottom.

Clicking this shows me the various system variables, including the PATH.

If I edit this, I get a standard dialog where I can add the Flyway folder.

Once done, I can test this with “flyway info” at a command prompt.

One note, if I have a command prompt open, I need to restart it to get the new path.

Licensing Flyway

This is an interesting item. I didn’t directly find an answer in the quick start. Most people probably use the Community edition, so they don’t need a key. I, however, wanted to play with the Enterprise version. I got a key from Redgate, and then set the environment variable. As you can see below, this isn’t enough. I got an error that I didn’t have a license.

I wasn’t sure if flyway.licenseKey is a file or a setting. I looked around a bit, and on the download and install page, I found an item doe the Configuration. This mentions that the first place Flyway looks is the install folder/conf/flyway.conf. Aha!

I looked in the conf folder under my Flyway install. Under here is the flyway.conf file, which is a key-value configuration file. It reminds me of the old Windows 3.1 .ini files.

When I open this, I see a lot of values. These are mostly commented out with a #. If you need to enable a value, remove the comment at the start of the line.

Scrolling to the bottom shows me that the last entry is for the license key. I removed the comment character (# ) and then pasted in my key.

The next time I ran flyway info I see this:

No license message, though obviously I haven’t set up the connections yet.

That gets me started, and I know things are installed. Now I need to start using it, which is something for another post.

August 17, 2020
SQL Clone Works with FILESTREAM
SQL Clone is an amazing product that virtualizes your data, allowing multiple instances to share a read only image, but still produce writeable databases that look normal to SQL Server. It’s similar to how a container appears to a user, but this uses real SQL Server instances.

I need to write up a more detailed walkthrough of this, but someone asked the question today about SQL Clone and FILESTREAM and I didn’t see a proper article on the Redgate site, so I decided to run a test and post this.

Setting up FILESTREAM

We have some articles at SQLServerCentral on FILESTREAM, but essentially this feature uses a folder on your instance file system to store blog files, instead of putting them in the database. To enable this, you need to do it in Configuration Manager

and in SSMS

You do need to restart the database engine, but then you can create a database that includes a FILESTREAM filegroup.
```
CREATE DATABASE [FSTest]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'FSTest', FILENAME = N'D:\SQLServerData\SQL2017\FSTest.mdf' , SIZE = 8192KB , FILEGROWTH = 65536KB ), 
 FILEGROUP [FSFG] CONTAINS FILESTREAM 
( NAME = N'FSData', FILENAME = N'D:\SQLServerData\SQL2017\FSData' )
 LOG ON 
( NAME = N'FSTest_log', FILENAME = N'D:\SQLServerData\SQL2017\FSTest_log.ldf' , SIZE = 8192KB , FILEGROWTH = 65536KB )
GO
```
Once this is done, create a table and load some data.
```
CREATE TABLE Books
(   BookKey     INT              IDENTITY(1, 1)
  , BookTitle   VARCHAR(100)
  , FSGIUD      UNIQUEIDENTIFIER ROWGUIDCOL UNIQUE NOT NULL
        DEFAULT NEWID()
  , BookContent VARBINARY(MAX)   FILESTREAM);
GO
DECLARE @Document AS VARBINARY(MAX)
 
-- Load the image data
SELECT @Document = CAST(bulkcolumn AS VARBINARY(MAX))
      FROM OPENROWSET(
            BULK
            'E:\Documents\Using Local and Hosted Agents for Build with Azure DevOps.docx',
            SINGLE_BLOB ) AS Doc
 INSERT dbo.Books
     (BookTitle, FSGIUD, BookContent)
 VALUES
     ('Using Local and Hosted Agents for Build with Azure DevOps'   -- varchar(100)
    , NEWID() -- uniqueidentifier
    , @Document
     )         
 ;
GO
```
This gives you a database table with some data in SQL Server, in the Books table, and some in this folder, FSData, on your file system.

DO NOT mess with this folder, but the contents here will be included in any backup.

Now, I showed how to make an image and clone in a previous post, which I’ll do from this database.

When I get done, I’ll deploy this to another instance. In this case, I was worried about a folder issue on the same instance, but I’ll test that another day.

Whoops, I need FILESTREAM on the second instance.

I’ll follow the same config steps as above and restart this instance. this time things work.

If I script the table, I see it is a FILESTREAM enabled table.

That’s it for now, but I’ll get a proper article written for the Redgate Hub.
August 12, 2020
Creating a SQL Clone Agent and a First Image

In a previous post, I set up the SQL Clone server. This is really a metadata store and web front end, but it does no real work. The Agent service, which is installed on each SQL Server instance that will work with clones, does the work.

This post looks at installing the agent and then creating the first image and clone of a database.

Downloading the Agent

To get a SQL Clone agent, you need to have access to the SQL Clone Management server. This is a web application, with a URL that defaults to the name of the machine where you installed the server and port 14145. You do not have to be logged into this machine, but rather, logged into a machine with SQL Server that you need an agent on.

For me, I installed this on Aristotle, so I go to:

http://aristotle:14145/dashboard/

The Getting Started screen appears, which we saw in the last post. I want to click the “Download agent”.

This downloads a file, but before I run this, I need a service. On the Agent installation doc page, you see some architecture. While I am using the same machine, for many PoCs, I’d likely have installed the server on a central machine, such as the SRV-SQLCLONE machine in the image below.

In many POCs, my laptop, or some shared development SQL Server instance, is represented by SRV-HC1-SQL1 above. This is where I connect to as a developer and deploy clones. Or maybe I need an agent on my local workstation, WKS-DEV-01.

In any case, I need a service to run the agent. While I could use my local account, I don’t recommend this. I really want a separate account. Get in this habit. Learn to use Group Managed Service Accounts or deal with separate domain service accounts.

For me, I use SQLCloneAgent on each machine that runs as an agent, so I’ll create that account.

This does need some special Windows permissions. The install will allow it to be a service, but this does need to be an admin to use the Virtual Disk Service. Let’s ensure this is working.

This account also needs read/write access to the share, so I’ll alter the share with permissions to allow read and write.

This does need SQL Server permissions. This agent will create and drop databases regularly on the local SQL Server instance, and so it needs to be able to do that and manage those items. While you may be able to get away with CREATE DATABASE permissions, the documentation notes this account needs sysadmin privileges.

It might seem that create/alter any database is sufficient, but that’s not the case. The agent checks this access, so grant this.

With the account created and the permissions on the share, let’s install the agent.

Installation

This is a standard Windows install process. There is a procedure for a silent install, but I’ll do this interactively. Double clicking the downloaded EXE runs the install.

When this completes, I get the config process.

Clicking Continue asks for my service account.

In a moment, if I’ve typed the credentials correctly, I see this.

In Services, I see this running. Note, the Clone Management services happens to be here, but this isn’t required. This could be on another machine.

In the settings of the SQL Clone application, I can see my agent listed. In fact, as you add new agents, their version and status is listed.

This is working, so let’s proceed.

Creating a Cloned Database

Once I have an agent, the Getting Started flow has a new option: Create an image.

I click this and I need to pick a source. I’ll pick an existing database on this instance, so I select “SQL Server”.

This gives me two dialogs. I enter a SQL Server name, and SQL Clone will check that my agent has sysadmin access. Once it does this, I can select a database on this instance.

I click Continue to set up modifications. The main purpose of SQL Clone is to allow you to use full size production databases in development, by saving space and masking out PII data. This is a test, and I’m picking a dev database to make an image, so I’ll leave this alone and move on.

I need to select a location for my image. In this case, I’ll enter the share I created in the first post.

Now I need to enter the name for this image and check that I have what I wanted to be entered.

A word on naming. I know some people will name these with dates, which I used to do. However, for the most part you will have 2-3 images for any database in rotation.

When I click create, this starts running. I can see some progress on the dashboard. In this case, this is a 10MB database, so it runs quickly. When the creation is done, the dashboard updates with the activity item in the upper right, and I can see the image at the bottom.

I can see my image in the share. This is a VHD in a folder, and I don’t want to mess with this. I can see the size, however, which is my data size.

This is an image, but I don’t have a database, so let’s quickly deploy a clone. On the left menu of SQL Clone, let’s click Create Clone. This gives me a place to select an image (of which I only have one now).

I can modify this during deployment, but let’s skip that.

Next I need to pick an instance to deploy this clone on. I’ll pick my local instance.

Once I do that, I give this a name. This is the database name I see in SSMS.

I create this, and I see it in SQL Clone:

and SSMS:

Summary

That’s a quick look at getting an agent installed and testing it’s working with an image and clone. I can now work with the SimpleTalk_Test database as I would any other. Once I’ve tried something, I can get rid of it and recreate it easily, or create copies if I want to test multiple things.

SQL Clone is a valuable tool that changes the way you work with databases, allowing developers and automated systems to work from a known base for their code changes. With frequent recreations of updated images after deployments, you can always be sure that your developers have a consistent foundation.

Try SQL Clone today if you are looking for a way to consistently and easily deploy databases for your developers.

August 6, 2020
Unboxing SQL Clone 2020 for a PoC
When I got a new machine, I had SQL Clone on my old machine, in a few VMWare VMs. I decided to move this to my new machine, so that I can more easily demo things. I also want this always running so that I can use in constantly in development. That’s part of my job, but it’s also a good way to approach this as a Proof of Concept.

I’m going to install this on my desktop, but the process is the same for a distributed installation on a server and a desktop. I’ll separate out the server and then the desktop into two posts, with a quick look at how this works. I’ll also detail the steps I need that differ slightly from what’s in the documentation.

Architecture

There are really three components to SQL Clone:
- The SQL Clone management server (web server and data store in SQL Server)
- A file share for storing database images
- An agent on each SQL Server instance that gets database deployed to it
SQL Clone has a server component, which is really just a metadata store and a web interface. This manages how the clones are deployed and tracked, but really, these are just metadata stores. Not much horsepower is needed for the server, either the web server or the database used as the data store. I’ll detail this setup below.

There also is a file share that is used for keeping the read-only images. This does need to be high quality storage that each SQL Server instance can connect to with minimal latency. I’ll set up a share on this desktop, so that I can use this from other machines.

The SQL Clone Agent goes on the SQL Servers where you deploy cloned database. This will also be on my local workstation, but I can add agents on other machines as well if they run SQL Server. I’ll look at this in another post.

Installing the Server

The documentation for getting started lists requirements on this page. There are a few items that this lists for the server:
For this setup, I will start by downloading the SQL Provision eval. Once I have this, I also want to ensure I have a SQL Clone license key (or am doing an eval).

Once this is done, I’m going to start with the service account. I use SQLCloneMgmt for my service account name. I’ll create this on the local system. You can use a domain account as well, and the setup will assign the rights needed for running a service.

Save the password as you’ll need it later.

Next, I decided to create the database in advance. The documentation notes that the default is SQLClone_Config, so I used that name. I also set this to the simple recovery because I don’t expect to worry about PIT recovery.
```
CREATE DATABASE [SQLCLone_Config]

GO

ALTER DATABASE [SQLCLone_Config] SET RECOVERY SIMPLE 

GO
```
I do need to add my service account to SQL Server. This is really just an account that connects to the database, creates some objects, and then reads and writes some data.

I also want to go to the User Mapping page and give this db_owner in the SQLClone_Config db.

That’s about it for the pre-setup. Now, let’s install the server.

I’m choosing SQL Provision, which is Data Masker and SQL Clone. Data Masker is used to process data during image creation, so I’ll install both. Once this is done, the Eula is next and then the install location. I’ll accept the terms and defaults.

There is a check for running applications and then a normal Windows application install runs. When this completes, I get to the configuration screen and then a login for Redgate on top of it.

I’ll log in and my license isn’t found. No worries, I can activate this later. I’ll need to ping someone for a new one. Once I get that, I clicked the Activate link and entered it. I get a confirmation. Note: you can move forward as an eval for the installation.

The configure Clone screen appears when you get done with licensing. Click Next to move on.

The next screen is where I put in the service account information and the SQL Server details. This is the SQLCloneMgmt account I created earlier, and for my SQL Server, it’s on Aristotle.

I click “Finish” and after a moment my default web browser opens. I see this:

This is the welcome screen for the Dashboard. Since this is a new install, there is not information here. Click “Get Started” and you move to the flow that is used for SQL Clone.

The server is installed, but I can’t do anything without an agent. Agents do all the work on the SQL Server instance machines. I’ll do that part in another post. If I look at my SQL Server, I see this:

SQL Clone is installed and ready for work. The next thing for me is to set up a file share for the server.

File Shares

I can work with a local path, but I’ll constantly get warnings for images, since only SQL Server instances that can see the local path can work with the images. It works fine for a proof of concept, but it’s annoying. As a result, I’m going to make a share that’s essentialyl a loopback, pointing back to this machine.

I have a few drives on my machine. The E: drive is an SSD, and I want to use that.

Whether this is the local machine or a server, I’d create a folder on this drive. I want to create “SQLCloneImages” so I know what this is, and then look at the properties to share this.

I’ll click “Share” and add my account. On a server, I’d likely pick an existing AD group to read this share, or create a new one. When I complete the process, I see a confirmation.

Sharing is a weird concept in Windows, as we’ve tried to be better about security. You’ll likely do something different, depending on your Windows OS.

That’s it. I can test this in Explorer, and I see the share.

Everything is ready for an Agent, which I’ll tackle in another post.
August 4, 2020