Interview Questions for a SQL Server DBA

When I’m interviewing a SQL Server DBA, I have three questions that I always ask.

My favourite interview question is this:

“What is the difference between a clustered and a non-clustered index?”

My second favourite question is this:

“What is the right disaster recovery strategy for my company?”

The first question is one of basic understanding. Depending on their work experience, the candidate may have an inkling about why such a distinction matters, and this lets me know how much I have to teach. What it does not do is disqualify them. I am not averse to teaching, which is why I write on this website in the first place.

The second question should be answered with “it depends”, followed by the candidate asking me questions about my company so that they can figure out a way to begin answering it. If the candidate blurts out an answer without asking questions, well, their résumé goes in the garbage. This is the disqualification question.

I’m mostly self-taught, with formal training to fill in the gaps much later in my career, so I know the difference between “I know this thing, but not the textbook definition”, and “I read this somewhere on Google and have never used it”.

If the candidate uses words that are blatantly wrong but have the right idea (“the blob is sorted on the disk, but the non-blob one is a copy of the blob, but smaller, because you can only have one blob, and it’s shaped like a Christmas tree”), then that’s a pass for me. SQL Server doesn’t care how you describe an index to an interviewer, as long as the syntax is correct, and that’s what Books Online is for.

Heck, I might even say “if you can’t explain it with words, draw it for me on the board”. I love pictures. I’ve drawn many lopsided databases in my time. That’s what the dry-erase board is for: working through a problem that exists in the real world. It’s not there to make someone regurgitate a textbook definition. Again, that’s what Books Online is for.

Here’s a practical example from my school days, as a student and as a teacher: the notorious open-book exam.

In this scenario, the exam taker is allowed to refer freely to their textbook throughout the exam, and the invigilator is there to make sure no one is distracting their fellow students.

The open book exam relies on the exam takers having a working knowledge of their subject. In the case of English literature, for example, it helps to have read the book. Summarised study guides only get you so far — if I’m asking why the love between Catherine and Heathcliff can be described as “demonic”, providing examples from the text, you would have to know where to look.

Even so, the open book exam is unfair for those people who haven’t read the book but have seen the movie ten times and can quote their favourite lines from it. Or if they prefer the audio book, because they struggle to read words on a page, especially under stressful situations. Or their parents read the story to them many times in their youth. Or English isn’t their first language. Or their second language.

If you have haven’t read the book, you will run out of time to answer the questions, because you’ll be frantically reading the textbook for the first time, trying to understand why anyone wants to know what a linked list is for (a very typical interview question for software developers). Meanwhile, you’ve used linked lists before in that job you had writing code for your cousin’s bicycle store, but you didn’t realise that was their actual name.

If I’m asking you, with only a few years of experience administering an Access database for your uncle’s grocery store, to create an index in SQL Server, without using the graphical interface of Management Studio, the first place you’re going to look is Books Online.

As my friend Gail Shaw (b | t) once said many years ago, when you are working on a SQL Server database, you should have two windows open, and one of them is Books Online. Why guess arcane syntax when you can simply look it up?

Which brings me to my third favourite question:

“What was the last mistake you made, and how did you recover from it?”

I’ll go first.

This morning I was working on a stored procedure for a new system. The code called for an upsert-style stored procedure (INSERT and UPDATE in one block of code, to check for the existence of a row of data in order to update it, or insert it if it doesn’t exist). My UPDATE statement was missing a WHERE clause.

Fortunately, the tools that I have at my disposal managed to catch this before the code was ever run. My point is, even with decades of experience, and being able to recite all of the keywords in an UPDATE statement, even the most battle-tested person forgets a WHERE clause once in a while and updates or deletes the entire table.

This is why I love my second favourite interview question, because I get to ask the candidate this bonus question:

“Does the disaster recovery strategy you came up with in question 2 cater for the scenario you just described?”

After all, backups matter only if you can restore them promptly and accurately.

Final Thought

This discussion has made a resurgence thanks to my friend Janie Clayton, who has posited (in my interpretation anyway) that technical interviews are designed to exclude certain people from applying for a position, for arbitrary reasons (university degree, culture, gender, language, etc.), for the sake of some puritanical ideal.

My take is obvious: If you have working knowledge that you can demonstrate, and some experience, you can learn the rest on the job. Every system is different, with different goals guiding a decision. It takes time to become accustomed to a new system, and even the smartest person in the room will have to ask questions.

The question is not “what have you memorised?”, but “what can you learn?”.

