The 700 Milestone

I’ve watched various SQL Saturday milestones go by. I missed 100 in Brazil, but did manage to get to 200 in Philadelphia and 300 in Kansas City. 400 was in Torino, and I am sad about not making the effort to get there. 500 was Boston, but I had a conflict. 600 Chicago, but again, other plans.

Colorado Springs was awarded 700, and I had to submit ASAP to that one. I’ve got a morning commitment for volleyball, but I’ll be racing down to get to the event and attend the after party.

I’ve got a repeat presentation, Branding Yourself for a Dream Job, but there are some other great talks. PowerBI from TJay Belt and Meagan Longoria, Optimizer secrets from Brian Hansen, Data Science from Reeves Smith and more.

Plus there are two great pre-cons on Friday. The Midnight DBAs talking Enterprise Maintenance and Kevin Kline giving you 50 things you need to know.

If you’re nearby, get a free day of training and say hi in the afternoon. If you can convince the boss, I’d say you should take advantage of one of these pre-cons as well.

Posted in Blog | Tagged , , | 2 Comments

Great Developers Use Source Control

I was rewatching Ike Ellis (b|t) talk on the habits of Great SQL Developers from SQL in the City 2018, and his first item was “Use Source Control“. I happen to agree with Ike, which is why I’m writing this, and I really hope you do as well. Certainly at Redgate we’ve built tools that help you get your T-SQL code into a VCS, but whether you want to work manually, use someone else’s tool, use our SQL Source Control or ReadyRoll, I’d ask that you consider getting all your code into some sort of VCS (Version Control System).

Ike notes that if viewers did this one thing, he’d be thrilled. I agree. Please, learn to use version control. If you wonder why, listen to Ike’s talk. He relates a story that notes that using source control doesn’t make you a better developer, but that better developers do use a VCS. This is a habit that helps build better habits and is a step on the journey to you becoming a great developer.

Does it help? Well, I think it does in some sense. Developers that use a VCS often build a habit of checking in changes before they try something that might be problematic. They also in a more integrated fashion with their work, and easily rollback problematic code without wasting time (or focus) trying to undo something. They get a previous version back and move forward.

I do think that this one thing changes the way you view code, and it provides you with a safety net. This is one of those skills that I’d really recommend you learning, as it will pay back it’s value tremendously over time as you learn to depend on the VCS and stop doing things like keeping multiple objects or files around, and trying to sort out what code is where. As you work with others, or even with your past self, you’ll learn to include better comments that help you change focus quickly and understand the particular reason behind a version of code. This will help you learn to be a better developer.

There are numerous ways to get your T-SQL code into a VCS. There are tools, but there are plenty of PoSh or other scripting methods. In fact, every DBA should get in the habit of scripting out instance level objects (which most tools don’t handle). Store them away, and then repeat as you need to make changes. You might be surprised how often you’ll be glad you have the previous version of a job, a schedule, a linked server, or more. I learned to keep all my scripts, from replication to running a quick report for a business user, in a VCS. I’ve never regretted this choice.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 3.7MB) podcast or subscribe to the feed at iTunes and Libsyn.

Posted in Editorial | Tagged , | Leave a comment

The Last iPhone

A few years ago the iPhone 7 was released. This was a big release for Apple, and I knew quite a few people that were ready to plunk down their credit card on day 1 and upgrade. I wasn’t, and in fact, I upgraded to an iPhone 6s+ from a 6 a few weeks after the release. At the time, my old phone felt slow and small, and I decided to make the upgrade sooner rather than later to my last iPhone. Why? Really for one main reason.

Most of us reading this work with technology and software. We often deal with upgrades to our tools and systems better than most of our friends, but not without complaint. Quite a few of us have been annoyed, or even angered, by the changes that Microsoft has made to software. From SSMS to Office, what the developers in Redmond might see as an upgrade, we see as a step back. One that interferes with our workflow and forces us to adapt and build new habits. The Ribbon bar in Office and the “Modern” look in Windows 8 come to mind as some of the more disruptive upgrades.

I get it. I work for Redgate Software, and we often struggle if we make significant UI or workflow changes to software. We might have reasons to view a change as better, but without a doubt, there are a percentage of users that always disagree. We try to ensure it’s a small percentage, performing a lot of research among customers before we implement a change, but someone will dislike our decision. As a side note, if you care about your software, participate in research with your vendors. You can help them determine if something should, or should not, change.

In the case of my iPhone, I use this device to listen to music quite often. Whether at the gym, in an airplane, or between matches I’m when I’m coaching, I depend on headphones. In my case, I prefer wired headphones and I wouldn’t buy a new iPhone because there was no headphone jack. I’m on the go, in various places, multiple vehicles, with different bags, and I don’t want to depend on a dongle, nor do I want to have bluetooth devices run out of charge. I also sweat and destroy or lose a few pairs of headphones a year. As I watched rumors, and contemplated upgrades, I decided that the iPhone 6S+ would be my last device.

