Voice of the DBA

Category: Blog

Looking Back at the SQL Clone Launch(es)

Yesterday was the SQL Clone launch livestream from the Redgate office in Cambridge, UK. I flew over on Monday, along with Grant Fritchey, to help broadcast a few sessions about the product. I’ve been excited to see SQL Clone for a few years now, ever since I saw a POC in 2015.

Having only one day to prep was tough, but family commitments meant I couldn’t arrive until Tuesday morning. A long night on a flight across the water, but I made it and landed in time to get to Cambridge before lunch.

The event was actually a three-peat, broadcasting the same sessions three times to cover various time zones around the world. A long day, arriving at the office around 6:30am and not leaving until after 8pm. It’s not that bad, as my sessions were spread throughout the day.

We had some fun as well getting ready. Grant and I did a short commercial, which made me chuckle when I saw it.

The sessions were based on some demos and testing we’ve done over time, and I think they showed some of the power of the SQL Clone product. We’ve got some intro videos, one of which is below, to help you understand how the product works. We’ve also been publishing lots of short pieces on specific SQL Clone use cases on the Redgate blog.

The behind the scenes was fun, with prep and rehearsals occupying a very long day for me on Tuesday. However, after our SQL in the City Streamed last year, everyone involved at Redgate has had some practice in how to setup and run a live event.

There’s a dedicated double conference room that we’ve turned into a broadcast studio, with lots of equipment and a small set. I’m especially glad we had four microphones for all the presenters. Switching mics with limited time is a challenge.

Last year we used a podium, but I’ve found that trying to work with demos on a small platform is hard. In various conferences around the world, I’ve found there isn’t enough space to easily type or maneuver a mouse in the typical space that a speaking podium offers. I asked for a desk, and sure enough, our engineers found one, raising it up to accommodate a standing height.

SQL Clone is sheep-themed, after the scientific work with Dolly the sheep. Our product marketing manager, Karis, was a good sport, donning a costume for a short promo video. I grabbed a short video and a few pix.

SQL Clone short promo segment

Early Wednesday, we started the event, to a small audience in Asia and a few early birds in Europe.

The flow is similar to a television broadcast. Our engineer will give us a few countdowns (30s, 10s), and an audible 5, 4, 3, then a thumbs up to start presenting. It’s slightly odd to present to a screen, and it does take some getting used to remembering the camera and not the people in the room are the ones that need your focus.

We also have multiple clocks and timers on various screens. Below you can see the view from the table. There is a monitor in the upper left that shows what the laptop is outputting. Since some people like PowerPoint’s presenter mode, we have to switch between extend and duplicate on the laptop. Seeing the output in front of you is handy.

To the lower right we have a monitor that has a countdown timer for this particular session. This is handy in keeping an eye on how much time is remaining in the presentation. I wish there were a scheduled end time as sometimes our start times are off, but I try to keep an eye on the start time and guess how much over I might go. In our case, the intro and Grant’s piece were short, so I could go over a bit. The one thing that would be nice is a negative count up once we hit zero. Right now the timer stops.

At the rear of the room we have a large clock on one screen that shows the local time. Since I know roughly when I’m supposed to start and end, that’s helps. On the upper right screen, showing the Windows background we put up a large Word document where someone can type questions from Twitter/YouTube/Slack, etc. They use a large font so we can read them from the stage area.

We have a series of buffer videos, ads, and transitions that are setup between live talks. Watching the engineers switch between audio and video, moving between different inputs is fascinating. Mechanical, and perhaps not so exciting if it were happening all day, but for a a few events a year, it’s neat. I’m looking forward to seeing how we can improve the process in the future.

The Event

The day started with Grant talking about the challenges of database provisioning. I’ve dealt with all he noted, regulatory issues, space, time, repeated work, etc. Until I started working with the SQL Clone team, I hadn’t realized quite how much of a hassle the copying and provisioning of databases can be. I’ve fought with storage admins for space, and with developers over the time it takes to copy and restore. SQL Clone makes this easier, and if you want to move faster, then you might find the space and time savings worth the cost of the product.

Or you might just keep dealing with the time and space issues. Your choice, and there are plenty of companies that are happy to spend your time and effort on copy/restore tasks.

Once Grant finished, it was my turn to show how SQL Clone solves some of these issues. I got to demo the GUI, self-service implementation and then discuss the code solutions with Richard McCaskill, our product manager. Hopefully I didn’t distract Grant, but it’s a bit boring to watch the same presentation 3 times when you aren’t involved and are just sitting in the room.

The PowerShell cmdlets for SQL Clone are the real power of this tool, allowing you to easily provision new images and clones for developers. I’ll have to write more on this, but we demoed a similar setup to one of our customers where they create a new set of databases on developer instances for new branches. That is very cool.

We had growing audiences throughout the day as we moved across the globe. If you watched, let us know if the time and length worked for you, as well as where you are. I think we’ll do more of these, and with the number of products that Redgate has, I bet we end up with some sort of broadcast every couple months.

March 30, 2017
Watch Your DataTypes in Aggregates–#SQLNewBlogger

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

I’ve got a database of NBA statistics with data like this for players. I downloaded a CSV and loaded it into SQL Server.

I decided to play with the data a bit and at one point wanted to see who scored the most points for a team and year. So I ran this query:

SELECT
    year,
    team,
    MAX(pts)
FROM dbo.player_regular_season
WHERE
    year = ‘1972’
    AND team = ‘LAL’
GROUP BY
    year,
    team;

The result was 705. That’s a decent number of points, and if I weren’t careful, this might seem fine. 1972 was a long time ago, and they didn’t score as many points as they do today in games.

In fact, if I were putting this in a summary report with lots of data, it might be the case that someone glancing at this would make a poor decision based on the data.

Why?

Let’s look at the data.

Even a quick glance would let me know this seems funny. There are values of 1575 and 1084 in there, but the MAX() I returned was 705. If I look deeper at the import, I can see why.

Anything stand out there? If you look, pts is a varchar, not a numerical value. In the character world, 705 beats 1575. I really need this query:

Always be aware of the datatypes you work with and manipulate. Knowing a little bit about the meaning and use of the data can help you spot anomalies like this. As much as I like random test data, I’d also be sure you have some real data cases when you have users check your work. It’s easy for them to miss problems like this without good reference cases.

Or use good test data that you’ve setup and unit tests.

March 29, 2017
Getting a VHD into Azure with PoSh
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

I thought this would make a nice SQLNewBlogger post, an easy get started one. I used the docs from Microsoft as a guide, so here’s how it went.

Login to my account. I used Add-AzureRMAccount here to get the login dialog. I’m not repeating this for now, so this is just an interactive test.

Now I tried to create a storage account, but I hadn’t assigned a subscription. I didn’t think I needed to since I only have one,but hey, I guess I do.

I started typing Select-AzureSubscription, but realized I didn’t know what it was. I can’t remember naming this and under my name in the portal, I didn’t see a place to find it.

Ah, under All Resources I see something, so I’ll try this.

On second thought, under My Permissions, I see it there.

Or not

Get-AzureSubscription doesn’t quite work.

It’s Get-AzureRMSubscription

So, why doesn’t my creation of the storage account work? I should really read error messages. The issue is Select-AzureSubscription fails because I need Select-AzureRMSubscription.

Sometimes the rev’ing of the cloud is hard.

Actually, maybe neither work.

I retreat.

I’m still annoyed. “US West” isn’t valid. FFS, Microsoft. Whether I type any of these, just ask me or process them.
- “US West”
- “West US”
- “USWest”
- “US_West”
OK, I decided to go exercise for a bit.

I’m back (30 minutes later), and I try Get-AzureLocation. I can guess some cmdlets. I get a lot of stuff back and see “West US” is valid. I try a few other valid names of regions, but get errors. It’s not that parameter.

Let’s try a new name.

#$%#@#@# engineers and developers. That’s a valid error message? Not, this is a duplicate or something that makes sense. Glad I left.

Maybe not. At least this is an error that makes sense.

Finally. Let’s upload the VHD.

$2017-03-23 14_33_25-{0%} cmd - powershell$

And it’s off.

$2017-03-23 14_33_41-{0%} cmd - powershell$

Hopefully this will work. I haven’t tried this with PoSh before, and it was somewhat frustrating, though it really didn’t take that long.

SQLNewBlogger

This was a bit of a live blog. Do something, take a screenshot, write some text. It was a bit of a learning experience.
March 27, 2017
TSQLTuesday.com – Quick WordPress Setup in Azure

When I agreed to host the T-SQL Tuesday site, I wasn’t sure what to do. I thought about just putting up a static page, one I’d edit, but in some conversations with Adam Machanic, he wanted to have a few people able to manage the site. That makes sense and means I have less to do.

My first thought had been to build a SQL database, set up a web page, maybe add some logins for people, etc. Sounds great, but that can be a lot of work, and while I don’t mind some projects, I don’t want a big project that’s just a small service. Especially for data that’s really just historical logging.

When I woke up one morning and found a domain transfer request, I felt some pressure to get something up quickly. One of the great things about using domains on the web is that I could build something quick, point the domain to it, and build something else in the background, moving the domain later. There might be redirect issues, but in this case I expect most people just hit the domain, tsqltuesday.com, so I decided to focus there.

Azure Websites

I had experimented a bit with Azure websites, setting a few up, and thought this might be easy. In fact, it was really easy, so I decided to write a bit about the experience. Note, this is what the experience is like in Mar 2017. It could change quarter to quarter, so use the process and images as a rough guide.

To get started, log into your portal. If you don’t have an Azure account, google how to create one, set it up, and then come back.

Once you have an account, click New and choose .

From here, pick “See All” and then find WordPress.

Once you select WordPress, you just click Create.

I tried to keep this simple, and picked these options.

one thing I had to do was pick a size above the free service. This is because the free services don’t allow custom domains, and while tsqltuesday.azurewebsites.net works, it’s not as cool at tsqltuesday.com. Plus Brent had already transferred the domain to me, so I was on the hook.

Once this was done, I had the site created.

Once the deployment completes ,you have a blank WordPress site. You log in with your credentials and start configuring, just as you’d do on wordpress.com or with your own installations.

I took a few guesses on how to organize the site, but I’ll discuss those late. This is really about getting a quick site up, one for your business, for your blog, or whatever you’d like. This is my second WordPress site on Azure, and I’m amazed at how easy it is.

I’

March 24, 2017