Microsoft Ignite SQL Server Keynote Liveblog #MSIgnite

Yes, there are actually multiple keynotes here at Ignite, Microsoft’s new infrastructure conference. With so many products (Windows, O365, SharePoint, Dynamics, mobile, Azure, etc.) and so many attendees (over 20K), they had to break things up a little. This morning, CEO Satya Nadella and friends delivered the overall keynote. This afternoon, Microsofties have delivered a handful of different keynotes by topic, and I’m sitting in the SQL Server one.

Post-Event Summary: Microsoft demoed a few of the features discussed in the SQL Server 2016 data sheet:

  • Always Encrypted – data is encrypted by the client drivers, and unencrypted data is never seen by SQL Server (or SSMS, or Profiler, or XE)
  • Operational Analytics – means you can put an in-memory columnstore index atop your in-memory OLTP tables, and do reporting against Hekaton transactional data
  • R analytical workloads will live inside the SQL Server engine just like Hekaton does
  • Support for Windows Server 2016 and up to 12TB of RAM (which is great, given all the in-memory features)
  • The newly acquired DataZen mobile-friendly reports will ship in the box with SQL Server
  • Stretch tables are like table partitioning, but some of the partitions will live in Azure, while the hot/current data lives on-premise in your SQL Server

Watch the Keynote Recording

Hot diggety, I love Microsoft for the fast turnaround on their session recordings. Here’s the full session:

My Liveblog Archive

Here’s my notes from the session, oldest to newest:

3:15PM – Shawn Bice, Engineering Partner Director, taking the stage to talk about SQL Server 2016 and Azure SQL Data Warehouse.

3:18 – “In this new social environment, people are tweeting.” YOLO.

3:20 – Paraphrasing Shawn: “In the cloud, we want an engineer to come to work, have an idea, and fail as fast as possible. You need to figure out what works and what doesn’t. In the boxed product, you can’t do that. We can try all kinds of things in the cloud, figure out what works, and then put that stuff into the boxed product.”

3:22 – Talking about how the cloud has made things more reliable. I’d be a lot more impressed with this if they hadn’t just totally borked SQL 2014 SP1.

3:23 – “By the time you get SQL Server 2016, we’ll have been running it at cloud scale for many, many months.”

3:24 – The big 3 pillars for this release: mission critical performance, deeper insights across data, and hyperscale cloud.

3:26 – You have a transactional system, then you get the data out via ETL and put it in a data warehouse. It’s delayed by 2-24 hours. If you’re doing your fraud detection in the data warehouse, you’re detecting things a day too late. How can we bring both rows (Hekaton) and columns (ColumnStore) together faster?

3:27 – Rohan Kumar onstage to demo applying an in-memory columnstore index to an in-memory OLTP table. Instant applause just at the explanation of what’s about to happen. Shawn: “Alright, we’re off to a good start!”

3:30 – “Close to zero impact on your OLTP transactional systems.” I know some people will go, “wait, that means there’s an impact!” Well, you never get something for nothing, and with today’s hardware, it’s pretty easy to buy faster gear to pay for eliminating an ETL process altogether.

3:33 – For the record, we can’t see the build number onscreen, but his instance name included CTP2X.

3:34 – On to Always Encrypted. Interesting that there’s a space between these words, unlike AlwaysOn. “Always Encrypted is literally about always encrypting the data.” Requires an enhanced ADO.NET library that uses a column encryption key.

3:36 – Rohan back onstage to show Always Encrypted with…Profiler. I LOVE MY PROFILER. It will never die.

3:42 – To migrate your existing data into Always Encrypted, they’re suggesting using SSIS to pull the table down, encrypt it on the client side, and then push it back into a new table. Not exactly seamless, but it points out that the SQL Server engine is simply not going to be involved in the encryption/decryption process. You’re not going to be directly accessing this data via T-SQL queries in stored procedures – it’s gonna be encrypted there.

