Voice of the DBA

Tag: Redgate

Getting Team City working with BitBucket

As a part of building a CI/CD home lab, I set up TeamCity in the past. I plan on using this for multiple projects, and in fact, I’d built a basic Hello, World C# application as my first build.

Now it is time to work on something more complex. As a part of my SQL Server Builds project, I decided to host the Ready Roll build here. That means connecting TeamCity to BitBucket.

I had no idea how this might work, and some searching showed that this needed a plugin prior to v10, but the capability was built-in after that. I’ve been meaning to upgrade, so I did that first. As you can see, I have my TC v10 system, and when I go to create a new project, BitBucket is a first class citizen.

Once I picked this, the system has me authenticate with Bitbucket (I won’t show this) and then get a key and secret that I paste into the dialog. This is fairly easy to follow, but for security purposes I’ll skip that. Once I’m authenticated, I can see my repos:

I pick this repo and a project is created. In fact, TeamCity detects that I’ve got a VS solution in the repo, so it sets that as my build config.

That’s pretty cool, and I’m hooked up. Or am I? Let’s see. I’ll manually run a build.

It doesn’t work. However, if you read the error, you’ll realize that the ReadyRoll project type doesn’t seem to work. That’s because I’m building on this server, which is separate from my development machine. I need to install the ReadyRoll binaries there.

A quick download from Redgate, and I install ReadyRoll. Now when I click “run”, I get this:

Success.

There’s more to getting setup, but this is a quick look at getting TeamCity hooked up to BitBucket.

If you want to try ReadyRoll, grab an eval.

November 22, 2016
DLM Automation–Making a Database Connection
One of the things I’ve been wanting to do is dig more into the command line automation cmdlets from the Redgate DLM Automation suite. While there are plugins for many build and release systems, I should be able to customize, or fall back, to the command line if necessary.

Plus, this allows me to get a better feel for what’s happening at each step.

This post looks at the basics of making a connection to a database. Note that this is documented on the DLM Automation 2 Cmdlet Reference page.

New-Database Connection

When I started working with the connections, I used the New-DatabaseConnection cmdlet. However, when I run this now, I get this:

That’s fine, as my call to the new cmdlet works.

Do I know what’s happening here? I should investigate further.

First, if I’ve run this, what does it do? Not much, apparently. If I run an Extended Events session looking for a connection, I don’t get one. Let’s try a new command, this time using a name and password.

That’s an invalid password for my instance (as it should be on every instance in the world). However, I don’t get an error. No connection was made. In this case, the cmdlet is storing off the connection credentials that will be used. If I don’t provide the user/password, then a trusted (Windows auth), connection is assumed.

Can I now test this? Sure, I’ll use the Test-DLMDatabaseConnection to make a connection.

This fails. That’s good to know. Now I can use this to actually determine if I target database is alive before I continue processing. My preferred method here would be to wrap this up in some error handling. For example, I can use a try..catch structure in PoSh. I’ll switch to the ISE to make this cleaner. Here’s the code
```
try
{
Test-DlmDatabaseConnection -$test -ErrorAction Stop
} 
catch
{
Write-Host "error"
}
```
In the ISE:

I can also get the error details from the current object ($_). Note, I’m not a PoSh expert, so if there are better ways, let me know.

I can also pass in some common parameters, like –ErrorAction and –ErrorVariable. Then I can use these to decide whether or not to continue. In my case, I’ll just write a message. Here’s my code:
```
$testconn = New-DlmDatabaseConnection -ServerInstance ".\SQL2014" -Database "st_integration" -U "sa" -Password "test"
try
{
Test-DlmDatabaseConnection $testconn -ErrorAction SilentlyContinue -ErrorVariable ErrorHolder;
} 
catch
{
Write-Host $_.Exception.Message
};

if ($ErrorHolder)
{
Write-Host($ErrorHolder)
}
else
{
Write-Host("Everything works");
}
```
That will give me this:

If I remove the user and password parameters, I get this:

You didn’t think I’d publish the real sa password, did you? Of course not. If I change the code to the right password, things connect.

Checking the Object

What if I just want to check the object? Well, there are properties associated with the object. For example, if I use a simple variable to assign to the object, I can see some properties. If I just type the variable name, $testconn, I get these properties:
- ServerInstance
- Database
- SQLServerCredential
- ConnectionString
- Description
You can see these returned in a test session.

Other Options

I have other parameters I can use with my connection, or rather, in place of my connection object. I can use a connection string, with all of the parameters that are valid there. For example:

If I enter the correct password, I get this:

My connection string can contain all of the items I might normally use: ApplicationIntent, Encrypt, NetworkLibrary, etc. A full list of parameters is at: https://www.connectionstrings.com/all-sql-server-connection-string-keywords/

The New-DlmDatabaseConnection object outputs a RedGate.DLMAutomation.Compare.SchemaSources.DatabaseConnection object, which is the input to quite a few other of the cmdlets. While not complex, it is worth understanding how to use this cmdlet, which is the basis for determining from which source or two which target you will be moving database changes.

In another post, I’ll start to bind this cmdlet with others and actually perform some database deployment automation work.
November 16, 2016
The GUI Helps Build SQL Prompt Snippets

I work for Redgate and write about products. I’ve got a series of SQL Prompt posts here on little things I like. SQL Prompt might be my favorite tool. SQL Prompt will be yours as well if you give it a try.

SQL Prompt is an amazing tool for writing T-SQL. It helps you to concentrate on your code, without having to go off and examine Books Online or other documentation, or even worry about formatting.

One of the keys to making Prompt efficient for you, in your environment, is to use Snippets. These are short codes that insert a lot of T-SQL, like the SSF snippet. As I’ve worked to make myself more efficient, I’ve also tried to be efficient in my creation of snippets. One way to do that is using the SSMS GUIs.

