Quick PoSh SQL Provision Databases for New Developers

As part of some presentation work, I wanted to demonstrate some onboarding of new developers. To that end, as part of a demo, I wanted to build a script that would take a few parameters and generate some SQL Provision databases for development. The idea is that an admin can have a script here to set up a developer with a database for a project with SQL Provision. I imagine this would be part of a script that also cloned a repo, set up a project, etc.

The Process

My thought here was that I needed a script that uses some pattern to build databases. The one I decided on was that the basic image name would be used with the developer name to prevent collisions. This would work if an admin used login names or even nicknames, as long as there is some uniqueness on the instances. Of course, if you’re provisioning to a local instance, this doesn’t matter.

In any case, the images I make are often date driven, with a format like:


Using formats like this, I can easily strip off the beginning part of the name (SimpleTalk) and then append my _developer to the end. For me, this means I’d get a database named: SimpleTalk_Steve

This manifests itself as a script:

2018-10-03 20_09_05-NewDeveloper.ps1 - Minimize impact Dev Test Environments - Visual Studio Code

That produces a new database.

2018-10-03 20_08_57-SQLQuery6.sql - Plato_SQL2016.DataMaskerDemo (PLATO_Steve (61)) - Microsoft SQL

I also see this in the SQL Provision dashboard as a new item.

2018-10-03 20_10_14-Microsoft Edge

The Script

I started this with some simple PowerShell. The first part of this script is a few parameters and a variable. Note that I’m splitting the instance name here.

2018-10-03 20_11_48-● NewDeveloper.ps1 - Minimize impact Dev Test Environments - Visual Studio Code

From here, I connect to the SQL Provision server and then get the instance and image objects.

2018-10-03 20_12_01-● NewDeveloper.ps1 - Minimize impact Dev Test Environments - Visual Studio Code

Lastly, I create the clone, making a new name from the image, and if the word “base” is included (I do this often), I strip it out.

2018-10-03 20_12_06-● NewDeveloper.ps1 - Minimize impact Dev Test Environments - Visual Studio Code

Quick and easy, but this allows me to demo how to onboard a new developer.

There are lots of enhancements, and I need to add some error checking if there isn’t an instance, and if the connection fails, but for now, PoSh lets me quickly start getting some useful scripts that I can use for demonstrating some functionality. This certainly would work in an environment where I knew the Clone server was there and I had control over imaging. If not, I’d be writing more PowerShell.

SQL Provision is pretty amazing and lets you really leverage technology to provide developers with copies of databases in seconds. Download an evaluation and see what you think.

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

Start the DevOps Journey

More and more, I find that getting someone’s mindset to change is the major impediment to changing to a DevOps process. Once a person starts to believe in DevOps and accept a new way of working, they can rapidly start to improve the efficiency of their work. Moving on to change the mindset of a team (or teams) is even harder, but this is arguably the most important part of changing your software development process.

I don’t have a great way of changing everyone’s mindset, but if you want to start, I have a thought for you that might add a little work, but will help you understand how to make changes in the way you work and experiment with a better way to deploy database changes.

The tools are the easy part, and I want to help you get comfortable because the harder part is then trusting your process and helping others to learn how to trust a DevOps flow as well. To get you to start that journey, you need to learn one skill: how to execute your changes from the command line.

That’s it. Think about how you will manually execute your tasks now, and then ensure there are command line execution processes for these. This could be a call to SQLCMD with a filename as a parameter or it could be a DACPAC deployment with sqlpackage.exe. If you are a Redgate customer, we have PoSh cmdlets for deploying changes with SQL Change Automation or command line SQL Compare calls.

A command line interface (CLI) is really the key for the DevOps toolset to succeed. That’s only a small part of the process, since you need cultural change, you need to still model your database well and write good code, and you still need to learn how to improve your code over time. Adding a DevOps process allows you to spend time on code quality, testing, and skill improvement, instead of assembling changes and files for that next deployment.

Steve Jones

The Voice of the DBA Podcast

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

Posted in Editorial | Tagged | Leave a comment

The Developer’s Guide to Azure–A Good Overview

Someone sent me a link a few weeks back  about a free book from Microsoft. It’s the Developer’s Guide to Azure, and you can download it in Kindle or other formats. I actually got it for the Kindle on my phone and went through it in my spare time during the last couple weeks.


The book is a typical overview e-book, similar to what I’ve found in other areas from Microsoft. It’s very high level, with some examples and code, but very limited in scope and coverage.

What I did like about the book is that it really describes all of the different services that Azure offers at this time, with some examples of how you might think about them. There were a few services I hadn’t heard of, so this gave me a tiny bit of insight into what they are and why they might be useful for me.