3:43 – Support for Windows Server 2016 with up to 12TB of memory. I’m gonna go out on a limb and guess that’s not in SQL Server Standard Edition.

3:44 – PolyBase – query relational and non-relational data with T-SQL by introducing the concept of external tables. “We did it a few years ago but it was only available in our APS/PDW, but it’ll be available to you in the box in SQL Server 2016.”

3:45 – About the acquisition of Revolution Analytics – we’re adding a 4th workload to SQL Server. When we added in-memory OLTP, we didn’t ask you to build separate servers. It’s a feature, you just turn it on. R analytics will be the same thing, hosted in the SQL Server process.

3:48 – Yvonne Haarloev coming onstage to demo DataZen.

3:52 – Looks like the DataZen demos are happening on a Surface. Was rather hoping the mobile strategy demos would be done on, uh, a mobile device.

3:55 – Yay, mobile dashboard demos! The downside is that you have to design a dashboard for each device’s form factor – they’re not responsive design. Still, way better than what we had. But like SSIS, SSAS, and SSRS, this is yet another designer and a totally different code base that BI pros will have to deal with. No cohesive story here – but you just can’t expect one with the recency of that acquisition.

3:56 – “We are very committed to Advanced Analytics and bringing it into the engine. If you found in-memory OLTP to be favorable…” If you did, I would actually love to talk to you. Seriously. Talk to me.

3:59 – Stretch tables – basically, table partitioning that splits the table between on-premise SQL Server and Azure storage. It will support Always Encrypted and Row Level Security. The entire table is online and remains queryable from on-premises apps. Transparent to applications. (I’m just typing from the slide, no opinion here.)

4:00 – Stretch tables are activated by sp_configure ‘remote data archive’, 1 – it’s not turned on by default. From there, you can enable a specific database to stretch to Azure using a wizard that starts with a login to Azure.

4:03 – As part of the Azure sign-in process, Rohan got a call from Microsoft because he has two-factor authentication turned on. He had to put in his PIN, and then the SSMS wizard kept going. Spontaneous applause from the audience – great to see enthusiasm for security features.

4:08 – The stretch tables demo failed due to firewall configs. Oops.

4:13 – Demoing a restore of a database that has stretch tables. The Azure connection metadata is apparently stored in the SQL Server database, so it just gets attached again after the local part of the data is restored.

4:16 – Now switching to Azure SQL Data Warehouse. It has separate storage and compute – you don’t need compute power all the time for data warehouses, like during big batch jobs. You only have to pay for that compute power when you need it for the jobs. It scales elastically, so you can crank up big recommendation horsepower during peak holiday shopping seasons, for example.

4:21 – Rohan back onstage for the first public demo ever of Azure Data Warehouse.

4:27 – Discussing about what work retailers have to do when they have discount battles. What would it really cost us to match another company’s discount? Could we profitably pull that off? Power BI helps answer that question with large amounts of compute quickly.

4:28 – Like this morning, they’re talking up the differentiators between Azure SQL Data Warehouse and Amazon Redshift.

4:29 – Shawn: “This is all real, none of this is faked out.” “I’m more excited than ever.” Yeah, there’s a lot of really good stuff going on here this year. And that’s a wrap!

Reading the SQL Server 2016 Data Sheet

The SQL Server 2016 PDF data sheet is out, giving us the first public look at the next round of features in Microsoft’s database product.

Quick disclaimer: I’m a Microsoft MVP and Certified Master, and we’ve got clients that work with Microsoft. As such, I’m bound by a few non-disclosure agreements. For this post, I’m going to take off my regular hat, and put on my reverse-engineering hat.