By all means, if you’re applying for a specialised role (for instance, performance tuning), then you need to have specialised domain knowledge. For everything else*, there’s Books Online.

* MasterCard did not pay for this post.

Should I upgrade SQL Server 2008 / R2, or migrate to Azure?

Brent Ozar Unlimited runs a website, called SQL Server Updates, which comes in really handy for keeping your on-premises SQL Server up to date.

Last week I noticed something interesting: if you’re running SQL Server 2008 or 2008 R2, Microsoft’s extended support for it ends on 9 July 2019. That’s two versions of SQL Server, almost two years apart in release cycle, with different feature sets, expiring on the same day.

Granted, by that date SQL Server 2018 will probably have been released, which would make 2008 officially five versions old and 2008 R2 four versions old. But in corporations, time moves both slowly and quickly. By that I mean, planning to upgrade takes a long time, and before you know it, it’s 9 July 2019 and everyone is panicking.

It’s the right time, today, to start your upgrade plan. A lot of customers I speak to, running on 2008 or 2008 R2, are talking about upgrading to 2012 or 2014 right now.

On Twitter, I said that after the end of next year, I would not recommend upgrading any instances to SQL Server 2012, for this reason:

In 2019, moving to SQL Server 2016 buys you 7 more years of support. I wouldn’t consider moving to SQL Server 2012 after the end of 2017.

At the end of 2017, SQL Server 2012 will be more than five years old. Despite your best intentions assuming a major upgrade will be quick, and considering that SQL Server licensing costs the same regardless of the version you buy, there’s no practical reason not to go to at least SQL Server 2014, and preferably SQL Server 2016 (which I consider the biggest release since SQL Server 2005). You can always run your database in 2008 Compatibility Mode if you’re running SQL Server 2014.

I wrote down some reasons to upgrade from SQL Server 2005 last year. These reasons still apply.

Whither Azure?

Which brings up the big question: what about Azure SQL Database? As you know, my first question when customers ask about moving to Azure SQL Database is “Are you sure?”, followed by “Are you very sure?”.

By the time July 2019 rolls around, Azure will be ten years old. That’s hard to fathom right now, but at their current rate of progress, Azure SQL Database will be a mature platform (it already is mature for appropriate workloads, when taking elastic pools into account).

So, if you meet all of the following conditions:

  • you use SQL Server 2008 or 2008 R2,
  • you plan to upgrade after 2017, and
  • you’re running a database smaller than 1TB,

I would recommend that you seriously consider migrating to Azure SQL Database, instead of upgrading to an on-premises database server running SQL Server. The time is right, at the end of 2016, to spend 18 months on an appropriate migration strategy to achieve this.

Yes, 18 months from now we’ll be deep into 2018. Scary, isn’t it?

Act now.

If you want to discuss this on Twitter, look me up at @bornsql.

What to do at PASS Summit 2016

Next week, while blog posts are scheduled as expected, I will be attending my third PASS Summit.

Summit 2014

In my first year, I attended every single event I could.

The Monday night started with Steve Jones’ regular Networking Dinner. When I arrived, there were already over a hundred people there, all of whom I didn’t recognise. Until I saw Ed. Ed Watson and I had met in Tampa, FL, at a SQLskills Immersion Event in 2012 and stayed in touch on Twitter. I met new people that night through Ed and reconnected with the folks I’d met at the five Immersion Events I’d attended in 2012 and 2013. The Summit hadn’t even started, and I was already seeing its benefits.

On Tuesday was the First-Timers’ Orientation Meeting & Speed Networking event, where I met some folks with whom I reconnected repeatedly during the week. There may be thousands of attendees every year, but humans recognise familiar faces in a crowd, and we didn’t need to feel overwhelmed.

Later on Tuesday night was Denny Cherry’s long-running SQL Karaoke evening, where I met Argenis Fernandez for the first time in the flesh, and sang a duet with Ed Watson (Sonny & Cher’s I Got You Babe).

There were many vendor-sponsored networking events as well, where we received free food and beverages. I attended several of those.

I also attended both keynotes (hint: the first keynote on the Wednesday is marketing, so you can sleep in – but take note, Brent Ozar thinks it might be worthwhile attending this in 2016. The second one, on the Thursday, is worth attending), and I can say that Rimma Nehme is amazing.

I attended a lot of sessions. Many of them were incredible. I had my brain melted by Bob Ward’s talk, “Inside SQL Server I/O”. If you get a chance to view this online, do so.

