Press "Enter" to skip to content

Day: February 12, 2016

Availability Groups And Failover Cluster Instances

Published 2016-02-12 by Kevin Feasel

Derik Hammer digs into a more complex architecture for HA+DR:

The Availability Group will handle the data synchronization between the data centers. Asynchronous mode is recommend due to potential network latency hindering primary site performance. With the combination of these two features, you meet HA with the FCI’s automatic failover locally and DR with manual failover of the Availability Group between sites.

Derik’s conclusion is important:  this isn’t the type of thing a brand new accidental DBA should try to build; there are a lot of moving parts here.  But if you want a robust solution and have the support people available to handle the relative complexity, this is a good option.

Comments closed

SQL Server Express Images In Azure

Published 2016-02-12 by Kevin Feasel

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 closed

Read-Only AG Routing

Published 2016-02-12 by Kevin Feasel

John 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 closed

Widening Indexed Identity Columns

Published 2016-02-12 by Kevin Feasel

Aaron Bertrand shows what happens when you try to widen an identity integer column associated with an index or computed column:

Summary: 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 closed

JDFI

Published 2016-02-12 by Kevin Feasel

Sometimes, 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 closed