I’ve owned 5 Apple mobiles, but I’m moving away from them for good. Not because of software, which I think is similar among both major platforms, but because of hardware here. I ordered a new phone, and I’ll say goodbye to the platform. I have liked iOS, and I think there are things it does better than Android, and certainly some conveniences that apps include, but Android is close, and I’ll adapt.

Most of us won’t do that in our career. We won’t change desktop OS or database platforms or even programming languages. We might adopt something new, but we often stick with what works. There are good reasons to do this, especially when we can take advantage of the built up knowledge. However, never say never. If there’s a very strong reason to switch, I’d like to think most of us would.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 5.3MB) podcast or subscribe to the feed at iTunes and Libsyn.

Posted in Editorial | Tagged , | Leave a comment

The SQL Privacy Summit

This May 18th, Redgate is putting on a SQL Privacy Summit for people that are looking for solutions to help them comply with the GDPR regulations.  I’ll be heading over to participate, and I’m looking forward to hearing from customers and attendees about the challenges they’re facing.


The Details

Friday May 18th 2018

The Grange Tower Bridge Hotel, 45 Prescot Street, London E1 8GP

8:15am – 5:30pm (GMT – convert)

The schedule is out and I’ll be doing a variation of a talk I’ve delivered before on how the GDPR is really asking for solid data practices that we’d all like to implement. There are some panel sessions and lots of networking time built in.


This is an all day conference, though early bird rates continue through this week. You can purchase a ticket for the event from the event announcement. If you’re a customer, contact sales, and you may be able to get a set of discounted tickets.

Hopefully I’ll see you there and we’ll get the chance to talk about how we can all do a better job securing and protecting our sensitive data.

Posted in Blog | Tagged , , , | Leave a comment

Powershell and Data Masking with SQL Provision

Just a quick post here after the PASS Marathon Webinar during which I talked about the GDPR effects around the world. In the talk, I demo’d SQL Provision, which is SQL Clone + Data Masker for SQL Server. Someone asked for the PoSh, so here it is.

# Connect to SQL Clone Server

$mycredential = Get-Credential

#$password = Get-Content .\socratescredential.txt | ConvertTo-SecureString

#$mycredential = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList "home\sjones", $password

Connect-SqlClone -ServerUrl 'http://SOCRATES:14145' -Credential $mycredential

# Set variables for Image and Clone Location

$SqlServerInstance = Get-SqlCloneSqlServerInstance -MachineName 'PLATO' -InstanceName 'SQL2016'

$SqlServerDevInstance = Get-SqlCloneSqlServerInstance -MachineName 'PLATO' -InstanceName 'SQL2016_qa'

$ImageDestination = Get-SqlCloneImageLocation -Path 'E:\SQLCloneImages'

$ImageScript = 'e:\Documents\Data Masker(SqlServer)\Masking Sets\datamaskdemo.DMSMaskSet'

# connect and create new image

$NewImage = New-SqlCloneImage -Name 'GDPRImage2' -SqlServerInstance $SqlServerInstance -DatabaseName DataMaskerDemo -Destination $ImageDestination -Modifications @(New-SqlCloneMask -Path $ImageScript) | Wait-SqlCloneOperation

#Demo pause

Start-Sleep -Seconds 2

$DevImage = Get-SqlCloneImage -Name 'GDPRImage2'

# Create New Masked Image from Clone

$DevImage | New-SqlClone -Name "GDPR2" -Location $SqlServerDevInstance | Wait-SqlCloneOperation

This uses SQL Clone, and I’ve coded in my server here, just for simplicity. I assume you could use your own variable or parameter, but if not, you need to learn a bit more PoSh.

This script works as follows: First, I get credentials for my server. I have a lab domain, but my primary desktop isn’t on the domain for various reasons. As a result, I need to authenticate. The Connect-SqlClone cmdlet is used to do this.

Once I connect, I need to get the SQL Server instance used for cloning. In this case, I need two. One  ($SqlServerInstance) is production and one is development ($SqlServerDevInstance).

I set the location for the image, which is a folder here, but this should be a share in your environment as you typically have images used for multiple developers. If you watched the webinar, that was the warning that popped up since I used a local path and not a share.

The data masking is done with Data Masker for SQL Server. I’ve written a few pieces on this, but essentially the GUI creates a file that describes the masking rules. In this case, I set a variable to the file.

The creation of the image is with the New-SqlCloneImage cmdlet. This needs an instance to use for copying the data and then the parameters for the db and the masking script. The key here is the script needs to be a New-SqlCloneMask object. Hence the @(). If you use a T-SQL script, and you can, you need to use the New-SqlCloneSqlScript object.

