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.

IMG_1363

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.

IMG_1378

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.

IMG_1367

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

IMG_1370

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

IMG_1384

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.

IMG_1369

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.

Posted in Blog | Tagged , , | Leave a comment

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.

2017-03-22 10_32_00-SQLQuery1.sql - (local)_SQL2016.NBA (PLATO_Steve (102))_ - Microsoft 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.

2017-03-22 10_46_16-SQLQuery1.sql - (local)_SQL2016.NBA (PLATO_Steve (102))_ - Microsoft SQL Server

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.

2017-03-22 10_47_25-SQLQuery1.sql - (local)_SQL2016.NBA (PLATO_Steve (102))_ - Microsoft SQL Server

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:

2017-03-22 10_48_30-SQLQuery1.sql - (local)_SQL2016.NBA (PLATO_Steve (102))_ - Microsoft SQL Server

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.

Posted in Blog | Tagged , , | Leave a comment

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.

Posted in Editorial | Tagged | Leave a comment

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.

Posted in Editorial | Tagged | 1 Comment

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.

2017-03-23 14_09_25-Sign in to your Microsoft account

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.

2017-03-23 14_11_36-cmd - powershell

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.

2017-03-23 14_13_22-Getting a VHD into Azure - Open Live Writer

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

2017-03-23 14_12_26-All resources - Microsoft Azure

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

2017-03-23 14_14_17-My permissions - Microsoft Azure

Or not

2017-03-23 14_14_41-cmd - powershell

Get-AzureSubscription doesn’t quite work.

2017-03-23 14_17_54-cmd - powershell

It’s Get-AzureRMSubscription

2017-03-23 14_19_02-cmd - powershell

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.

2017-03-23 14_22_02-cmd - powershell

I retreat.

2017-03-23 14_23_59-cmd - powershell

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.

2017-03-23 14_30_22-cmd - powershell

#$%#@#@# 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.

2017-03-23 14_31_27-cmd - powershell

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.

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

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 .

2017-03-11 21_01_53-Web   Mobile - Microsoft Azure

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

2017-03-11 21_02_15-Untitled

Once you select WordPress, you just click Create.

2017-03-11 21_02_56-WordPress - Microsoft Azure

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

2017-03-11 21_03_47-WordPress - Microsoft Azure

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.

2017-03-11 21_07_42-tsqltuesday - Microsoft Azure

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.

2017-03-11 21_09_56-WordPress › Installation

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’

Posted in Blog | Tagged , , | 1 Comment

Change Approvals

Part of a developer’s or DBA’s career is getting their work released to some system where others can use the system. Some of us may do this more than others, but we all usually have some role, whether in packaging changes up or actually pushing the button that runs a script or copies files to some live server. This can be an exciting and stressful time, depending on how you feel about the quality of the work.

Releasing software often isn’t something done in a vacuum. Even in the highly agile, DevOps companies like Amazon and Facebook where developers release code many times a day, there is often some sort of approval process, whether implicit or explicit, before code goes out. Even if it’s just a peer that code reviews something, or a business person that examines a test version and pronounces it correct, another person often weighs in on our changes.

That’s not always the case, as I know in emergencies, some of us make quick decisions and change or run code that only we have examined. I’d hope that’s the exception, rather than the rule, with most database changes.

Today I’m curious. Who approves your database changes? Is there a formal process? An informal one? Does the person making the decision even understand the code or do they depend on you to have written and tested solid T-SQL?

It’s been said that the person closest to the work is often the best person to judge if it should be released, but that’s only partially true. Deploying code is often disruptive. It introduces change, which customers may or may not like. There may be good reasons to release at discrete intervals, rather than whenever the developer things things are working. This may change with heavy use of feature flags or feature toggles, but in general, code releases are interruptions and we want to limit them.

Unless something is broken, in which case, we often want the change as quick as it can be released. Does that mean we want a different change process? Let us know today.

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

I Hate To Send This Email

I use TrueDelta to report my car status every month. It’s a nice service, allowing car owners to see what experiences others have, and think about which models and years might be a good fit for me. I’ve tracked multiple cars with their service for the last 3-4 years, with reminders from them every quarter to update any repairs I’ve made.

Recently I got an email from them with this opening: ” We hoped never to have to send this email. A few days ago we learned that TrueDelta has joined the increasingly long list of organizations whose server security has been breached by hackers.” The email went on to note that names and passwords to taken, and that everyone needed to perform a password reset. I applaud them for including “Security breached” in the subject as well as immediately changing everyone’s passwords so old ones wouldn’t work.

I’ve been hacked at SQLServerCentral, though to our knowledge no data was stolen, merely vandalized. We haven’t ever been able to track suspected data breaches back to SSC, and I hope we never do, but I’m not naive to think that we never will. I hope we don’t, but hackers make determined efforts to gain access to data. At least we are aware of security measures, have a small staff with administrative access, and try to not allow any simple attack vectors.

