No More SQL Masters

It’s so sad to say that there will be no more Microsoft Certified SQL Server Master, Architect, and Solution Master any more after October 1, 2013. We all received this email from Microsoft. If you are still keen in getting the highest technology certification, September this year will be your last chance. It’s also your last chance to stay in this exclusive and highly technical community. Otherwise, waiting for evolved certification in the future and/or joining millions of MCSEs will be your choices. Although it will be retired very soon, I still believe all certified masters possess their expert level knowledge on SQL Server. They have been spent tremendous time on learning SQL Server, practicing, and resolving real life problems. Great experience attending 3 week training session at Microsoft Redmond. Thanks for all my instructors.

Great Place to Learn SQL Server 2012

Here is the place I found for the people who are willing to learn new features of SQL Server 2012.

http://social.technet.microsoft.com/wiki/contents/articles/6982.sql-server-2012-developer-training-kit-bom-en-us.aspx#Module_4_SQL_Server_2012_Database_Server_Programmability

http://blogs.msdn.com/b/oneclickbi/archive/2011/12/27/more-demos-of-power-view-available.aspx

Installing Online Document for SQL Server 2012

Prior SQL Server 2012, you have the option to install Book Online for SQL Server. In SQl Server 2012, the option is still there, however, selecting this option will only install the document viewer rather than the library. You will have to take more steps to have the document downloaded. Here are the steps you can take to install the library.

By Default, libraries will be downloaded and installed in C drive. If this is not the location you want, you may want to follow steps here to change the path.

  1. Open SSMS, go to Help, and select Manage Help Settings. Help Library Manager will be opened
  2. Choose Install Content From On Line. The Help Library Manager will retrieve available libraries online.
  3. Scroll down to SQL Server 2012 and click on “add” for items Books OnLine, Developer Reference, and Installation.
  4. Click on Update.

Name MCSE is Back

New certifications are available very soon. They are MCSA – Microsoft Certified Solution Associate, MCSE – Microsoft Certified Solution Expert, MCSM – Microsoft Certified Solution Master, and MCA – Microsoft Certified Architect (stay the same).
Overview: https://www.microsoft.com/learning/en/us/certification/cert-overview.aspx
Overview SQL: http://www.microsoft.com/learning/en/us/certification/cert-sql-server.aspx

Proud of Being A MVP

This morning, as a routine of my regular day, I checked my emails and found a very special one which is from MVP Program. It says

Dear John Huang
Congratulations! We are pleased to present you with the 2012 Microsoft® MVP Award! This award is given to exceptional technical community leaders who actively share their high quality, real world expertise with others. We appreciate your outstanding contributions in SQL Server technical communities during the past year.

I am proud of being a MVP and I appreciate this recognition of all my work done in our SQL Server Community. I would like take this opportunity to thank all professionals and MVPs here who brought me into the community, provided opportunities to let me grow, nominated me, and also ones who attended my events, read my blog, and asked me questions from various locations. Thanks for your continuous support.

sp_rename Causes Lock Leaking

One of my friends finds this issue in the systems he is managing. In my locking and blocking series, I mentioned that when a record is being modified it always acquires IX lock on the object, IX lock on the page, and X lock on the record. In rare case, this may also hold IX lock on another object. This is cased by sp_rename.

Continue reading “sp_rename Causes Lock Leaking”

SQL Mail Removed in SQL Server 2012

SQL Mail is removed from SQL Server 2012 as Microsoft promised in SQL 2008’s BOL. Configuration “SQL Mail XPs” is removed from SQL 2012, check sp_configure in SQL Server 2012. Supporting procedures are removed as well. Those procedures are removed from master database xp_startmail, xp_stopmail, xp_findnextmsg, xp_readmail, xp_deletemail, xp_sendmail, and sp_processmail. Ensure your SQL Server applications do not use them anymore. If do, have those contract procedures handy before upgrading SQL Servers to 2012.

A Procedure for Creating Database Snapshot

Database snapshot is a great SQL Server built-in feature serving many situations for programmers and DBAs to quickly create a read only copy of a operational database. Sometime, creating a database snapshot is a daunting work especially when you have hundreds database files. Here I give you the code to generate database snapshot automatically with few parameters regardless number of files the database has.

create procedure CreateDatabaseSnapshot
(
	@SnapshotName sysname,
	@SourceDatabaseName sysname = null
)
as
begin
	set xact_abort on
	select @SourceDatabaseName = ISNULL(@SourceDatabaseName, db_name())
	declare @SQL nvarchar(max), @Proc sysname = quotename(@SourceDatabaseName)+'..sp_executesql'
	declare @t table(name sysname, physical_name sysname, i int identity(1,1))
	insert into @t
		exec @Proc N'select name, physical_name from sys.database_files where type = 0'
	select @SQL = 'create database '+ QUOTENAME(@SnapshotName) + ' on '
	select @SQL = @SQL + '(name = '+QUOTENAME(name)+', filename = '''+physical_name+'.'+@SnapshotName+'''),'
	from @t
	where i>1
	select @SQL = @SQL + '(name = '+QUOTENAME(name)+', filename = '''+physical_name+'.'+@SnapshotName+''')'
	from @t
	where i=1
	select @SQL = @SQL + ' AS SNAPSHOT OF '+QUOTENAME(@SourceDatabaseName)+' ;'
	exec(@SQL)
end
go

Usage:

exec CreateDatabaseSnapshot 'AdventureWorks2008R2_snapshot', 'AdventureWorks2008R2'