In this post we’re going discuss how to implement load testing of your storage subsystem with DiskSpd. We’re going to craft tests to measure bandwidth and latency for specific access patterns and IO sizes. In the last post “Load Testing Your Storage Subsystem with Diskspd”  we looked closely at access patterns and I/O size and discussed the impact each has on key performance attributes.

Diskspd command options

Let’s start with some common command options, don’t get caught up on the syntax. Diskspd’s documentation is fantastic. It’s included with the program download here. Here I’m going to tell you why I set these settings this way, so you can adjust them as needed for your environments.

Encrypting Connections To SQL Server Using Certificates

In this post we’re going to cover configuring a connection string in.NET applications for encrypting connections to SQL Server using certificates. The audience for this document is a developer that needs to configure encrypted connections from applications to a database server.

Encrypting connections with SQL Server using Certificates consists of two parts:

• An appropriately configured connection string
• A server certificate installed on the Database Engine (not covered in this post)

Configuring a Connection String

One of the primary activities I do before bringing SQL Server into production is load testing the storage subsystem. On a new system this is critical because I want to ensure that we’re “getting what we’ve paid for” when it comes to the disk subsystem. All too often there’s a configuration issue, component mismatch, a fundamental misunderstanding of the technology or worse an insufficient disk subsystem…these all can lead to poor disk performance. Even if it’s the simplest test, its imperative to measure performance as it’s significantly harder to make changes to a SQL Server once a database is in production. So do your testing. This is especially an important topic if your disks are not direct attached or in a shared storage environment such as a SAN or VMware data store. Storage networks, controllers, shelves…it gets complicated fast!

I am super excited to announce that I have recently been accepted as an author for Pluralsight.

My audition was on Monitoring AlwaysOn Availability Groups and was accepted on the first pass:) the clip discussed monitoring replication latency something I’ve blogged about here and here.

In the recent weeks I worked with my editor and we have selecting a course topic “Understanding and Using Essential Tools in Enterprise Linux 7”. This will be the first course in a series of courses that covers the Red Hat Certified System Administrator® (RHCSA) and Red Hat Certified Engineer® (RHCE) certifications. If all goes to plan the course should be completed in late November and published a few weeks after that.

In previous posts here and here we discussed AlwaysOn Availability Group replication latency and monitoring concepts, specifically the importance of monitoring the send_queue and redo_queue. In this post I’m going to show you a technique for monitoring Availability Group replication latency with Redgate SQL Monitor and its Custom Metric functionality.

Here’s the rub, monitoring AGs is a little interesting for the following reasons

1. We’re interested in trending and monitoring and that isn’t built into SQL Server or SSMS’s AlwaysOn Dashboard.  Both provide only point in time values.
2. We’ll need to monitor the health of the Availability Group as a whole. So we want to track performance data on all replicas in the AG. But interestingly the redo queue and send queue values in the DMVs on the primary are always NULL. So we need to get those values from the secondary replicas.
3. Further, to work this into SQL Monitor’s Custom Metric framework we’ll need to limit our query’s result set to a single row and value.

Redo Queue

The redo queue is the amount of log records that haven’t been sent to a secondary replica in an AG. We want to track this as it is a measure of the amount of data on a secondary that is not yet redone into the database and can impact operations offloaded to secondaries

Its pretty often that you have to sit down at a SQL Server and need sort out what the backup situation is. One of the first things that I check is, when did the last backup for each database complete? But answering that question is getting more complicated. If you’re using Availability Groups, you could be offloading your backups to a secondary and that can skew your backup data.  In Availability Groups, database backup history is only stored on the instance that the backup executed on.

Over the last two years I have had the pleasure of attending all three SQLskills Immersion Event classes. This training is second to none in its quality and intensity. The three courses help you look at SQL Server from different angles and are major parts of my job and likely yours as well. Each course uses a building block approach where you’re introduced into core fundamentals that the later modules build upon with more advanced topics.

In this post we are going to show the implementation of a PowerShell script using SMO to move data between filegroups on SQL Server. This article is the second of our two part series on “Moving SQL Server data between filegroups – Database Structures”, you can find the first article here.

The Challenge

Looking around on the web, I couldn’t find a solution to the problem of moving data between filegroups that I liked. Further, many of those solutions are T-SQL based, which I thought were very complex. So I went off to write it myself. The problem lends itself to an iterative solution and I felt that T-SQL was not the right tool for the job. Enter PowerShell, which give us the ability to easily iterate over sets of data with minimal code, couple that with the SQL Server Management Object model and we have the makings of an elegant solution.

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