If you haven’t spent a lot of time looking at Azure and keeping up with the advancements, you might pick up this book as a good general guide that will help you think about which areas you need to focus more attention, read about, or even build a PoC.

I was especially interested in the security section, as I haven’t done much myself with Azure Active Directory and I wasn’t aware of all the services that exist.

The book ends with a nice walkthrough of setting up some different services and configuring them. I haven’t completed these, but I might go through some and even work these into presentations, as I think Azure has a lot to offer that I could use in an environment I can access from anywhere.

Posted in Blog | Tagged , | Leave a comment

Cloud Backup

I think that backup and restore are the most critical things for any database professional to master. Whether you’re a professional DBA, a developer setting up a system, or a seasoned DBA, I would argue having your data safe is the primary task. Security comes next, then performance, availability, and a number of other tasks that could go in any order, but if we don’t have a way to restore our data when hardware fails, we’re in trouble. And given enough time, or enough difference pieces and parts, something will fail. If you can’t restore a system when a problem occurs, that’s what Grant would call an RGE.

Over the years companies have moved to many different technologies to handle backups. Tape was common early in my career, but all disk systems, with de-duplication capabilities have become popular. I really don’t think about anything other than getting a second (or third) copy of data these days, so I can’t speak to any particular way of managing backups. However, for SQL Server, I do want the option to set full, differential, and log backups based on my RPO and RTO requirements.

There seems to be a new trend for companies that I ran across: they’re moving to the cloud. Here’s a short slideshow of some stats that show cloud use is increasing. This is a survey, so it’s not all companies, but the trends are clear. More data is being backed up, and it’s likely easier (and cheaper) to use cold storage in the cloud. That makes sense since it’s data that you expect you’ll very rarely need to use in a restore.

There are a couple of other interesting items I saw in the survey. The number of companies that are backing up more than 100TB grew quite a bit, even as the number of companies backing up < 25TB fell. That’s a sign that we’re capturing more data. Whether we need to, or whether legislation like the GDPR will get companies to trim some of that data, remains to be seen.

Another interesting item is that more companies are testing their DR plans. Fewer never test them, but the frequency is increasing with more companies testing quarterly or monthly. That’s smart as we become more dependent on computer systems. I know some organizations can’t roll back to paper, as we’ve seen in a number of airline IT issues. If you never test your DR plan, I hope that you don’t have an issue when you can ill afford to find another job, becuase you might suffer those consequences. Really, I hope that you actually know this isn’t professional and you start working on ways to test a restore of service.

More companies are moving to the cloud, and the resistance for security, cost, privacy, etc. reasons is going down. I think many of the concerns that both executives and IT professionals have had in the past are proving to be non-issues. This is especially true as more vendors institute government rated or more secure data centers as a part of their product offering.

If you don’t like the cloud, that’s fine. If you don’t know anything about it except rumor, guesses, or hearsay, I’d suggest you learn more. The cloud is likely coming into your career, so learn a bit about it. At least enough to give reasons why you don’t want to move your data there. If you do, you might be surprised that the cloud is not that bad a place to be, at least for some workloads.

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

Are Indexes Actually Changes to the System?

I haven’t thought about this in some time, but Brent Ozar raises an interesting question: should index changes require change control? I’ve worked in an organization that didn’t consider an index change to be an item that was presented as an official change. They were seen somewhat like adding new users or changing a firewall rule. These were ticket requests that could be triaged and made by an individual group without notifying the rest of IT.

I’ve also worked in organisations where an index change was viewed as a deployment and subject to change control procedures. Not that the network or security people cared about the details of the index, though the help desk might. These just wanted to be aware as any deployment change could affect their departments.

In today’s world where many companies seek to adapt faster to changing customer demands or market opportunities, I think the idea of meeting every week to discuss any upgrades or changes to applications is somewhat silly. Certainly large changes, like major application upgrades to ERP systems, new security hardware, and alteration of core infrastructure ought to be debated in a larger group. Deploying changes to an application isn’t one I’d think deserves the debate. At least not if your organization is trying to become more efficient.

I do think a way to avoid issues is to work more closely with the development group if you are in operations or vice versa. I’d be sure to let other app teams know about index changes? Certainly Brent lists a number of potential problems, many of which could occur. A new feature might break and prevent inserts, exports could use more disk space, imports might eat up data and backup space, queries could cause slowdowns or blocking. These are all possible, maybe even probable.

I agree with Brent in that any changes need to be tested. I don’t know about any sort of formal change control, but there ought to be a process that evaluates the changes and mimics them in an environment that allows you to determine the production impact. Using a known, automated process with testing and well documented deployment actions will reduce the risk, but perhaps not eliminate it. However, when you find issues, if you have an automated process, it’s easy to add tests or alter the process to ensure that it will succeed in the future. Especially if you examine the effects of these changes and learn to avoid problematic development or deployment patterns in the future.

