A Fine Slice Of SQL Server
Microsoft now offers an Azure image for SQL Server Express:
We just announced that we added images for SQL Server Express with Tools 2014, 2012, and 2008R2 in the Azure Gallery. SQL Server Express is a free version of SQL Server that you can use for dev/test and for web and mobile apps with lightweight relational database needs.
I’m not the world’s biggest fan of Express edition, but if you’re cost-conscious enough, this might be a nice move for you.
Comments closedJohn Handra shows how to configure read-only routing on an Availability Group:
NOTE: SQL Server 2016 introduces load-balancing across the secondary replicas in your AlwaysOn Availability Group. To utilize this feature, the above routing list will need to be modified so that SQL Server directs incoming read-only connections across the servers you choose to be load-balanced. You will have to use nested parentheses around the server instances you want to be part of the load-balanced group.
Read-only routing requires some additional setup and possibly load changes, but it can give you a nice performance gain by off-loading some of your read requests.
Comments closedSummary: We will need to drop and re-create any indexes, clustered or not, that reference the IDENTITY column – in the key or the INCLUDE. If the IDENTITY column is part of the clustered index, this means all indexes, since they will all reference the clustering key by definition. And disabling them isn’t enough.
Getting column sizes right at the beginning is your best bet. Stay tuned for other alternatives.
Comments closedJason Thomas shows us how to create an NBA-style shot chart using Power BI:
My entry is an analysis of the first 35 games played by Stephen Curry from the Golden State Warriors in NBA. The main feature of the entry is a Shot chart which shows the position from which he attempted his shots and the color denotes whether he made or missed it.
Jason also asks you to vote on his contest entry if you think it’s good.
Comments closedSometimes, Michael J. Swart says, it’s better to just do it:
Okay, this is getting out of hand. The query shouldn’t have to be this complicated.
Luckily I work with a guy named Chris. He’s amazing at what he does. He questions everything without being a nitpicker (there’s a difference). He read through the Mythbusters post and followed all the links in the comments. He asked whether gbn’s JFDI pattern wasn’t better here. So I implemented it just to see what that looked like:
I’ve ended up doing the same thing in a similar scenario. But as Aaron Bertrand notes in the comments, test your results because performance could end up being even worse than before.
Comments closedTom LaRock shows how to exfiltrate data in a database protected by Transparent Data Encryption:
Enabling TDE does not protect your BACPAC files, just your database backups. If you are relying on TDE to protect your data at rest then allowing users to create BACPAC files will put you at risk. But no more risk than any other user choosing to run a SELECT statement and save the data somewhere (or perhapsjust use PowerBI to open a connection and import to Excel).
TDE has a single, specific purpose. If you want something more stringent, SQL Server 2016 Always Encrypted might be an option.
Comments closedClearing out a full transaction log is a common problem. A quick search will find you dozens of forum entries and blog posts. Because of that I’m not going to talk about the correct methods of dealing with a transaction log full error. What I want to discuss is why you shouldn’t use the following method.
And Kenneth also hits the one legitimate use: dire emergency. If this is a normal part of some process (e.g., warehouse loading), bite the bullet and either live in Simple recovery mode (understanding the risks) or get the disk space to keep it in Full mode. Switching back and forth—especially if you aren’t taking full backups immediately after switching back—is a good way to get yourself burned.
Comments closedJens Vestergaard shows us custom assemblies in SSAS:
The example in this post will be the well known Hello World example in the context of SSAS, and I trust this will illustrate the possibilities with this technique well enough, for you to apply your own solution, to your challenges.
If you’re at all familiar with CLR in the database engine, this looks to be the Analysis Services equivalent. Hopefully it doesn’t have the same “We can’t possibly use this!” taboo that CLR seems to have in the database engine world.
Comments closedThe app’s plan was cached the day before. But wait a second! My assumption was that it had recompiled this morning due to the updated stats.
Also check out Aaron Morelli’s link to an older blog post on how the plan cache works.
Comments closedKendra Little has a few warning signs for backups:
5. Your log backups run every 30 minutes. I have yet to find a company with log backups running every 30 minutes who was actually OK with losing 30+ minutes of data. Maybe you are part of the company where it’s actually true, but if you’re not 100% sure, get someone to sign off on it. With an ink pen. Really.
Funnily enough, I’ve experienced exactly this, except the business side was flabbergasted that I wanted to take transaction log backups so quickly—they had a 24-hour RPO, so why bother with such frequent backups? I kept a straight face and explained that if I had my druthers, I’d take a transaction log backup every 1-3 minutes.
Comments closed