Press "Enter" to skip to content

Day: April 14, 2016

DMVs

Published 2016-04-14 by Kevin Feasel

Andy Yun talks about his favorite SQL Server feature:

Much earlier in my SQL Server career, I never knew much about how SQL Server operated under the hood. I only had knowledge of the things I could do directly, but I never questioned what was really happening behind the scenes. Then I attended SQLskills IE1 training, which was a turning point in my career. Among other things, it was my first exposure & deep dive into SQL Server Internals. I became enamored with learning how things really worked under the hood and the DMVs/DMFs became one of my best friends.

This is a big part of SQL Server administration.  It boils down to asking the server where it hurts and understanding how it responds.  DMVs are extremely helpful in that regard.

Comments closed

SQL Agent

Published 2016-04-14 by Kevin Feasel

Steve Jones picks his favorite SQL Server feature:

I’ve seen the platform grow and expand quite a bit. I’ve spoken on a number of topics over the years, as my jobs have changed and my emphasis has wandered. Of all the features available, however, if I have to choose one, it would be…

SQL Agent.

I’m a programmer at heart. I grew up admiring the power of computers to execute code over and over again. I appreciate the ability of computers to remember things and remind me, or to handle them on their own.

SQL Agent has quirks, but a fully-featured scheduling system integrated into the database engine is extremely powerful.

Comments closed

DBCC Checking Memory-Optimized Tables

Published 2016-04-14 by Kevin Feasel

Brent Ozar shows us how to run a DBCC check against a memory-optimized table.  The answer is not trivial:

THE EASY FIX: RUN FULL NATIVE BACKUPS EVERY DAY, AND FREAK OUT WHEN THEY FAIL.

Backup failures aren’t normally a big deal, but if you use in-memory OLTP on a standalone server or a failover clustered instance, backup failures are all-out emergencies. You need to immediately find out if the backup just ran out of drive space or lost its network connection, or if you have game-over Hekaton corruption.

Note that you can’t use SAN snapshot backups here. SQL Server won’t read the In-Memory OLTP pages during a snapshot backup, which means they can still be totally corrupt.

This works fine for shops with relatively small databases, say under 500GB.

Brent also has a Connect item to fix this.  Upvote if you have memory-optimized, durable tables.

Comments closed

Message Handling With Service Broker

Published 2016-04-14 by Kevin Feasel

Colleen Morrow shows how to send and receive messages using Service Broker:

If you’ve been following along in this series, you’ll have created the necessary components of the service architecture, namely the message types, contract, queues, and services.  You will also have started a conversation between your initiator and target services.  You’re finally ready to start sending and receiving messages in your Service Broker app.  Whoohoo!  Let’s get to it!

Here’s what we’ve been waiting for, and it’s a good example going through message handling.  I also hope Colleen handles poison pill messages and other Service Broker-related traps.

Comments closed

Restraint

Published 2016-04-14 by Kevin Feasel

Andy Mallon comes out of left field with his favorite feature:

It’s really tempting to implement cool-sounding features. It’s really tempting to hyper-tune solutions to be the absolutely perfect, most-optimal solution. But it takes a real expert to realize when you’re over-engineering a solution.

Take a moment to appreciate your own restraint. Appreciate all the features that you didn’t implement because you didn’t have to. Be happy that you looked at the big picture and decided the best solution was the one you were able to support.

It’s definitely an interesting approach to the T-SQL Tuesday challenge, so full credit for that.

Comments closed

Azure Transactional Replication

Published 2016-04-14 by Kevin Feasel

John Sterrett names transactional replication into Azure as his favorite feature:

In the field, I see a lot of people using Availability Groups to have a near real-time replica for reporting.  I talked a little bit about this above.  What isn’t mentioned here is you have to maintain a Windows Failover Cluster, Quorum, Active Directory (Unless using Windows 2016 Preview) and more. This gets you a replica that is just a copy of the database. What does this mean? You cannot change database objects like security, indexes, etc. Also, what if you don’t need the whole database(s) for reporting? If not, you can replicate only the data you truly need.

So, let’s recap here.  You only have to replicate the data that you need.  You can have different security and indexes on your reporting subscriber database(s).  The reporting subscriber database can be scaled up or down as needed based on your needs.  The reporting database can now be an Azure Database. Folks, I call this a huge win!

There’s a lot more replication love out there than I’d expect.  John promises to follow up with a guide on how to implement this, so keep an eye out for that.

Comments closed

Change Management Template

Published 2016-04-14 by Kevin Feasel

Kendra Little walks through a fairly simple but very useful change management template:

Good Change Management makes your team smarter. When you change things a lot, things are going to break sometimes. If you’ve done a good change request, you’ll know exactly what to do when something breaks: either you’ll roll the change back, or have a Plan B to execute on. Good change requests also mean that business owners understand the risks of the changes and have approved them, and that teammates have reviewed them: good changes aren’t done in isolation.

Change Management isn’t just for IT. If you’re a developer who deploys changes to production, you need this as well.

Change management doesn’t need to be a complex endeavor.  It should, however, give you enough information to save yourself if things get crazy.

Comments closed