Dear readers,
It’s been a week since I joined Microsoft and after the initial excitement, it is now time to get my blogging home moved. As of now I will be writing mainly to my MSDN blog, so make sure to follow it and be up to speed with all the interesting stuff that awaits me out there :-)
Did you know that disabling a clustered index actually blocks the access to the underlying table? Below is a script you can use to test it, if you do not believe me :)
--First create the test table with a clustered index
USE [FTest]
GO
/****** Object: Table [dbo].[FTable] Script Date: 11/21/2012 2:02:56 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[FTable](
[FTableID] [int] IDENTITY(1,1) NOT NULL,
[LargeString] [nvarchar](600) NULL,
[LargeInt] [bigint] NULL,
CONSTRAINT [clIX_PK_FTable_FTableID] PRIMARY KEY CLUSTERED
(
[FTableID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO
--now test the insert of one row
INSERT INTO [dbo].[FTable] ([LargeString],[LargeInt]) VALUES('This is a test row','123')
GO
--disable the clustered index
ALTER INDEX [clIX_PK_FTable_FTableID]
ON [dbo].[FTable] DISABLE
GO
--try inserting one more row. Note the error: "The query processor
--is unable to produce a plan because the index 'clIX_PK_FTable_FTableID'
--on table or view 'FTable' is disabled."
INSERT INTO [dbo].[FTable] ([LargeString],[LargeInt]) VALUES('This is a test row','123')
GO
--rebuild the clustered index so the table to be accessible again
ALTER INDEX [clIX_PK_FTable_FTableID] ON [dbo].[FTable] REBUILD PARTITION = ALL
GO
--insert one more row to validate the access
INSERT INTO [dbo].[FTable] ([LargeString],[LargeInt]) VALUES('This is a test row','123')
GO
If you go and try to disable the clustered index from SSMS you even got a nice warning message:
Probably you are wondering why is that – well, as you know, the clustered index is actually containing the table data in its leaf level, so if you disable it, the SQL Server Query Optimizer will not be able to use this very index for creating a plan to access the data, which ultimately prevents the access to the table itself.
This is a very simple approach to generate mass script for changing database owner of all user databases. Why would you need that? Well there are couple of reasons – you might have mass detached\attached all user databases (in an upgrade scenario for example) or you might have restored numerous databases on a brand new instance. Then for sure you will not have the ‘sa’ as owner of the database. In this case, use the below script to get the ownership fixed. Note that it only works for SQL Server 2005 and above!
select 'ALTER AUTHORIZATION ON DATABASE::[' + name + '] TO [sa]' from sys.databases where database_id>4
Microsoft CSS team released a new post of their learning series on SQL Server 11 HADRON. In this article they share how is the synchronous commit request processed and what is behind the scenes of that process. You can read the article here.
CSS SQL Escalation Services are continuing their learning series on SQL Server 11 HADRON. In their latest article they write about page level repair and its enhancements in the latest release. It also shows and some new features of the SSMS GUI introducing the “Resource Assistant” and the colorful timeline thingy 
The article can be read here. Enjoy! 
Following is a video of a step-by-step setup of SQL Server 11 AlwaysOn availability group feature from Gopal Ashoc (Product manager for SQL Server High Availability). The video demonstrates both enabling and configuring the Failover cluster and configuring SQL Server HADR ON with adding databases to the availability group.
You can watch the video on Youtube here.
The goal of automating administration across a large organization is aiming at simplifying the day-to-day job of the DBA and also ease some dataflow tasks between two or more servers.
By definition multiserver administration is administering two or more instances and with it you can do:
Implementing multiserver administration with SQL Server is part of the SQL Centralized Management Solution provided in Codeplex here. Multiserver administration covers the part with Master-Target server of SQL Server Agent Jobs.
Link to the MSDN article on the subject you can find here.
The following step-by-step guide is provided by Microsoft SQL Server CSS engineers based on the CTP version of SQL Server 11 or code-name Denali. The guide can be found here.
I urge you to read it and just note some of the specifics which probably will not be changed upon final release of the product. I am talking about things like HADRON instances are stand-alone instances, you must first enable the feature on service level and creating availability groups. Some of the screens/features/setting might be changed or removed by the time the RTM is released but still this is as much as we can get with the current version.
The latest post from this series discusses running consistency checks on a replica database. Most of you probably have struggled with running DBCC on a production database on regular basis so I have to say this new possibility kind of eases every DBA having a VLDB that has to be checked.
You can read the full article here.
The second article from the series is on new DMVs supporting the HADRON. You can read it here.
As noted in the article, these series are based on the pre-release of SQL Server 11 so take that into consideration when you read it.