From there I use New-SqlClone to cerate the actual database for the developers. I could automate this and create multiple ones if I wanted.

One last note, Wait-SqlCloneOperation is good in scripts as a few things take time to complete. I added the Start-Sleep for demos since things take longer at times, and in a demo seconds matter. I need the consistency. In an overnight script, I’d likely leave this out.

Posted in Blog | Tagged , , , , | Leave a comment

The Reliable DBA

Many of us would consider ourselves to be reliable at work. Our employers count on us, co-workers may assume we’re handling certain tasks, and it’s human nature to think that we are meeting our obligations and responsibilities. Maybe not 100% of the time, but certainly I know my goal is to complete tasks I’ve committed to, on time, and to avoid dropping any balls that I’m juggling. I think I am mostly successful here, but certainly am late or forgetful about things at times.

However, as a group, department, or even a set of services, does your organization think highly of the databases? Are your systems meeting their SLAs for performance and availability? If not, does your group respond in a timely manner? We sometimes think that we are, but without feedback and communication can we be sure? This certainly could be taken the other way, with other groups constantly complaining about your performance, even while you are meeting your commitments.

I ran across a talk from Uber on reliability. It’s more of a high level architecture talk about distributed systems and being able to detect and respond to issues. Certainly Uber works at a rate and scale that few of us will reach in our organizations. Add to the highly public nature and real time demands, and reliability is extremely important for their business. Mistakes can have dramatic hard dollar effects instantly, and there is a lot of pressure on their staff.

For most of us, our databases do continue to become more important, and even when we are meeting our SLA commitments, are we providing reliable service from the entire staff? Is information being shared, with root cause analysis or retrospectives that help knowledge transfer among all of the individuals that might respond to an issue? Are you dependent on a superstar that must be called in to solve issues with the database, network, or storage?

I’ve been the main person on call, the expert for a system that received calls on weekends, vacation, and other downtime. It’s no fun to be in this position, and it certainly distorts work life balance, not to mention upsetting the rest of my family. My goal is to be there if necessary, but train others so that they can provide a consistent, similar level of service to customers and clients if need be.

Becoming too dependent on any one person isn’t much different than becoming too dependent on one server or disk drive or network cable. At some point you’ll have a failure and that item will no longer be available. If you don’t have a spare, you’ll have regrets.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 4.1MB) podcast or subscribe to the feed at iTunes and Libsyn.

Posted in Editorial | Tagged , | Leave a comment

Looking Forward for T-SQL Tuesday #100

tsqltuesdayIt’s T-SQL Tuesday #100. Amazing. I can’t believe this has been going for so many years. I’m proud that I had the chance to host the main archive site at and host a few topics.

This month we return to the original founder, Adam Machanic, with the topic of looking forward 100 months. I wrote about predictions last week, so here’s a shot at a few more details.

July 2026

I’m approaching the big 60. It’s a year away, and I can remember joking with Allen White about how old he’s getting. In fact, it wasn’t too long ago that I had the chance to mourn that milestone with Grant Fritchey as well. Apparently my turn is coming, but that’s fine.

Life is good and I still enjoy working with SQL Server. It’s been 35 years now that I’ve been on the platform and after a slow few years of few versions, things have taken off. As I write this, I’m working on CTP of v23. Microsoft tried to do away with version numbers, but there are still many of us that need to run our own SQL Server instance. We expect SQL Server 2026 to be released in a few months, and it’s looking good so far. I’m especially pleased that Microsoft has continued to produce separate versions of SQL Server for different platforms.

Linux and Beyond

A few years back we got a version of SQL Server on Linux. I remember being both pleased and surprised by the first demo I saw back in 2016. As I learned more about the process, it was a neat engineering challenge for the team, but once they conquered the underlying API mapping, the rest of the code based was roughly the same. This work allowed Microsoft to continue to expand their platforms, and today we have a small version of SQL Server Express on ARM processors, which can automatically sync data with our larger systems.

Speaking of syncing…

Replication Works

I’m not sure how or why, but in 2021, Microsoft finally started to put some resources onto replication. I suspect they realized that many companies need more than just HA solutions for full databases. Customers need to move data around for other systems, to ensure MDM works, and gain compliance with the GDPR, UK, Japanese, and other legal frameworks. Customers also need to move subsets around for reporting, so replication got some love.

It’s more robust, requiring DNS to register the publishers, distributors, and subscribers. We have custom schema changes that can move data, and the entire process actually has tooling in SSMS to help monitor the system.

All that work with Linux helped, with better network discovery and reliability across all SQL Server versions. Parts of the system are built on the old Data Sync architecture, and more people are willing to use it since we don’t have to drop and rebuild replication for schema changes.

Multiple Query Engines

A little over a 100 months ago, we got the graph query engine in SQL Server 2017. It wasn’t that popular, but people used it and now we can use both Gremlin and Cypher.