“In-memory enhancements: 30X in-memory OLTP + 100X in-memory ColumnStore = Real-time operational analytics” – SQL Server 2014 brought us each of these features individually. ColumnStore indexes really can get you 100X performance on your analytical warehouse-type queries, and Hekaton may or may not give you rocket speed transactional performance. Both of these storage types are very specialized – for serious performance reasons, you don’t want to run analytical queries against your Hekaton tables, and you don’t really want to do transactional delete/update/insert activity against your ColumnStore tables. The graphics in the brochure show one transaction going into both tables. While this might at a glance seem like you get the best of both worlds, keep in mind that both of these say “In-memory” – and you’ll probably need two worlds’ worth of memory to pull this off. Fortunately, servers like that are available today.

“Always Encrypted technology helps protect your data at rest and in motion, on-premises and in the cloud, with master keys sitting with the application, without any application changes.” In the past, we’ve basically had two places we could encrypt or decrypt data:

  • In the database – in which case, the database administrator could see private data, thereby giving security professionals nightmares
  • In the application – which also gave the security pros nightmares, because our developers would be rolling their own encryption code

The key phrase here says “protect your data at rest and in-motion…without any application changes.” Microsoft’s implying app-level encryption, keeping those prying DBA admins out. However, if the data is encrypted before it hits the database, you need to consider the following scenarios:

  • Analytics queries – your analytics and reporting apps will have to be equipped to decrypt the data if you need to show it onscreen
  • Range queries – if you need to see all of the users whose last names are like Smi%, you need to decrypt them
  • Replication – if you want to copy the data from one SQL Server to another, it usually needs to be decrypted on the way out

“High Availability…with the ability to have up to 3 synchronous replicas, DTC support, and round-robin load balancing of the secondaries.” No reading between the lines necessary here – these are hotly requested features for AlwaysOn Availability Groups.

“Manage document data with native JSON support.” While this has also been hotly requested, I’d caution you to be careful what you ask for. Users demanded XML support inside SQL Server, and then proceeded to use SQL Server as an XML query engine, sending CPU through the roof. SQL Server is one of the world’s most expensive application servers.

“Scale and Manage – Enhanced performance, scalability and usability across SQL Server Enterprise Information Management tools and Analysis Services.” Careful reading this one, and note which column it’s in – “Deeper Insights Across Data.” While you might read this as a better/faster/stronger SQL Server Management Studio, that’s not what Enterprise Information Management means.

“Powerful Insights on any device – Business insights through rich visualizations on mobile devices. Native apps for Windows, iOS and Android. New modern reports for all browsers.” Microsoft recently acquired DataZen, a reporting tool that focuses on mobile devices, and promptly gave it away free to most Enterprise Edition customers. The simplest way to accomplish this feature would be to simply throw DataZen’s existing code in free with the SQL Server boxed product. Like any acquisition, I wouldn’t expect this to become a seamless part of SQL Server for a year or two at least. (Think Microsoft’s acquisition of DATAllegro back in 2008, then gradually transitioned into Parallel Data Warehouse aka APS. Some will say, “Well, that was a tougher product to build,” but native apps for mobile BI ain’t an easy nut to crack either – as evidenced by Microsoft’s difficulty in getting to that sweet pistachio. Look, this analogy had to go off the rails at some point, we all saw that coming)

“Advanced Analytics at massive scale – Built-in advanced analytics provide the scalability and performance benefits of running your “R” algorithms directly in SQL Server.” Just like you can run your C# code in the database, too. Or your XML parsing. Or your JSON. If you wanna do your processing in a $7k USD per core database server, Microsoft wants to empower you to do it. I would too, if I was wearing their exceedingly good-looking and expensive shoes. I like the way they think.

“Stretch Database technology keeps more of your customers’ historical data at your fingertips by transparently [sic] and stretching your warm and cold OLTP data to Microsoft Azure on-demand without application changes.” Database admins often come to me and say, “I’ve got a ton of data in a table, but most of it just isn’t queried that often. I need to archive it. Will table partitioning help?” Sadly, it usually doesn’t, because good table partitioning requires changes to your application. If you want great performance, you have to make sure your WHERE clause specifically excludes the archival partitions. Otherwise, SQL Server isn’t terribly good at excluding those partitions, and it ends up scanning all of the partitions.

