Voice of the DBA

Category: Blog

Moving to AWS EC2

SQLServerCentral has been stuck in the past for some time, but we’ve finally decided to move. I’ve been asking for this, and it’s usually a resource issue. However, earlier this year we started to debate and examine the costs, and decided to pursue a move to EC2. We have other infrastructure there, VPNs setup to connect, and this made the most sense.

I was excited. The last time we upgraded, we left SQL Server 2000 for SQL Server 2008 R2. That was a long time ago, and at the time, I wasn’t sure that 2008 R2 was a big change. We used the core engine only, and it didn’t seem that this was that cost effective a move for us. Now I wish we’d delayed another year and moved to 2012.

We’re moving into the current era, choosing to run on SQL Server 2017. I’ll have access to lots of the latest and greatest T-SQL, which will help with some of our complex reporting tasks.

Cutover

I didn’t do the work, but one of our engineers practiced a backup and restore, and then setup log shipping to limit the downtime. After a few days of testing, and a few of us running the new site through our own tests, we made the cutover.

Total downtime, about 35 minutes to quiesce the old system, get the last logs copied over, and then move networking.

And we’re live.

We have six Basic Availability Groups setup. We could potentially have issues with a failover in that it’s possible that a user does something in one BAG that doesn’t make it to the other side, but in practice, it’s not a concern. We don’t deal with sensitive information, and likely any data lots can be manually moved.

This was the most cost effective move for us, roughly equivalent hardware, though we moved from physical db servers at Rackspace to VMs in EC2. However, we’re now on modern versions of Windows, SQL, and more importantly, .NET. That last item was slowing some development items, so we can now start to move forward with future development.

March 28, 2018
The 700 Milestone for SQL Saturday

This past weekend was SQL Saturday #700. Another milestone in the franchise of events, and I was glad to attend. This is my fifth or sixth Colorado Springs event, and everything ran great. The leadership has changed over the years, as has the structure and format, but attendance continues to be strong and reliable.

This year I was late, coming down after coaching youth volleyball. I arrived just as people were outside at the food trucks. That was a great idea, one that I think they took from our “no lunch”, bare-bones SQL Saturday in Denver. A few trucks pulled up and attendees could go outside and choose during a long break for lunch.

This is a smaller event, but consistently draws over 100. Many of them were still around for the end of day raffles, as you can see above.

I’m glad to see the changing of leadership over the years hasn’t prevented this event from continuing. I always worry about sustainability as organizers grow through life and become busy or change careers. As many of us age, retirement is also an issue, and it’s important that we continue to find ways to hand off responsibilities and knowledge, as well as enthusiasm to others.

I was only around for a couple sessions, including my own, but I did get a chance to hang out and chat with friends, even getting a selfie with a few.

I’d love to see more of you at the next Colorado Springs, but really more of you make an effort to attend, speak, volunteer, or organize for future SQL Saturdays. Fingers crossed we’ll meet at 800 (which should be coming soon).

March 26, 2018
Scripting Tables with dbatools

I really like the dbatools project. This is a series of PowerShell cmdlets that are built by the community and incredibly useful for migrations between SQL Servers, but also for various administrative actions. I have a short series on these items.

I’ve been experimenting with scripting some tables, and I wanted to check to see if dbatools made that easier. I was amazed by the number of new cmdlets in the project, and after hunting around, I found a couple cmdlets: Get-DbaTable and Export-DbaScript. I decided to see how these work.

My first experiment was to run Get-DbaTable. This takes an instance and database as parameters and returns a set of table objects. I get space and size information and some metadata. As a short look, here’s one table.

I decided to use this as input to Export-DbaScript to see what happened. I used this command:

Get-DbaTable -SqlInstance Plato\SQL2016 -Database NBA | Export-DbaScript

This gave me some output. It wasn’t quite what I wanted, but it worked.

Note that all of the tables were exported to the same file. If I opened that file, here’s a snippet of the code. I get a header, and then each table’s code put together as single batch.

Not quite what I wanted.

I can determine the file, using the –Path parameter. This still gets me one file, but I can make this better. I’ll make a folder and change to that folder in my PoSh window. Now I can run this, using ForEach-Object to iterate over the tables, outputting each to a file.

Get-DbaTable -SqlInstance Plato\SQL2016 -Database NBA | ForEach-Object { Export-DbaScript -InputObject $_ -Path ($_.Name + “.sql”) }

When I do that, I get this:

Each table’s code is now in a separate file.

That’s nice, and with filtering, I could have this generate scripts for specific objects, or types of objects and update them from PoSh, likely a job or task, relatively easily.

Certainly the Generate Scripts process in SSMS is easier than writing this script, but that’s not programmable. This wraps around the SMO scripting objects and makes the process much easier.

If you haven’t tried dbatools, give it a go. You’ll appreciate the ease of scripting and might start to enjoy working with SQL Server in PoSh.

March 23, 2018
I learned about the order of logical operations #SQLNewBlogger
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

I had logic in a CS curriculum many years ago and I’ve worked with AND and OR statements for years. I’ve sometimes confused myself, but I usually ensure I have parenthesis included to clarify the code. Not just for me, but for anyone that might glance at the code later.

As a side note, I also try to format code so a quick glance can reveal what happens.

However, I learned something new this week. I saw a question about the order of logical operations in this form: a or b and c.

I had somewhat assumed, like math, we’d use a left to right evaluation. However, that’s not correct. Look at this snippet:

If we went left to write, we’d have two rows from the OR (n=1, n=2) and then an AND that produces no rows. So no results?

That’s not correct. According to BOL for OR, the AND operations occur first. So n=2 AND n > 3 occurs, with 0 rows. Then the OR with n=1 is evaluated to return 1 row.

Fascinating.

At least to me. I’ve never thought because I’d write
```
WHERE (n = 1 OR n = 2) AND n > 3
```
or
```
WHERE n = 1 OR (n = 2 AND n > 3)
```
and be sure that what I wanted to occur would occur.

A quick lesson. While it’s good to know what the order or evaluation is for your platform, don’t count on this. If there is a chance for confusion or unintended consequences, use parenthesis. It’s simpler and easier, and I might argue, more elegant.
March 22, 2018