Voice of the DBA

Tag: Redgate

Watch Out for Automap in SQL Compare

I wrote a piece on SQL Compare and customizing the Automap feature. This was handy for me in a small project where I encountered drift and needed to fix the production side.

In the article, I don’t map one of the columns in the target. In this case, I’d actually be dropping the target column in SQL Compare. After all, the idea of SQL Compare is that we want to synchronize the databases.

If that’s not correct, then I’d need to do one of two things: add the column in development or add a filter. Filters are a great way to avoid issues in production when development items are in flight or uncertain (I’ve written about those as well).

In this case, if I really needed to keep this column in production, I wouldn’t want to add a filter. Instead, I’d really want to add the column in development. In fact, I’d want to add it in the same place it is (column order) in production, so in Development I’d rebuild the table with the new column, in that order, and then run the Compare again.

Automap can be a great feature, but it can’t know what your intentions are. It can only guess, like a new developer that is working on the system for the first time. SQL Compare needs guidance to do the job properly.

SQL Compare is here to help you, and if you haven’t used it, give it a try. If you’re on a version older than 12, I’d say you should upgrade to 13 immediately. If you work with SQL 2017, then move from SQL Compare 12 to 13 and ensure all your comparisons work with the new features.

November 27, 2017
Adding a Format SQL Button to the Redgate Toolbar

I’ve gotten used to CTRL+K,Y to format SQL with SQL Prompt, but a customer wanted a button on the toolbar. It’s fairly easy to do, but I thought I should document the process for others.

First, if you click the small area on the right of a toolbar in SSMS, you’ll get an “Add or Remove Buttons” menu, as shown here.

If you click that, you’ll see this dialog.

In this case, I’m happy with the buttons, I want to customize my toolbar. Click that option. This opens up the dialog below, and I’ll want to click the “add Command” to add a menu item as a button.

From here, I get a list of all SSMS menus. In this case, I’ve clicked the Dimension item, and I can see all the possible menu items on the right.

However, I want a SQL Prompt item, so I need to scroll down the left to SQL Prompt. Once I click that, I see the commands on the right,

Now scroll the right to find Format SQL.

Click OK and then your new button appears in the list.

And on the toolbar.

Now you GUI clickers can reformat SQL quickly.

November 14, 2017
Prepping for Summit 2017

It’s about time for the PASS 2017 Summit. The event essentially starts on Monday with pre-cons and the unofficial networking dinner. Be sure you RSVP and come to the dinner if you don’t have plans. The official event is

Many people are already traveling and packing for the event. I feel a bit behind as I won’t leave until late Monday afternoon and arrive late Monday night. I’m sure I’m not alone, but it seems like everyone’s ready and I’ve still got a day of work Monday.

Redgate Software has a booth and a few presentations next week on Wednesday. We’d love to chat with you about ways to make database development easier, especially if you’re thinking DevOps. We also have some contests, swag and prizes.

I’ll be around Tues, Wed, and Friday, and of course, moving from Game Night to the Redgate Party Thursday night. I’ve got other commitments Thursday day, but hope to see lots of you there.

October 27, 2017
SQL Clone Server Service Permissions
SQL Clone is amazing, and it can really save time and disk space for many organizations. I’ve got a series posted here on various little things I’ve learned about the product. There are also a number of articles on the Redgate Community Hub.

I was working on helping a customer install the SQL Clone server recently and one of the things that the client wanted to know was what are the minimum permissions needed for the SQL Clone Server.

When you install the SQL Clone server, the configuration dialog asks you for a Windows account and password. This is noted in the documentation as the account that configures and starts the server.

This means that during the configuration, this account will:
- Create a local service on the Clone Server OS
- Connect to the SQL Server specified
- Create a new database (or use the one that exists)
- Map itself to dbo in the new database
If the SQL Server can be a remote SQL Server from the SQL Clone server, a domain account is needed. If this is a local SQL Server, then you can use a local account. The account does need to have local administrator privileges.

With that in mind, here’s what I did as a minimum permission set:
- Create a new domain account, SQLCloneServer (I want to be able to use a remote SQL Server. I left this as just a member of Domain Users.
- Add this account as a local administrator on the SQL Clone server host.
- Add this AD user as a login to the SQL Server that will host the configuration databse
- Give the SQL user the dbcreator role (you can remove this later and leave them with permissions inside the db)
That’s it.

Scripting

It’s always better to script. Here’s the AD part in PowerShell:
```
New-ADUser -Name “SQL Clone Server” -GivenName “SQL” -Surname “Clone Server” -SamAccountName “SQLCloneServer” -UserPrincipalName SQLCloneServer@mydomain.com
```
Here’s the local SQL Clone, web server permissions part, using local commands. This could be in PoSh, but it’s not as clean (to me).
```
net localgroup Administrators "MyDomain\SQLCloneServer" /add
```
Here’s the SQL Part
```
USE [master]
GO
CREATE LOGIN [MYDOMAIN\MySQLCloneUser] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO
ALTER SERVER ROLE [dbcreator] ADD MEMBER [MYDOMAIN\MySQLCloneUser]
GO
```
October 23, 2017