I hung out a lot with another friend, Larry Toothman, whom I’d met in Tampa at the same SQLskills Immersion Event where I’d met Ed. Sadly, Larry died in 2015, but the SQL Server community was incredible with showing support to his husband, and keeping Larry’s memory alive by sharing stories and handing out ribbons.

The main takeaway I had was that I had to return in 2015. The networking aspect alone made it worthwhile. The learning was the cherry on top.

Summit 2015

In 2015, I took a calmer approach. While I still attended a lot of technical sessions, I went to two pre-cons (pre-conference events, which are run separately and cost extra). One was PASS-sponsored, and I got to learn a lot from Aaron Bertrand in his “50 Things All SQL Server Developers Need to Know” full day session.

My other pre-con was unpaid, and unsanctioned by PASS. I attended the Brent Ozar Unlimited “FreeCon” event, with 49 of my closest friends, to learn the things you need to know to market yourself better. That’s the reason I now write at least one blog post a week and have made a bigger effort to sell my skills as a consultant.

I sang another duet with Ed, at the new SQL Karaoke venue (Summer Nights from Grease) and had an impromptu group of ladies helping me sing Sandy’s part.

While I did attend both keynotes again, I decided I would skip the next year’s Day 1 Keynote, because it’s pure marketing. In the second keynote, Rimma Nehme was back again, incredible as ever, with David DeWitt to assist her.

I also did not attend every session I’d planned, because the rumours were true: I was gaining more knowledge by networking with people. I met some new people, including Ewald Cress, a guy I went to school with in a rural town in the middle of nowhere.

Bob Ward melted my brain again, but this time his talk was easier for me to understand. I sat next to Gail Shaw, a fellow South African I know from the old country, and we agreed later that the 2014 talk was a lot meltier.

I met Steve Stedman in the flesh, after taking part in a Database Corruption Challenge he had run earlier in the year. As it turned out, Steve and I did some business together in 2016 as a direct result of this networking. It works, folks.

On Friday evening, David Klee invited a group of us to try again at karting. In 2014, due to bad planning with taxis, we missed out, but ended up crashing a birthday party of my favourite Australian, Rob Farley. Rob and I got to know each other during Steve Stedman’s corruption challenge too.

2015 was more successful at karting too, because I won the final race with David coming in a very close second place.

Summit 2016?

More networking. More karaoke. Because I’ll only be arriving on Tuesday this year, I will miss some of the unsanctioned festivities, but rest assured I’ll catch up with everyone again. I also plan to have my brain melted by Bob Ward. Alas, his talk is the same time as Gail Shaw’s, so I won’t be able to heckle her.

Advice for newcomers

If this is your first year, definitely do the orientation first, and check out the sessions that interest you.

On the other hand, don’t forget to introduce yourself to new people and talk to them about SQL Server. If you are on Twitter, and follow any of us in the SQL family on Twitter, come and say hi. (Heck, even Grant Fritchey, Executive Vice President of PASS, agrees.)

I’ll be wearing my trademark black EAT SLEEP RAVE REPEAT CREATE READ UPDATE DELETE sweater (don’t worry: I have three of them, and they get washed). Since I have terrible concentration, eyesight, hearing, etc., I’m usually sitting right at the front of each session I attend, so you can’t miss me.

Speaking of Twitter, come find me on @bornsql and let me know if you’ll be at Summit 2016. Come to Denny’s karaoke evening.

The golden rule applies to Summit, as it does in life. If you’re respectful to fellow attendees, the favour is returned, and you’ll become lifelong friends with lots of folks, who will be happy to help you out in a bind.

Temporal Tables in Azure SQL Database

In the latest Microsoft Azure newsletter I received last week was this most excellent news:

Azure SQL Database Temporal Tables generally available

Temporal Tables let customers track the full history of data changes in Azure SQL Database without custom coding. Customers can focus data analysis on a specific point in time and use a declarative cleanup policy to control retention of historical data. Designed to improve productivity when customers develop applications, Temporal Tables can help:

– Support data auditing in applications.
– Analyze trends or detect anomalies over time.
– Implement slowly changing dimension patterns.
– Perform fine-grained row repairs in cases of accidental data errors made by humans or applications.

For more information on how to integrate Temporal Tables in an application, please visit the Getting Started with Temporal Tables in Azure SQL Database documentation webpage. To use temporal retention, please visit the Manage temporal history with retention policy documentation webpage.

Long-time readers of my blog will remember my short series about Temporal Tables in SQL Server 2016. Now it’s time to play with them on Azure SQL Database too!

