In this blog post I will drive you through configurations steps required (with GUI screens) to successfully implement SQL server’s new high availability option called “ALWAYSON” aka HADR .

If you haven’t read more about Always ON or Availability groups in SQL server code named “Denali” version, here are some articles that I would recommend you read before using this blog post to configure SQL server’s new and cool feature.

Problem:

I was asked by a colleague that when he runs a create database statement with large initial sizes for database files , he was able to find the ghost clean-up process with SPID less than 50 ( system process) blocked by the create database statement by simply running the query sp_who2.

In this article I will try and cover the concepts in understanding the amount of memory or buffers required for completing a database backup. I would be using a couple of trace flags to get that detail and further discuss two of the parameters MAXTRANSFERSIZE and BUFFERCOUNT in the BACKUP DATABASE TSQL syntax which will help us understand a bit more about memory consumption for a database backup and time involved in completing the backup.

DBCC CHECKDB & READ ONLY DATABASES:

Can you run a checkdb command on Read-only databases? If yes, does this get logged into boot page of the database? If checkdb finds an error can it fix on a read-only database?

These were some of the questions running through my mind as I manage some read-only databases in an SQL server which is mostly used for archiving and wanted to have the same level of checks and monitoring stuff we do an read write OLTP databases.

Another long-waited feature SEQUENCE is included in the latest version of SQL server code named DENALI. Sequence is now an object in each database and is similar to IDENTITY in functionality. Sequence is an object that has start value, increment value and end value defined in them and this sequence can be added to a column whenever required rather than defining an identity column individually for tables.