Steve Jones

The Voice of the DBA Podcast

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

Posted in Editorial | Tagged , | Leave a comment

More than 25 Years for me

I was surprised to see a 25 year celebration for SQL Server at Ignite recently. It was also a 25 year celebration for Bob Ward. If you haven’t met Bob or seen him speak, make an effort to do so. He does an amazing job and can likely answer most any question you pose about SQL Server. I didn’t know Bob had been at Microsoft for 25 years, but that’s an impressive milestone and I’ll congratulate him when I see him in person later this month at the SQL in the City Chicago summit (register now:code Steve).

The surprise for me was that I installed SQL Server in 1991, which by my calculations, was 27 years ago. It was in the fall of 1991 that a corporate development department came down to our remote site and had us install a SQL Server in preparation for a new application that we’d install on Dec 31, 1991.

However, if you read the celebration post from Amit Banerjee, you will see that he notes in 1993 SQL Server released on Windows NT. That’s true, and I remember getting a wide box of NT 3.1 Advanced Server manuals that I dutifully read since I hadn’t been thrilled with the performance and stability of SQL Server to this point.

You see, in 1991, we installed SQL Server on OS/2 1.3, which was horribly unstable and unable to handle the load of our application. I’m not sure if the SQL Server port from Sybase was the issue or OS/2 wasn’t stable, or the hardware wasn’t sufficient. Suffice it to say that I was thrilled when we migrated in 1992 to OS/2 20 and later 2.1, which were more stable. Then I could stop working 100 hour weeks.

Despite a poor first impression from me, I grew to really enjoy SQL Server and switched from networking and infrastructure to database work. The rest, as they say, is history.

Posted in Blog | Tagged | 2 Comments

Give Up on Natural Primary Keys

There is plenty of debate over how to design your database. At SQLServerCentral we have a Stairway Series as well as a few articles that cover design topics. I think it’s important for anyone that builds tables to spend some time learning what others have done and understand the pros and cons of making different choices. It does often become hard to change designs once they are in use, so trying to choose a good entity design early is important.

One of the things I think is important in modeling your particular entity is including a primary key (PK). In my DevOps talk I stress this, as I’d rather most attendees come away thinking a PK is important as their first takeaway from the session. There are exceptions, but they are rare, and I would prefer that most tables just have some PK included from the beginning.

A PK ought to be stable as well, and there are plenty of written words about how to pick the PK for your particular problem domain. Often I have received the advice that natural keys are preferred over surrogate keys, and it is worth the effort to try and identify a suitable column (or set of columns) that will guarantee uniqueness. I think that’s good advice, and it’s also advice I tend to ignore.

There’s an interesting article about keys and the GDPR. The first part is a rather basic description of what PKs are, but the second part talks about keys and some of the rights that data subjects have under the GDPR. I think these are worth considering, especially as it’s likely similar legislation will make its way into other jurisdictions, as already seen in California. The short part of the argument is that the right to be forgotten or to have your data deleted is incompatible with the use of natural keys.

It’s an argument, though I’m not completely sure if I think it would be solid. There are valid reasons to keep some information about a user, and I suspect keeping a list of emails to delete from a database restore as a separate list would be a valid use. Even if the user asked that their information was removed. It would be, but there would also be a need to ensure that the correct data was removed,  hence a list of emails.

The bigger problem for me is that if I needed to redact or alter this key data, which I would likely do in order to keep some integrity in my database, I’d need to alter this data in lots of tables. That makes for a much more complex set of scripts, including ensuring that I am correctly building a map of the new values I would use for a key. It’s much easier to have a surrogate key that doesn’t change and just redact the other information.

I’m sure there are arguments both ways, but as we move towards the era of not only seeing data as valuable, but also as an asset we can’t completely control, I think surrogate keys make more sense now than ever. Let me know if you agree.

Steve Jones

The Voice of the DBA Podcast

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

Posted in Editorial | Tagged | 3 Comments

Redgate Releases Sept 2018–Practicing DevOps

At Redgate, we build tools to help you build software in the entire DevOps cycle. In fact, I love this new graphic that shows the areas that we focus on in the entire software development cycle.

We don’t just preach DevOps, we live it. As an example, here are the releases for September:

SQL Monitor (Sept 11, 20) – New viewing and reporting options

SQL Clone (Sept 11, 24, 25) – Added reset, new notifications and an EAP of the  next version!!!!

Data Masker/Figleaf – Sept 5, 14, 25, 26 – Bug fixes for Data Masker and enhancements to Figleaf

