Voice of the DBA

Tag: syndicated

Redgate at Summit 2017

I’ll be heading out to the PASS Summit next week, spending Halloween in a conference center with a lot of other geeks. I know some of you won’t make it, and we’ll miss you, but I understand.

I’m meeting my colleagues from Redgate there, including the new editor of Simple Talk, Kathi Kellenberger. We’ll be at the event all week and we look forward to chatting with you about databases, DevOps, and more. We’ve also got a few events during the week that we hope you’ll attend.

Stop by the Redgate Booth in the expo center for a demo, or just to chat about SQL Server.

Adopting a DevOps Process for Your Database

I’ve got a session on Wednesday, Nov 1, at 1:30 that will provide an overview of how you might rub some DevOps on your database development. I’ll use some of the Redgate tools, but really I want you convince you that a smoother, more reliable database development process is possible.

Lower your risk, make your deployments reliable and repeatable. Come join me, see how the process can work, and ask lots of questions. I’ll have lots of answers.

How DevOps for the Database Helps with Compliance

Immediately after my DevOps talk, I’ll be joining Grant Fritchey and Richard Macaskill to talk about DevOps and compliance. We are seeing more and more organizations bound by rules and regulations that require them to be careful about who can see their production data and how it’s stored. This will mean that more of us need to be careful about how we manage data.

With the move to DevOps and releasing changes more often, this can present some challenges for data professionals. However, DevOps can actually help with compliance if done right. Join Grant, Richard, and I for a discussion of the challenges and potential solutions.

Redgate Rocks

If you are coming to the Summit, make sure you stop by the Redgate booth to pick up your #RedgateRocks ribbon for entrance to our party Thursday night. I’ll be there with the Redgate crew to celebrate the last evening of the Summit at 1927 Events.

This is your chance to get out of the Conference Center, head towards the ocean and have a good time in downtown Seattle with us.

October 24, 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
Monday Night Networking at Summit 2017
Next week is the PASS Summit in Seattle. Thousands of SQL Server professionals are going, many coming Sunday or Monday for a pre-conference session before the main event. For a number of years, Andy Warren and I have run a networking dinner on Monday night, but we’re stepping back this year because of scheduling.

Luckily, Lisa Bohm has taken over the Monday Night Networking dinner, and you can RSVP for the event from her blog. Andy and I are thrilled that Lisa is managing this, and we’d ask for a couple things from you:
- Pass the word to others, especially first timers. Blog, tweet, post on LinkedIn and Facebook. Let people know.
- It would be nice if a few of you would volunteer an hour of your time to help Lisa greet people and introduce first timers to others.
I’m sorry that I won’t be there. I’m coming late to the Summit, as is Andy, so please help a little and make this a success.
October 23, 2017
SQL Data Generator–Getting a value based on another column

This is a series on SQL Data Generator, covering some interesting scenarios I’ve run into. If you’ve never tried it, SQL Data Generator is a part of the SQL Toolbelt. Give it a try today with an evaluation today.

One of the things that people often want to do is generate data, but limit the generation to some data in another column. Here’s a good example. Let’s suppose I have some data that represents a balance in an account. That’s in a column we’ll call Balance. In another column, I have a status that is either OK or Overdrawn, depending on whether the Balance column is positive or negative.

If I perform a random generation on these columns, I’ll get some strange data. Sometimes the data below matches up, sometimes it doesn’t. I have positive numbers as Overdrawn and negatives as OK

Let’s fix that.

In Data Generator, I have a variety of choices for the generators. Let’s look at what I can do for the Status column.

Certainly there are RegEx and Python scripts, but there’s a nice “Cross Column” section with some examples. In this case, let’s look at the Age in Years generator. The definition is:

In this case, it’s a simple .NET date function and some math. I can do that. Most importantly, I can see the “Insert Column Name”, which lets me pick another column in my table.

Python

The language of choice in SQL Data Generator is Python, specifically Iron Python. Outside of C# Datatime values, Python is needed. If you examine any of the other cross column items, you’ll see we need a main() function that returns something.

In this case, it’s a simple expression. I’ll use an If statement to check if the Balance is >= 0. Here’s a Python construct.

def main(config):
     if Balance >= 0:
         return “OK”
     else:
         return “Overdrawn”

Whitespace matters, as does indentation. If I put this in like so:

I’ll get this. Notice that the status is correct.

In a real project, you may have more complicated logic, or more likely, status values. One way to handle those is to use a Python function and return the appropriate values for your system.

You can build some complex and interesting data generation projects with SQL Data Generator. Give it a try today.

October 19, 2017