SQL Server

Why is moving data between filegroups hard?

****As a consultant its common to walk into a customer site and find databases that are contained in one very large file. For various reasons it can be beneficial to adjust the number a data files for a database. See here. However, in SQL Server moving data from a one file database into a multi-file configuration is a non-trivial task. It’s a two step process, requiring that you add a new filegroup then in the filegroup add your multi-file configuration. Once you have that up, then we need to rebuild the indexes into that filegroup. This can be challenging if you have a lot of tables with a lot of indexes as SSMS allows you do move data but only for non-clustered indexes and only one at a time. Another issue is there are different techniques for moving different physical structures such as clustered indexes, heap and tables with LOB data.

AlwaysOn Availability Groups have made a big splash in the SQL world and are quickly becoming the HA and DR technology of choice for many SQL Server environments. Crucial to their success is the ability to move data between the replicas in the Availability Group quickly and efficiently. In the previous [post][1] we discussed design concepts for offloaded backups in AlwaysOn Availability Groups, specifically we focused on how data is moved between AGs and the potential impact on backups and recovery. It is important to measure and trend replication health and this article introduces techniques and queries that you can use in your environment to measure and trend replication health and some of the nuances of the data reported in DMVs. ### Measuring Availability Group Replication Latency Central to measuring replication health is the [sys.dm_hadr_database_replica_states][2] DMV. On the primary replica this DMV returns rows representing the current state for each database and it’s replicas participating in AvailabilityGroups.  The key fields we’re going to focus on for our monitoring are: * log_send_queue_size – the amount of log records not sent to a secondary * redo_queue_size – the amount of log records not yet redone on the secondary * last_commit_time – the time of the last committed log record on a replica * last_redo_time – the time of the last log record was redone on a replica

AlwaysOn Availability Groups made their initial appearance in SQL 2012 and have generated a lot of buzz, HA and DR in one! Even with AGs, still integral to your DR strategy are backups and with AGs you’re given the option to offload backups to a secondary replica. In this blog we’re going to talk about offloaded log backups the potential impact to your databases’ recoverability under certain conditions, we’ll begin with some preliminaries on data movement in AGs.

Today I am excited to announce that I have been accepted into the Friends of Redgate program for 2015. The program targets influential people in their respective technical communities such as SQL,.NET and ALM and enables us to participate in the conversation around product and community development. In the short time I’ve been a part of this, I can already see the value of the program! Did I mention how excited I am:)

In-Memory OLTP – a potential game changing technology

Every once in a while a technology comes out that has the potential to change things dramatically. In-Memory OLTP (Hekaton) is one of them. The design team set out with a goal of reaching an order of magnitude improvement over existing technologies and techniques. To do so they had to rethink key facets of the relational database system, latching, locking, logging and statement compilation. When a technology as potentially disruptive as this comes along it gets everyone’s attention. When an opportunity to review a book based on this technology came along it certainly is worth the effort. I spent a ton of time with this book (maybe a little too much), reading and re-reading chapters but it was worth every minute.

Here are my unedited chapter notes:

Chapter 1

Chapter 2

• Collation – current version requires BIN2 on character index columns. Best to do so at the column level. Supports only sorting, comparison and grouping. Will remove the need for case sensitive code on tables and columns but not data.
• Collate database_default to handle tempdb collation of temp objects. Research more.
• Interpreted SQL via interop useful for ad hoc or migration of code.  Doesn’t perform as well as compiled.
• Key point restrictions really show that the Hk tables need to be isolated bc of the interface between the engines. P 46

Chapter 3

Often I’m asked what is the best practice for a single SQL Server installation. Well, that is a tricky questions and the answer is it always depends. Let’s discuss what a “standard” SQL Server build looks like if you had to start somewhere. Here let’s focus on Standard edition on a physical server. Enterprise edition and virtualization are topics that can stand on their own.

**Processors – **The higher the clock frequency the better

This morning at a customer site I was researching an issue where Availability Group read-only routing was not working correctly. Quickly I was able to determine the issue was a misconfigured read-only routing URL list. In this blog post I’ll show you the requirements for read-only routing in Availability Groups, how I determined the URL list was the issue and what to do to fix the situation. The requirements for Read-only routing in Availability Groups are:

Recently,I have been working with the Debugger Symbols for SQL Server to generate call stacks and learn more about the internals of SQL Server.

I approached one of our clients about doing this on a non-production system and they thought it be great. They would get better insight on their workload, I would get access to a real workload. win..win, right? Even in their stage environment they have a pretty heavy workload so it would be a good candidate for generating call stack data.

Where to get the debugger tools

To generate the needed symbols you will need the “Windows Software Development Kit (SDK) for Windows 10” download here: https://dev.windows.com/en-us/downloads/windows-10-sdk and click “Download the standalone SDK”

• I like to select the Download option and pick “Debugging Tools for Windows” this will download all of the installation files

How to install the debugger tools

The installation of this software is very straight forward, Then find in the downloaded files \Windows Kits\10\StandaloneSDK\Installers\X64 Debuggers And Tools-x64_en-us.msi and install the debugger and install the tools with the default settings.