Changes to Table Schema

With Azure SQL Database, just like SQL Server 2016, you can change the table schema without breaking the link to the history table. From the above Getting Started link, it states that you perform standard ALTER TABLE statements, “and Azure SQL Database will appropriately propagate changes to the history table”. It’s good to see feature parity across products like this. Temporal tables even work on Basic-sized databases.

Go forth and play with Temporal Tables. You’ll no longer audit data changes the same way. Get the benefits of Change Data Capture without the need for massive complexity.

Share your CDC horror stories with me on Twitter, at @bornsql.

Azure Storage Throughput

Hot on the heels of DTUs from earlier this week, today we’re going to switch focus a little, while staying with Azure, and have a quick look at storage speeds on SQL Server running on Azure Virtual Machines (Infrastructure-as-a-Service).

Microsoft recently lowered their prices for more popular VMs, which is making this an attractive solution for a lot of folks who don’t want to run their servers on-site.

However, there’s a limit to how fast our storage can get, despite the attraction of solid-state drives on the Premium Storage offering.

We in the industry refer to “latency” when discussing storage. This is measured in seconds per MB (s/MB), or how long it takes to access 1 MB of data. Target latency for SQL Server is 5 ms to 10 ms. In other words, the best case scenario for SQL Server is that we should be able to access 1 MB of data in between 5- and 10-thousandths of a second.

Everything on Azure is virtualised, from the CPU and RAM, down to the storage layer. Behind the scenes, all of these components are connected by what Microsoft calls the “fabric layer”. In simpler terms, it’s the network, stupid.

Yes, it has a low latency. Yes, a customer of mine did save almost 50% of their budget by switching from a data centre to Azure, and yes, they did get a nice performance boost in the process.

However, there’s a limit, and that is the theoretical limit on network throughput. The absolute best case for any local network these days is 10 Gb/s, or in other words, 1.25 GB per second (there are 8 bits in a byte, so we have to divide the bits by eight to get the bytes per second).

In realistic terms though, the connection between our virtual machine and the SSD premium storage, depending on our region and storage tier, is more likely to be 1 Gbps, or 125 MB per second. It’s not really clear from the Azure Documentation what the actual limits are, but I’ve personally seen it max out at 125 MB/s. The region we host in matters a great deal.

Inverting that 125 MB/s figure to get the latency, we’re seeing 8 ms at best. That’s still within the window of 5–10 ms, so it’s not terrible. Now add in the fact that we’re not the only person using that underlying hardware, and the latency starts creeping up. There will be competition on the VM host, the network card, the storage, and it all adds up. Pretty soon we’re looking at 20–30 ms of latency, which is now more than twice as long as the preferred amount.

No matter what the CPU speed is, or how much RAM we have, our bottleneck is always going to be the network. Those of us who have experience with older SANs (storage area network) will recognise this. Even having the fastest spindles (hard drives) in the SAN is meaningless when we’re connected to the server with a 1 Gbps half-duplex network card (no sending traffic is allowed while receiving traffic).

As I say on this blog and in person, Azure is not the answer for everyone (whether we’re looking at SQL Server on an Azure VM or an Azure SQL Database). We need to make sure that we understand the inherent limits of the infrastructure before committing to a long-term project to migrate everything.

I’m not saying Azure isn’t a good idea. I just pointed out above that a client I migrated recently was very happy with their outcome. Their data centre provider offering was terrible, and Azure was a great improvement. I personally run a production system on SQL Server, running on a small Azure VM.

20–30ms might be twice as fast as your current solution. If your database is running on 7200 rpm SATA drives in a RAID 5 configuration, you’ll see a massive increase in performance by switching to an Azure VM with P30 Premium Storage.

Here are some fantastic resources to help you plan your migration better:

It all comes back to the question: Are you sure?

Let me know your Azure migration plans on Twitter, at @bornsql . I look forward to hearing from you.

Database Throughput Units

Last time we briefly touched on the metric that Microsoft uses to keep your Azure SQL Database in check: the DTU, or database throughput unit.

It uses a combination of CPU, I/O and log flushes/second, using a special algorithm, to calculate a single unit.

The reason for this is simple: Our databases don’t use the same resources all the time. We will have busy periods and quiet periods. Some of us are fortunate and only need the database to be available during business hours.

This leaves a bit of mystery around what sort of resources our database may need, if we want to run it in Azure instead of on premises.