SQL Server also added MongoDB API support, as well as a streaming database option for the incredible levels of data ingest caused by the IoT device growth. These are add-ons, and at least with MongoDB, they’re free in all editions. Streaming requires enterprise, which makes sense. Smaller companies don’t really need that level of data ingest.

Cloud First

Almost everyone is thinking cloud first. Thankfully, Microsoft changed the pricing of Stretch Database, and allowed us to move data to the cloud or back again to meet our needs. This has been helpful as most companies use a hybrid system, and as they grow and stabilize their workloads, often want to bring some of their data back into their own data center.

We’ve gotten more choices, as well. There are third party vendors, like Rackspace, that can run an Azure SQL Database or a CosmosDB instance. Microsoft licenses the system and provides third level technical support, but this has made all the difference in the world. Companies know they aren’t stuck with Microsoft for Azure SQL Database, so they’re more willing to give it a try.

Closer to the End

It’s been a great SQL Server ride for me. At this point I’ve been working with SQL Server for 40 years, and I’m getting close to the end of my career. I really enjoy the platform, and I’m still keeping busy with SQLServerCentral, Redgate, and a few clients, but I’m also taking more and more time off.

Hopefully you’ve had a great career to this point, and you will continue to enjoy working with SQL Server, as I have.

Posted in Blog | Tagged , | Leave a comment

Is Security Catching On?

This is a good sign, as a poll shows people want security over convenience, especially younger people. Surprisingly this is also true for older people, who likely don’t trust much of our new technology. This is the first time that a majority of poll respondents haven’t been happy to have some easy password acceptable or the convenience of linked accounts. More and more people are embracing biometrics and using password managers with separate passwords for services, especially those that involve sensitive data. That’s a change for the better, since security is important if we are to continue to grow and enhance the use of digital services in our world.

I think many of the issues with security are because of people near my age, the generation that has grown up as technology has. We’ve embraced and extended the very rudimentary systems that existed before Internet use and mobile devices became widespread. We also learned to build services in a way that gets them up and running without considering security from the beginning. Many of us grew up with computers that were either on or off, without any security implemented. We learned to network with completely open shares, and didn’t bother with access control. This is the same way many developers learned to build SQL Server applications using the sa account, not wanting to spend time learning the (basic) intricacies of SQL Server security.

As we deal with an ever growing number of data breaches (over a thousand last year), it seems that many of the people working in technology, likely from my generation, still have not learned to build security early into an application. We add security later, after we’ve gotten a prototype or minimally viable product released. In many cases, we might never build security until we suffer some sort of incident. That’s the wrong time to start looking at limiting access.

With the GDPR being enforced this spring, there will be plenty of pressure on companies to avoid being in the headlines for a data security issue. I’m hoping that customers will continue to put pressure on organizations to adopt better security measures.  Between customers and new legislation in various countries, I’d hope that more managers would understand that security is important and make it a priority for their staff. Most technical people want better security, even at the expense of a little inconvenience. These changing times might just allow us to get our wish.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 3.8MB) podcast or subscribe to the feed at iTunes and Libsyn.

Posted in Editorial | Tagged | Leave a comment

Catch up on SQL in the City 2018–GDPR Edition

The videos for our SQL in the City 2018 broadcast from last month are up on the Redgate channel in a playlist. If you missed anything or would like to rewatch something, you can do it.

If you’ve got questions or comments, we’d love to hear from you.

Posted in Blog | Tagged , , | Leave a comment

Learning Goals 2018–February Review

Better than month 1. Only XE this month.

My first week I got a reminder to spend some time on XE, so I watched a couple modules of Jonathan Kehayias’s Pluralsight course.  A lot of review, but I thought I’d start with the basics and Jon knows more about this than anyone I know.

I spent a few days going through this. Some at my desk, but more while riding a stationary bike. This gave me the chance to actually sit and watch without being distracted by other items on the computer.

I got through about a third of the course, but I still have more to watch. I also took about an hour in two sessions to practice a few of the items, explore how XE is setup and build some questions for SQLServerCentral. This allowed me to feel good about the process.


The first couple weeks of February were good here. Unfortunately things didn’t continue. I had travel the last week of Feb and a number of meetings that required prep and I got off track in working through XE.

Even today, as Mar starts, I have missed an hour or learning time.

On the plus side, I did take 3 days of vacation in Feb, which took away work and learning time, but allowed me to recharge a bit. I’ll continue that a bit in March, so it’s possible that I might not quite get what I want here, especially as I have a number of weekend commitments this month.

This is a good lesson, allowing me to realize that I can’t count on adding to my busy schedule without having to make hard choices. Learning has been one of those lower priority items, though I am trying to continue growing in this area.

Posted in Blog | Tagged , , | Leave a comment