Aug 312013

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.

May 042012

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

Apr 182012

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.
Apr 112012

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 SQL:

Apr 012012

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.

Dec 022011

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 »

Nov 142011

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.

Oct 032011

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
	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 CreateDatabaseSnapshot 'AdventureWorks2008R2_snapshot', 'AdventureWorks2008R2'