The DTU Calculator, a third-party service created by Justin Henriksen (a Microsoft employee), will calculate the DTU requirements for our on-premises database that we want to migrate to Azure, by firstly capturing a few performance monitor counters, and then performing a calculation on those results, to provide the recommended service tier for our database.

Justin provides a command-line application or PowerShell script to capture these performance counters:

  • Processor – % Processor Time
  • Logical Disk – Disk Reads/sec
  • Logical Disk – Disk Writes/sec
  • Database – Log Bytes Flushed/sec

For best results, we should run this session during the busiest time of the day for our database, so as to get a realistic idea of what the load is. Justin recommends running it for one hour.

Once the results are captured, a CSV file containing the output is then uploaded to the DTU Calculator site. It will take a few seconds and then provide a recommended service tier for our database. We can then see how much it would cost to run our on-premises database in an Azure SQL Database environment (assuming of course that it is compatible).

In the sample I captured for an existing client for the purposes of this post, I was surprised to find that the Premium Tier was recommended, at level P2.

DTU Calculator graph

Based on your database utilization, we recommend you migrate your SQL Server workload to Premium – P2. This Service Tier/Performance Level should cover approximately 91.83 % of your utilization.

NOTE: There is approximately 8.17 % of your workload that falls into a higher Service Tier/Performance Level. After migrating your database to Azure, you should evaluate your database’s performance using the guidance mentioned in the  information section above.

This means that it would cost more than $1,350 (CAD) per month (assuming a 24/7 uptime) to run this database in the cloud at the P2 level. A SQL Server Standard Edition licence for the existing hardware, when it is time to upgrade, will cost a shade under $10,000 (CAD) for four cores. In other words, the licence will pay for itself within eight months.

Perhaps this is worthwhile for the customer, if they do away with hardware and other on-site costs, but if their database load increases for any reason and resource limits are repeatedly hit, they will have to move up to the next level (P3), which is immediately double the monthly cost.

Please make use of the DTU Calculator if you intend to migrate an existing database to Azure SQL Database. It should form an important part of your migration plan. There is no reason not to, and it will save you the pain of making a costly and time-consuming mistake.

Let me hear your DTU results on Twitter at @bornsql .

Azure SQL Database Limits

Let’s talk briefly about resource limits with Azure SQL Database. Because we have to share resources with other users, and because Microsoft doesn’t want us affecting others dramatically, they have implemented some limits.

If for some reason our database does get overeager, the operations relating to CPU, RAM and I/O will be queued up by the underlying resource manager, which will allow those operations to continue once the resources are free. Yes, that will slow down our system.

Resources other than CPU, Memory, Log I/O, and Data I/O, as noted by the documentation, will be denied, and clients will see an error message.

Microsoft uses something called DTUs (Database Throughput Units, a combination of CPU, RAM and log writes) to figure out our Azure SQL Database’s resource usage.

Check out the DTU Calculator to find out more, and see how your on-premises database compares. Next week, we will go into more detail about DTUs, and how to calculate the DTU usage of an on-premises database.

It gets really complicated, really quickly, but using the DTU Calculator before we decide to move to Azure SQL Database, and being conservative with our estimates, will make our lives easier.

The biggest an Azure SQL Database can be on the Standard Tier, is 250 GB. The biggest it can be on the Premium Tier, is 1000 GB.

Coincidentally, the SQL Server Customer Advisory Team (SQLCAT) wrote a blog post titled “Real-World Azure SQL DB: Unexpected Database Maximum Size Limit” last month, about a gotcha when upgrading between service tiers. It is recommended reading.

It helps to draw parallels between Standard Tier and Standard Edition of SQL Server, and Premium Tier and Enterprise Edition of SQL Server.

(Edit: With SQL Server 2016 Service Pack 1, you can now create Memory-Optimized objects in all editions of SQL Server.)

There is a Basic Tier for Azure SQL Database, but the resource limits are so strict that I barely consider it. Perhaps if you run a small MySQL database for a WordPress website, with a caching plugin, this may be appropriate.

Using elastic pools (the ability to share the count of resource DTUs between several databases in a pool, that shrinks and grows as your requirements change) can help with balancing costs across several databases, but it is going to seem more expensive at the outset.

The bottom line with Azure SQL Server is that the resource limits may take you by surprise if you don’t plan correctly. Spend the time to evaluate the requirements of your database, and don’t just use Azure SQL Database because it’s new and shiny. If you currently have an Enterprise licence for SQL Server, chances are you’ll spend the equivalent (or more) on a Premium Tier database.