Not every company does a great job at securing their data, especially from phishing attacks. There’s a spectrum of how carefully data is protected by organizations, and as we’ve seen from haveibeenpwned.com and plenty of media reports, more and more companies lose data all the time. Some of those companies notify customers (some have to), and I would guess more than a few people have had to send out emails they never expected to send. More of us will dsend those emails in the future, and we should think about that today. Is there something we can do to avoid having to send those notifications?

There probably isn’t something to ensure it never happens, but we can certainly work towards improving our security. As developers, we shouldn’t have short limits or character choices for passwords. If you wonder why, there’s a great answer at security.stackexchange. We shouldn’t be writing our own authentication schemes, but incorporating code that’s been written, vetted, and reviewed. And make sure we apply patches. Most of the security holes in software are known and patched, but without being deployed. Certainly if new patches become available, we should be able to incorporate them quickly. Above all, learn what SQL Injection is and don’t allow unvetted user input in queries, including those in hidden form fields.

Steve Jones

The Voice of the DBA Podcast

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

Posted in Editorial | Tagged , | Leave a comment

Delete an Azure SQL Database from PowerShell

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

One of the things I’ve been working on is trying to get my DevOps, continuous delivery pipeline working with Azure. Part of that is a test deployment to an Azure SQL Database, which means I need to be able to update an existing database from a backup. Unfortunately, Azure SQL Database doesn’t support a restore over an existing database (yet).

That means one task I have is to remove an existing database, in order to replace it with a new database with the same name. A little work in the PoSh documentation found Remove-AzureRMSQLDatabase, which is just what I need.

To use this cmdlet, I need a connection to the Azure space first. I can do that with a credential that I get with this command. Ultimately I need to store this, but interactively this lets me get started:

Login-AzureRMAccount

This gets me an interactive login. I enter my account and password. Since I use a Live account, this won’t work in the pipeline, but it gets me going.

2017-03-10 09_00_34-Sign in to your account

From here, I can set a few variables I’ll need. I want the name of a resource group, a server, and a database. In my case, I’ll use a few variables. I call the database the “new” one, since I’ll be using an existing one that I’ll recreate from an “old” one.

2017-03-10 09_03_07-powershell - How to Login without prompt_ - Stack Overflow

From here, it’s just a question of calling the Remove-AzureRmSqlDatabase cmdlet with parameters. I do that, and get results. Here’s the call

Remove-AzureRmSqlDatabase -ServerName $server -ResourceGroupName $rgname -DatabaseName $newname

Here are the results. I’ve blacked out a few ids.

2017-03-10 09_05_16-Photos

This clears the database, and after refreshing, I can see it’s gone from my list of Azure SQL Databases.

2017-03-10 09_08_12-SQL databases - Microsoft Azure

Not much to this, but it’s part of a larger scheme, which is getting a copy of the production database and restoring it.

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

Launching Products

Over the years I’ve seen a lot of products launched. I still remember the fanfare around Windows 95, and the efforts to make this an event of sorts, back when virtual events didn’t make sense. There have been a few Windows or SQL Server launches, some of which took place at large conferences, but quite a few were launched in different communities with small physical events. In Denver, there were a few times when Microsoft rented a hotel conference space and scheduled speakers, some from Microsoft, and some local. These would be all day events, showcasing new features, with customers talking about their experiences in previewing the product.

Just a few weeks ago, Microsoft launched Visual Studio 2017, with some fanfare, and two days of virtual events. I watched one, and hope to find some time to watch a few of the second day’s events. While I don’t necessarily learn a lot, I go get a bit excited seeing new technology and how different people use it. It’s inspiring to see demos and solutions work well on stage. I think it’s a welcome respite from the drudgery of dealing with my own or other people’s code, and the various problems it presents, as well as the various bugs and limitations of the tools and IDEs I use. It seems there’s always some devil in the details that slows me down.

However, for a short time, I get rejuvenated and excited by the new features. I want to dig in and experiment. It can be a struggle to find time, but watching someone demo a new way of working reminds me of the things I love about computers and technology. I think this is one of the reasons I love attending SQL Saturdays or other conferences and watching other presenters talk. I always see some new and interesting things that generate ideas and excitement. I’m really looking forward to SQL Bits in a few weeks (still time to register), and a few days of seeing what others have been accomplishing with SQL Server.

I also have pride when I’m a part of the company launching a product. At Redgate, we’ve had a number of SQL in the City shows, which I’ve been honored to be a part of in many cities around the US and UK. Last year we tried a streamed version, which was yet another experience, but one I hope we repeat again. And, in fact, I get to be a part of another product launch next week. We’ve already release SQL Clone, but we have a live-streaming launch coming next week. I’ll be traveling over to Cambridge and getting ready for a few presentations that will hopefully inspire you to try the product. If nothing else, check out the stream and see if we show anything that sparks an idea in your mind.

I know many of us work inside companies, and our product launches are small, to relatively few customers that are often our fellow employees. Some of you work with companies and the results of your work are visible to lots of others. No matter which environment you work in, I hope you feel the same pride and excitement I do when you release a new idea for your users to enjoy.

Steve Jones

The Voice of the DBA Podcast

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

Posted in Editorial | Tagged , , | Leave a comment