Voice of the DBA

Author: way0utwest

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
Big Companies are Improving with DevOps

One of the comments I’ve often heard from people that work in IT and haven’t adopted DevOps is that the principles and changes required won’t work at their organization. Quite a few people think that only small, new companies, like Flickr and Spotify can use the ideas. Plenty of others look at only high-tech, progressive companies like Amazon and Facebook able to change.

That’s not true.

In fact, there are four Fortune 500 companies using DevOps, including a large (though young) bank, Capital One, and another, older one, WestPac. I’m not sure anyone would consider American Airlines or Hertz to be small, agile companies, though certainly they are in highly competitive industries and need every advantage over their competitors that they can get. I suspect that is the driving reason for many of these companies to adopt fast, quick software development. They can’t afford to have an idea take months to implement.

Those companies aren’t like yours? What about Maersk or Nationwide? Ticketmaster? Maybe Norstrom (and a few more)? I actually had the chance to speak with a number of Nordstrom employees that had taken a POC concept for the mobile group and proved that DevOps has value. From there, almost the entire IT department, hundreds of employees in groups from internal IT to mainframe to web, all have adopted various types of DevOps processes, starting with value stream analysis. Over a few yeasr, they have dramatically transformed their delivery of software. When someone in the business proposes an idea or need, it used to take over 6 months for something to get deployed. That’s down to a couple of weeks, and it’s released in a true, get-something-useful-to-the-customer fashion. This isn’t alpha or beta software, but a basic item that can be used and is then grown and changed according to customer feedback on a daily or weekly basis.

The transition to DevOps really requires some belief and understanding of the ways in which you can deliver better software, faster. This requires some slow growth, which seems crazy, but the the cultural changes take time, and even the technology tools you choose, require some patience, trust, and experimentation from your technology staff. While it might take months or even a year to get a DevOps process working well and one you’re comfortable with, the gains grow and grow over time.

Even if you don’t believe in DevOps now, why wouldn’t you try to get someone in management to set up a proof-of-concept and build something. It’s a small investment, that could have huge payback with limited risk. You’ll learn a lot and can then decide if it helps you delivery value to your customers in a better way. And if you do adopt DevOps, don’t forget to include the database in your process.

Steve Jones

The Voice of the DBA Podcast

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

March 29, 2017
Hacked
Hacked

By Steve Jones, 2013/01/02
This editorial was originally published on Jan 2, 2013. It is being rerun as Steve is out of the office.

I’ve been hacked before. My personal web site has been hacked with a variety of injection and XSS attacks over the years. None too serious, and I’ve had backups that allowed me to fix things fairly easily, especially once I had a copy of Data Compare, which saved me a lot of time. At SQLServerCentral, we’ve been hacked as well, though not in a long time. I think we’ve closed most of the security holes, and I haven’t had any issues to deal with in quite some time.

However as I was reading a note from Richard Douglas about being hacked, it brought back memories of working at JD Edwards. Richard was hacked at work, on his personal system. At JD Edwards, we were required to lock our workstations at all times when we were not physically in front of them. We also had two accounts: a normal user and a domain admin “privileged” user. As you might expect, there were numerous lapses of people walking to the kitchen or bathroom and forgetting to lock their workstations. It was considered fair game to change settings, send email to our group, even place semi-SFW pictures on someone’s desktop. It was quite embarrassing to be caught, and was much more a an effective security reminder than a reprimand from our boss.

However there is a serious security problem here. Many of us would use our privileged account all too often, since it was a hassle to log out and back in. The “run as” option didn’t work well for some applications, and we were less secure than we probably should have been. If someone walking by, whether an employee, guest, consultant, or someone else noticed SSMS running, how long would it take them to type:
```
  sp_addlogin 'joeuser', 'joeuser'
  sp_addrole 'joeuser', sysadmin
```
I type quickly and that took me less than 30 seconds. I’m sure even a slow typist could get that entered, and erased, inside of a minute. That might result in a serious security breech, if the system to which you were connected contained HIPAA, PCI, or any identity information. Perhaps even worse these days is the chance someone might attach a USB key logger to your keyboard.

You might be safe in your environment, but you can never be sure. A little care in ensuring you are not unnecessarily exposing security holes, and making sure that outsiders are always escorted can prevent embarrassing incidents from occurring.
March 28, 2017