Azure SQL Database is not a way to save money on your SQL Server licences. It is a different technology, with different resource requirements, that cater to a specific workload. Choose wisely.

If you have some thoughts about DTUs, come and talk to me on Twitter, at @bornsql.

Planning matters

It’s the 2016 PASS Summit at the end of this month, and I’ll be attending for my third year.

Because I’m a glutton for punishment, I’ll also be writing bonus posts for the month of October to celebrate Summit.

Fortunately, because I’m the luckiest person in the world, I am attending the Pet Shop Boys concert in Vancouver on the Monday evening beforehand, so that is going to be exciting, too.

Getting on a plane and flying from Calgary to Seattle is such a convenience these days. You book your flights, drive to the airport, breeze through airport security, and the plane jets you, undelayed, to Vancouver, where you hop on a train to your hotel, have dinner nearby, and then walk to the concert. The following morning after a good night’s uninterrupted sleep, you hop back on the train to the airport as scheduled, pass quickly through passport control (so as not to trouble you with that on the other side, one of the benefits of flying to the US out of Canada), once again breeze through airport security, and the plane jets you to Seattle-Tacoma airport, where you get off the plane quickly, fetch your luggage, breeze through customs, and there you are. A short train ride to the hotel later, you’re ready to face the networking track of the Summit for three days.

If only migrating a SQL Server database to Azure SQL Database were this simple.

Of course, I’m joking. There are many very important steps involved when thinking about flying to another country, even if it’s just for a few days. This is especially complex when you add in a side-trip to Vancouver for an evening. It requires careful planning, making sure enough time exists to get everything done. And you can’t forget your passport either.

So why do so many people in the Information Technology industry want to do things without careful planning?

Every time (and I’m guilty of this at times, too), a new technology comes out, and it becomes trendy, we all jump on the bandwagon and start playing around with it. So far, so good. That’s the equivalent of kicking the car tyres.

And then management says (because management plays golf with vendor sales, or the developer lead likes that it uses tabs instead of spaces) that we have to move to this new technology to save the company lots of money. Less good, but doable.

The next logical step is to do a proof of concept. That’s where we research the requirements of the existing system, the limitations of the new technology, and build a working model to see if it is fit for purpose. If it is not fit for purpose, the amount of time we have expended is minimal, and we continue bug fixing on the older technology stack, letting management know that it is not possible, or practical, to move to this new technology.

If the proof of concept shows merit, then we go back to the project manager and business analysts, and begin planning for migration. We include all the stakeholders, including customers (internal and external), and slowly develop clear goals and timelines. There’s a project lead, and politics don’t enter into it. And unicorns are real.

Why is it that we want so badly to “play” with the new technology that we lose sight of this necessary step?

Microsoft announced Azure SQL Database in 2009. While it has been seven years since that time, and children born in that year are now at school, it’s a very short time in the technology world. SQL Server itself is only 27 years old. COBOL, which is still very much in use, is 57 years old. C is 44 years old. Large chunks of the Internet infrastructure are built on C and C++.

My point is, if we are going to move a company’s systems to newer technology, we have to consider longevity:

  1. How long will the new technology be around?
  2. How long will the system we’re migrating be around?
  3. (Bonus question) Will someone getting upset on the Internet destroy my system?

The bonus question relates to the crisis NPM faced earlier this year, when a single 11-line JavaScript file caused thousands of projects, that depended on it, to fail.

Moving to Azure SQL Database is a complicated process. While the underlying structure might look familiar, and while most of our code will probably work fine, there are major stumbling blocks that could scupper our plans entirely.

Take for example the fact that you are not allowed to address tempdb directly. You can create local temp tables and table variables just fine, but you cannot check for their existence by referring directly to tempdb.

This works:

CREATE TABLE #test (col1 INT);
DROP TABLE #test;

This does not:

IF EXISTS (SELECT * FROM tempdb..sysobjects
WHERE id = OBJECT_ID('tempdb..#test')
DROP TABLE #test;

This “minor” issue occurs in many more places than you might think it does, because everyone reading this knows about that one legacy system we’re too scared to touch.

If management is pressuring you to migrate to the new stuff because it’s trendy, they’re doing it wrong.

Careful planning is what you need. This includes a proof of concept, a 100% pass rate using the Data Migration Advisor, and a project manager who understands the complexities involved, plus a proper testing plan that includes automated unit tests, edge cases, and humans bashing away at keyboards and mice.

And don’t forget to plan for a disaster, especially for a disaster that occurs during the migration.

Share your migration horror stories with me on Twitter, at @bornsql .