Microsoft sounds like they’re moving the cold table partitions up into Azure storage. That had damn well better not be the only portion of the solution that they deliver – they have gotta deliver better partition elimination. If not, this feature is going to be legendarily bad, because scanning partitions up in Azure is going to be even worse than scanning partitions locally. Questions around a feature like this would include:

  • How will backups work?
  • How will restores work?
  • How will DBCCs work?
  • How will data be moved from on-premise to Azure?
  • Can multiple servers attach to the Azure partitions? (For example, if you regularly back up production and restore it over to development, how will that work?

“Temporal Database: Track historical changes” – Whenever Jeremiah shows me an Oracle feature, I get so jealous. Oracle’s got this really cool feature where you can query a table to see what it looked like at 5PM yesterday, or at the start of the last financial quarter. It ain’t free/cheap – after all, you have to store all the historical data to make those queries work. For scenarios that need that level of archival detail, though, that’s killer.

“Row Level Security: Apply fine-grained access control to table rows based on users rights”I blogged about this when it was unveiled in Azure SQL Database.

“Dynamic Data Masking: Real-time obfuscation of data to prevent unauthorized access.” – We’ve got clients who work with payroll, health, and financial data. They need to restore their production databases into their development and QA environments, but it’s critical that the developers not see personally identifiable information like social security numbers. These numbers are stored in the database. So what’s a DBA to do? Currently, the solution involves scrambling the data as soon as the restore completes, but that’s painful.

“Enhanced Database Caching: Cache data with automatic, multiple TempDB files per instance in multi-core environments” – First off, I would just like to point out that Microsoft uses my preferred capitalization of TempDB. Take that, rest of Brent Ozar Unlimited®. But back to the issue at hand – TempDB (BOOM!) configuration has long been a sore spot for database administrators. TempDB defaults to just one data file, no matter how many cores you have. In a perfect world, Microsoft would fix the SGAM and PFS page contention problem. Or they could just duct tape it by automatically adding multiple files when necessary. Sounds like they chose the latter. I’m okay with that.

“Enterprise-grade Analysis Services” – wait, what was it before?

“PowerBI with on-premises data: New interactive query with Analysis Services. Customer data stays behind your firewall.” – PowerBI looks great, but it’s for Office365 customers only right now. A lot of our clients have smiled and nodded past that. The cloud is indeed the future, and most of us believe most of the data will end up there eventually, but Microsoft’s acknowledging the reality that for most of the market, it’s still the future, not the present.

“Easy migration of on-premises SQL Server: Simple point and click migration to Azure.” Migrating databases isn’t the problem. Moving all of the application servers and related services up to the cloud, that’s where the hard work is.

And now it’s off to the afternoon SQL Server keynote here at Ignite! Wow, this is really turning out to be the conference to be at if you want to see the future of SQL Server.

Microsoft Ignite Morning Keynote Liveblog #MSIgnite

Summary: Microsoft announced SQL Server 2016, with the first public preview coming this summer. The SQL Server 2016 release dates for the download were not announced. Here’s the SQL Server 2016 PDF data sheet, and here’s the SQL 2016 announcement.

As it happened:

8:00AM – Good morning, folks. This week, Microsoft’s new infrastructure conference, Ignite, opens in my current hometown of Chicago. Gotta love it when the conference comes to you – along with a sold-out crowd of 20,000 attendees.

Before talking about this morning’s keynote, let’s put the timing in context. Last week at the Microsoft Build conference in San Francisco, Microsoft made a few data-related announcements:

Given that Build is a developer-focused conference, where the audience would really have loved to hear dates, see demos, etc, and didn’t get them, I don’t expect to see more details coming out of this morning’s Ignite keynote.

However, there are two types of keynotes today. First up this morning, we have Satya Nadella doing the overall keynote. With the infrastructure focus of Ignite, I’d expect to see a heavy focus on Windows, O365, SharePoint, System Center, and Azure.

Later this afternoon, we have another set of keynotes: foundation keynotes, including one dedicated just to SQL Server. I’m much more excited about that one, and I’ll liveblog that one as well.

This morning, though, it’s all about Windows. So why am I here? Well, I got my start as a sysadmin, and I’ve never seen Satya Nadella in person before, and hey, it’s only about 20 blocks south of my house.

The keynote starts in about 90 minutes, and you can watch online at

8:18AM – the logistics of filling a room this big, wow. It’s an air traffic control style job, with dozens of staffers waving lit batons around, filling up one section at a time. The front sections are reserved for press, a tech leaders forum, and so on.

8:30AM – the button-down DJ explained that the live online feed is about to start, so he left the stage. He’s a warmup act, evidently. The music continues even though he’s not onstage. This is actually a political statement that human beings are no longer needed for block-rockin’ beats, and Microsoft is about to unveil their new product, IntelliDJ.

8:36AM – The audience is cackling about the awkward transcriptions onscreen.

8:38AM – How the hell is the WiFi still working this well? Witchcraft!

8:40AM – A Microsoft Band on one hand and an old-school watch on the other is the new socks with sandals.

8:43AM – Transcriptions good for laughs. “So whale you’re watching the keynote…”

8:48AM – Sounds like like the keynote streaming site is suffering issues already. If there’s 20K people here, I wonder how many people are trying to stream the video live. I don’t envy Microsoft – that’s hard work.

8:53AM – The pre-keynote-show is a neat idea, but talking to people about the motivation behind their tweets? Really?

8:58AM – It took two days to lay out the 15,000 chairs in this room. I believe it. Wow.

9:00AM – The WiFi failed me briefly, but only briefly. Nice job keeping this up and running!

9:03AM – The room goes dark, and they’re playing Microsoft’s version of the Dolby theater intro.

9:06AM – Common walks off the stage, and Satya Nadella takes over. The air conditioning just kicked on big time, too. Or maybe it’s the cloud coming in.

9:09AM – Satya: “We are the only company that feels deeply about both companies and organizations, and bringing them together to empower transformations.” Uhh, okay, no.

9:11AM – Mobile first, cloud first – but it’s not about the mobility of a single device. It’s about the mobility of the experience. It’s about the cloud back end, and adding intelligence to your experiences. (Interesting take – one way to read that is, me: “We’re giving up on one device to rule them all, and just putting our experiences on every device.”)

9:14AM – Satya: “There are going to be more devices than people on the planet.” In an environment like that, Microsoft can be a winner. Not THE winner, just one of multiple winners, and that’s a good thing. Microsoft has had a tough time selling devices that consolidate – for example, Surface Pro simply isn’t selling as the tablet that can replace your laptop, but if it’s just one of your devices, it makes much better sense.

9:17AM – Satya: IT is Innovation & Transformation. Cute. “Microsoft is the productivity and platform company that will thrive in the mobile-first, cloud-first world.”

9:20AM – Satya: “It is important to us to build trust into the core of the operating system…that’s not a bolt-on, you have to build that in.” That’s a tough sell here – it hasn’t historically been one of this platform’s strengths.

9:22AM – Announcing Windows Update for Business, but no details on that yet.

9:25AM – Satya’s talking a lot about letting people make their own technology choices, but letting IT staff get the control and compliance they need.

9:26AM – Office 2016 is going into a new public preview, Skype for Business will have live broadcasting features, Office Delve will have more organizational analytics. Will show you all of these as the keynote proceeds.

9:27AM – There’s been multiple announcements so far, but just kinda glossed over, no clapping.

9:28AM – Satya says SQL Server 2016 is “the biggest database breakthrough you’ve ever seen.” Makes the version number official, apparently – doesn’t seem to be a code name.

Satya announcing SQL Server 2016 at #MSIgnite.

A photo posted by Brent Ozar (@brento) on

9:34AM – Satya telling the story about Fujitsu selling lettuce grown in their old clean rooms. “So think about it,” he says to the attendees. Okay. I’m thinking. How is Microsoft reinventing themselves, is that what you want me to think about?

9:35AM – “Realmadrid is an amazing brand. Everyone knows about them.” I have no idea who they are. The attendees apparently do, though – the first round of applause we’ve had yet as the CEO comes onstage.

9:38AM – Satya: “You’re like a software company now.” Talking about the work Realmadrid does in analyzing data about their fans. Pretty vague.

9:40AM – Details are starting to come out about Windows Update for Business.

9:41AM – Joe Belfiore coming out to talk about Windows 10, then Gurdeep Singh Pall will talk about reinventing productivity with Windows, O365, and Dynamics. Brad Anderson will talk about security.

9:44AM – Joe: “My mission is to convince you, and give you the tools to go back with, to get your end users to love Windows 10.” Sounds perfect for this audience. Alright, I’m in. Show me what you’ve got.

9:48AM – Joe says about 5-8% of users use Alt-Tab to switch between apps. Adding multiple desktop support. Hold down control/Windows/right-arrow and switch between desktops, or left-arrow to go back. Dragging an app from one desktop to another got the first spontaneous audience applause.

9:51AM – Demoing Cortana, asking her questions aloud. This is especially useful in today’s workplace with open cubes and meeting rooms. Oh, wait, hold on, I’m being told…

9:54AM – Demoing Cortana connecting to PowerBI. “What’s the number of people at Ignite by country?”  Pops them up on a map with circle graphs and no numbers, not really useful at all.

9:58AMAnnouncing SQL Server 2016 – and the feature list!

10:04AM – Demoing Continuum, a new way of changing the Windows user interface to change between tablet mode and laptop mode depending on how you hold the device.

10:12AM – Demoing Windows Hello, a new authentication mechanism that works through the webcam. He takes a cloth off a webcam, and it unlocks his laptop in a matter of seconds without the user doing anything.

10:18AM – Belfiore off the stage. Good demos, relevant to this audience. Nothing jaw-dropping or magical, just steady progress. I don’t think he delivered on his commitment to get users excited about the Windows 10 UI, though.

10:19AM – Gurdeep: “Even as I speak, some of you are swiping right on Tinder.” HA!

10:21AM – Gurdeep is explaining how millennials are different by saying that his kid came up and told him “yolo.” Uhhhh..

10:23AM – Millennials work wherever they are. (News flash – seriously, this is not a millennial thing.)

10:24AMOffice 2016 Public Preview now available.

10:25AM – At Microsoft, we consider ourselves the custodians of productivity. (Again, news flash – millennials would probably tell you otherwise.)

10:27AM – Users can now create their own groups and teams on the fly, share things with them, schedule meetings. (I literally can’t even.)

10:29AM – “We’re making a huge bet on video for meetings….Polycom, Crestron…” (What year is this?)

10:32AM – Showing a HoloLens video and saying it’s a real, live product that’s that good. The reviews say otherwise.

10:36AM – Julia White coming onstage to do demos.

10:37AM – “This is the YouTube for the enterprise.” Well, in our company, I know what we would have: cats. Lots and lots and lots of cats.

10:40AM – Demoing the use of Delve to store files, share expertise, contact people. Isn’t this what SharePoint was supposed to be? When would I use one over the other?

10:46AM – Demoing live collaboration in Word that works just like Google Docs. Widespread applause. Have that many people really not seen Google Docs? Or are they clapping because Microsoft’s finally getting closer after all these years?

10:47AM – Sway will be part of O365 Business and Education soon.

10:52AM – Some awkward demos around collaboration and inking.

10:55AM – Julie’s off the stage. Her part seemed extreeeeemely rushed – guessing the prior presenters ran too long. This keynote is supposed to be over in 5 minutes. Oops, my bad. Thought it was 9AM-11AM, but it’s til 11:45AM.

10:59AM – Brad Anderson taking the stage to talk security. After 2 hours in a folding chair, I’m out. Time to grab some coffee and snacks, then I’ll blog the afternoon keynote that focuses on SQL Server.

New sp_Blitz® and sp_BlitzCache® Updates

Ah, spring, when a young man’s fancy lightly turns to thoughts of updating his DMV queries.

sp_Blitz®, our free health check stored procedure, brings several new checks and a whole crop of fixes and improvements. I’d like to call your attention to one in particular.

Julie Citro fixed check 1, THE VERY FIRST CHECK IN THE SCRIPT, which had a logic bug when looking for databases that had never been backed up. Sure, the next line had a workaround, but nobody caught this for years. All of you who ever read the source code, Julie Citro is officially a better tester than you.

sp_BlitzCache™, our performance tuning tool, has new updates too.

You can now run with @reanalyze = 1 first and sp_BlitzCache™ won’t break. You can run sp_BlitzCache™ from multiple SPIDs! If you had triggers, sp_BlitzCache™ would start to pick itself up – this has stopped.

Also, an integer overflow has been fixed – queries using more than 28,000 days of CPU since start up will no longer cause an arithmetic overflow.

You can grab the updated scripts in our First Responder Kit.


Microsoft SQL Server Licensing Simplified into 7 Rules

Licensing is really complex, but as long as you know these seven rules, you can go a long way:

  1. If you query it, you have to license it.
  2. “It” means the Windows environment – all of the processor cores that Windows sees. (Things get a little weirder under virtualization.)
  3. Running a backup or a DBCC is considered querying.
  4. If you license it, and you pay Software Assurance, you get exactly one free standby server of equivalent size. (Standby means you’re not querying it.)
  5. Standard Edition costs about $2k USD per core, but caps out at 16 cores and 128GB RAM (for SQL 2014, or 64GB for 2012).
  6. Enterprise Edition costs about $7k USD per core.
  7. Software Assurance is an additional annual fee that gives you free upgrades as long as you keep paying for it.

Then to learn more, get the 2014 Licensing Guide on the right side of the licensing portal.

Kendra says: Licensing is one of the most challenging topics in SQL Server– even just getting those ballpark list prices takes some time and research!

And Party and Alt Shift

This is a cool SSMS trick I picked up a while back

Though not nearly as far back as I wish I had. It’s so cool I made a .gif of it in action. When you’re done putting your socks back on, I’ll tell you how it works.


This .gif was brought to you by the Cool SSMS Tricks Foundation, in association with Worldwide .gifs


Pure ALT+SHIFT magic.

Hold down both keys at the same time, and use your up and down arrow keys to navigate vertically. There will be a thin grey line showing you exactly which rows you’ve grabbed. Then Just type normally. I uh, simulated a typing error, to illustrate that you can also delete text doing this. Yeah.

It really makes doing simple multi-line edits a breeze, especially if you don’t feel like setting up Excel formulas to do similar tasks. These are random Massachusetts zip codes, which is why they get a leading zero, and quotes.

Can you feel the efficiency?!

Kendra says: What in the…. holy cow, that actually works!

Brent says: I knew about that trick, but ZOMG PEOPLE THERE IS A PRODUCTIVITY GIF IN OUR BLOG

Happy Fourth Birthday to Us, 40% Off Presents for You

Four years ago this month, we turned this blog into a consulting company.

We’ve had so much fun over the last few years, and we’re really proud of what we’ve built:

  • Over 500 SQL Critical Care® patients
  • Thousands of training video customers
  • Thousands of in-person training attendees at dozens of classes and pre-cons
  • Hundreds of free YouTube videos watched by over a million people
  • Almost 50,000 email subscribers
  • Several million web site viewers
  • A handful of awesome full time employees

Let’s celebrate. Between now and Tuesday 4/28, discount code Our4thBirthday gets you 40% off online sales of our training videos. Enjoy!

How to Evaluate NoSQL Case Studies [Video]

Every now and then, one of our clients considers adopting an alternative database platform – sometimes NoSQL, sometimes a brand new relational database. They’ll ask for our help in evaluating the vendor’s solution.

One of the best ways to do it is ask the database vendor to set us up on a WebEx or GoToMeeting with one of their happy customers. I want to hear from the technical folks, not management.

I run the call with six PowerPoint slides – here’s how:

“Breaking” News: Don’t Install SQL Server 2014 SP1

Yesterday, Microsoft announced availability of Service Pack 1, saying:

As part of our continued commitment to software excellence for our customers, this upgrade is available to all customers with existing SQL Server 2014 deployments via the download links below.

Yeah, about that commitment to software excellence.

This morning, the download is gone:

Notice: The SQL SSIS team has found an issue with SP1 installation if SSIS catalog is present in the SQL Server instance.They are currently investigating this issue including possible workarounds and fixes.

Oof – the term “possible workarounds and fixes” doesn’t sound good for those who jumped in and applied the patch. A commenter on the MS Data Platform Insider blog reported that it hosed the master database broke the instance in a way suspiciously similar to a similar bug in SQL Server 2012 SP2.

(And jeez, what is it with service packs lately? Remember the SQL 2012 SP1 100% CPU issue? I’m starting to think you’re safer with cumulative updates than with service packs.)

Remember, kids, don’t rush into patching. If your servers are mission critical, test in your staging environment first – staging is the DBA’s development. (No, your development environment isn’t staging – because your developers make their living in the dev environment, and if you broke that with SP1 yesterday, you’ll be slaving away today to get your dev instance back up and running.)

Update: the fix is in. If you applied SP1, follow the instructions in this StackExchange post.

Moving Databases Made Easy – SQL Server on a File Share

We’ve all had to move a database. For databases above a small size, this is a painful process. While there are a number of ways that you can move a database without downtime, they all involve a lot of extra work. What if there were something easier?

Every database is unique and precious.

Every database is unique and precious.

What’s the Real Problem?

For most people, moving a database is a one time thing – you’re moving to a new server. That’s pretty much it. I spent 5 minutes staring at my ceiling trying to figure out other reasons that most people might move a database from one SQL Server to another.

For some people, though, databases could move on a regular basis. Think about it like this – you host a software as a service (SaaS) application. You’ve got one database per relative unit of work (customer, forum, cat picture collection). These databases can sit idle for months before, suddenly, there’s tremendous activity.

Normally, you wouldn’t make each SQL Server capable of handling peak load for any section of your SaaS. Instead, you would probably have a few servers ready to handle the worst load and move your busiest database over to those servers.

Typically, this is going to involve a small outage and setting up something like database mirroring or log shipping in order to move the database with minimal down time.

SMB to the Rescue!

Microsoft has supported housing SQL Server databases on a file share for a while now. If you’re not familiar with the idea, I recommend checking out the MSDN article Install SQL Server with SMB Fileshare as a Storage Option.

Moving a database with SMB is straight forward – detach the database from the old server, attach the database to the new server, bring the database online. For most of us, this quick change is much preferred to other methods of moving the database.

SMB File Shares and You

Although SMB file shares are a new technology, they’re worth considering for new SQL Servers. SMB file shares make it easy to move databases between SQL Servers. If you want to learn more about using SMB check out the MSDN article on using SMB and SQL Server.

Brent says: If your storage area network (SAN) is using older connectivity methods like 2Gb or 4Gb FC, and your Ethernet is 10Gb, you might even see a performance boost.