For example, suppose I want to build a snippet for databases. I can open the Create Database dialog. This has the places where I can specify the name, owner, size, growth, options, etc. You can see the main page below.

Once, I’m happy, I click the “Script” button at the top, as shown here.

This gives me the code in a window, where I can then customize for my template parameters and then paste in the snippet.

The same thing for logins and users. I create and drop a lot of security princpals, so I can right click on logins, as I have here in the Object Explorer.

I’ll get a New Login dialog, and I can add in the items that matter, like defaults, password, types, etc.

When I now click the Script button, I get my code. Note, I’ll cancel out of the dialog, and work with the code shown here.

I can cut and paste this into the Snippet edit box. In the image below, I’ve deleted the old code for cl (“Create SQL Server login”) and put my code in. I change the specifics (username and password) to template parameters by putting a name in between two dollar signs ($). This will give me a quick way to customize the snippet in each case.

Note that I need to replace each instance of “JohnDoe” with “$username$” for this to work well. I did that before I saved the snippet, as well as added a template for the database.

Now when I type “cl”, I get the snippet.

and hitting Tab gives me the code.

That’s a quick and easy to add Snippets for those items you perform often with the GUI in SSMS. You will code quicker and more consistently, and you’ll create new objects without thinking.

Hopefully, you’ll see the value in SQL Prompt and start using snippets to improve your ability to code quickly and take the hassles and guesswork out of cleanly building SQL Code. You can also read a similar piece I wrote on the Redgate blog.

Try a SQL Prompt evaluation today and then ask your boss to get you this productivity enhancing tool, or if you’re using the tool, practice using ii the next time you need to insert some data.

You can see a complete list of SQL Prompt tips at Redgate.

November 8, 2016
Creating Custom Databases with SQL Prompt
I work for Redgate and write about products. I’ve got a series of SQL Prompt posts here on little things I like. SQL Prompt might be my favorite tool. SQL Prompt will be yours as well if you give it a try.

SQL Prompt has lots of great features that can help you write SQL quicker. However, you’ve got to train yourself to use a few of these and not just start to type with your old habits. This quick tip looks at one of those areas where a little customization can make things work smoothly.

Perhaps you are like me and you’re often creating databases for testing things. It’s quick and easy, and certainly a good template goes a long way to ensuring that you don’t have any issues when you build a database.

There’s a “cdb” snippet in SQL Prompt that I have come to really like. Of course, the default code leaves something to be desired, so I usually change it. I wrote a bit about this on the Redgate blog, but there are a few other things I’d add to my changes for a developer.

Here’s the default code:
```
CREATE DATABASE database_name
ON
PRIMARY ( -- or use FILEGROUP filegroup_name
  NAME = database_name_data,
  FILENAME = 'database_name.mdf'
) --, and repeat as required
LOG ON
(
  NAME = database_name_log,
  FILENAME = 'database_name.ldf'
) --, and repeat as required
--COLLATE collation_name
--WITH
--  DB_CHAINING ON/OFF
--  TRUSTWORTHY ON/OFF
--FOR LOAD
--FOR ATTACH
--WITH
--  ENABLE_BROKER
--  NEW_BROKER
--  ERROR_BROKER_CONVERSATIONS
--FOR ATTACH_REBUILD_LOG
GO
```
Here’s how I changed this on the Redgate blog:
```
CREATE DATABASE database_name
ON
PRIMARY ( 
  NAME = database_name_data,
  FILENAME = 'E:\SQLServer\MSSQL12.SQL2014\MSSQL\DATA\database_name.mdf'
) 
LOG ON
(
  NAME = database_name_tlog,
  FILENAME = 'E:\SQLServer\MSSQL12.SQL2014\MSSQL\Log\database_name.ldf'
) 
WITH
  TRUSTWORTHY ON
GO
```
However, I really want other things in place when I’m working in a dev environment. For example, I’ve started to want to ensure that I create random test databases with the Simple recovery model. While I usually have the model database set to Simple, that isn’t the default and I sometimes forget. As a result, I’ll add this code to my snippet:
```
ALTER DATABASE database_name SET RECOVERY SIMPLE;
```
I also usually want to change the growth settings. I don’t care too much about the total limit, since I use placeholders, but I do want a slightly larger initial size to prevent growths when I load test data. In my case, I usually want to specify an initial size larger than model. Having this in the snippet also means I can easily modify it.

However, I don’t want to make this complex. I’ll make this easy by using the GUI to generate template code. I can open the Create Database dialog and see my options, which I can change, as I’ve done for size in the image (50 from 1 for data).

There’s a dialog for growth, and as you can see, I’ve adjusted the values.

and for things like recovery model. I’ve open the dialog below. Below here are all the other database options I might want to set.

When I’m done, I can click the “Script button shown in this image. It’s at the top of the dialog.

Once I do this, I cancel out of the GUI, and I can see my code.

Now I’ll cut and paste the items I want into my SQL Prompt snippet. This gives me something like this:

I could have more or less options, depending on what matters to me. I might even have a cdbp snippet for production settings, where I have more items specified to be sure they’re set. It’s one thing to expect defaults, it’s another thing to think they won’t change and shouldn’t be specified.

Hopefully, you’ll see the value in SQL Prompt and start using snippets to improve your ability to code quickly and take the hassles and guesswork out of building clean SQL Code. If you’d like to see another take on this snippet, check out my Redgate blog.

Try a SQL Prompt evaluation today and then ask your boss to get you this productivity enhancing tool, or if you’re using the tool, practice using ii the next time you need to insert some data.

You can see a complete list of SQL Prompt tips at Redgate.
November 1, 2016