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.
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.
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.
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.
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.
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 SQL Server is licensed by the core.