SQL Change Automation – Sept 19 – cmdlet updates and bug fixes

SQL Backup – Sept 6 – import registered servers

SQL Compare/Data Compare – Sept 3, 17, 24 – bug fixes in frequent updates releases

SQL Prompt – Sept 12, 25 – refactor insert into updates, bug fixes

SQL Source Control – Sept 24 – bug fixes

SQL Test – Sept 4 – bug fixes

SQL Index Manager – Sept 3 – bug fixes

Posted in Blog | Tagged , | Leave a comment

The Ever Expanding Data Platform

This past week was the 2018 Ignite conference from Microsoft, where we had a number of announcements about the data platform. You can rewatch some of the sessions from the event, and I might recommend the keynotes to see some of the demos and positioning of the data platform. That’s the direction that Microsoft is moving their database products, as a complete platform that not only includes SQL Server, but CosmosDB, Managed Instances, Data Lakes, and  more.

If you’re a SQL Server DBA, it’s time to stop thinking yourself as a SQL Server DBA or developer. Instead, you need to be a data professional, especially on the Microsoft stack. While you might concentrate on SQL Server and live in SSMS, you ought to be aware of the growing options for working on the Microsoft stack. Azure Data Studio, which Grant wrote about this week, ought to be a tool you investigate. You also ought to be looking at the latest version of SSMS, which had it’s v18 move into a public preview this week. With these tools being free, companies ought to be moving away from the older versions that shipped with SQL Server 2014 and earlier. Instead you should at least be on a v17 version of SSMS. Talk to your IT group and give it a try today. It works fine with all your SQL Server versions, from 2005 through 2017.

Microsoft is certainly hoping you’ll run more workloads in Azure, and that’s where the data platform is growing. CosmosDB, which I think has a lot of promise for various types problem domains, or even as a companion to SQL Server for certain types of data. There is an increase in their SLA to 5 9s, which is both impressive and ambitious. I know very few on-premises instances that get by with 5 9s across multiple years, leaving aside the ability to get 10ms write performance in the SLA. The is also multi master replication and support for the Cassandra API. While I haven’t done much with CosmosDB, it is on my radar to experiment with as a data store option.

Managed Instances will be generally available on Oct 1, just a couple days away. While I wasn’t sure that this product would catch on, I’m not surprised that some companies would like to get away from managing most of the stuff around the database and stick with the data. To me, this, more than anything else, can mean that DBAs at larger companies need to be managing data, security, and more, without worrying too much about the basics of backups and HA. Even threat detection, something few of us are good at, is handled by Azure. The restore demo in the keynote is truly impressive. I’m not sure many of us would want to, or be able to, architect those speeds. At least not as easy as provisioning an Azure Managed Instance.

There are lots of other announcements, which you can read. The one really interesting thing for me was the Data Box announcement. I’ve had more than a few people be concerned about the initial loads of data into an Azure database or data lake. I’ve had that concern, and actually been part of a company that FedEx shipped a rack of disks as part of a SAN to a DR site because of bandwidth constraints. The Data Box is a device that you can order and fill, shipping this back to Azure for loading. It comes in 40TB, 100TB, abd 1PB sizes. That is truly stunning to me. Drop ship 1PB if you have the need. You can even see a picture of it from Argenis Fernandez for some idea of size.

It’s an exciting time to be a data professional, and Microsoft’s data platform continues to grow. I don’t know that any of us will know more than a tiny bit about most of the platform, but I certainly plan on increasing my knowledge in a few areas to become better aware of how they work and what they are capable of. I might not be able to use them well, but I can at least have enough knowledge to have a conversation about the technology and have an idea of whether it might solve a problem that I run into at work.

Steve Jones

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

Understanding a UK Keyboard for a US developer

One of the very interesting parts of my job is that I run into UK keyboards on a regular basis. Redgate is a UK company and most of the trade show laptops that they bring to events are UK layouts. Our VMs are built in the UK, and when I use one of those, I need to remember that some of the keys are different.

I needed to learn the difference between the layouts, because when you’re in a hurry, you can’t scan the keyboard. I found a nice visualization and summary on Wikipedia. I’ve used that a few times, and with Kendra starting as a new Redgate evangelist, I scanned it again. I haven’t worked on a UK keyboard in a few months, so I want to highlight the differences in my memory for upcoming conferences.

For me, the main differences I need to remember are the at symbol (@ ) and quotes (“) are switched. That means for logging in with emails, I need to smoothly use what I know as quotes for the @. I rarely use quotes, so this isn’t an issue.

I do use the $ at times, and there isn’t a good way to get this without messing around. The same thing for the hash or pound (#) key. If I really need those, I often just end up changing the keyboard layout.

Posted in Blog | Tagged , | Leave a comment