ChoirulAmri.org

Virtualization is hot, because we can consolidates disparate servers in one box. It’s easier to manage one rather than multiple physical machines. Many companies run under utilized hardware so It can be optimized with Hyper-V. However, consolidation brings another challenges: there is only one single point of failure. That’s why we need to plan a disaster recovery scenario.

Hyper-V in Windows Server 2008 R2 already supports Live Migration. It means all services and application are still accessible when the migration is happening. This is another bonus of using Hyper-V beside the consolidation and cost saving. Running SQL Server 2008 on top of live migration enabled Hyper-V gives many advantages. When the migration/failover happens, it’s still maintain user sessions and connection.

Another advantage of using Hyper-V instead of failover cluster is It can run on top of commodities hardware. I’m not saying we can use mediocre one, but a standard high performance machine should work. The most important component is storage. We need to have FAST storage. Another tips for performance is a fast network. A Gigabit switch is a must, with multiple NICs on every machine so we can isolate traffic between inter server and application.

In general, below are steps to configure SQL Server 2008 and live migration enable Hyper-V:

1. Configure shared storage (SAN, iSCSI, etc)
2. Connect both nodes to shared storage
3. Install Hyper-V role and Failover Cluster in both nodes
4. Validate cluster configuration
5. Create a cluster
6. Create a Virtual Machine and enable high availability for live migration
7. Install OS and SQL Server 2008 R2 in VM

I did presentation and demos for Singapore CTU in this topic. It was a challenging task because I only had 2 machines, so I had to squeeze 4 VMs. I did not have privilege of multiple NICs so my storage server was up and down. It was a lucky timing that everything run well when I did live migration scenario. The PPT can be downloaded here:

Many thanks to Andrew who helped in Hyper-V cluster setting. He also created step by step storage and cluster configuration. It was presented for Singapore Windows UG:

I was forced to install SQL Server 2005 X64 in my system, because my development DB was X64. Since my machine runs Win 7 X64, I was tempted to install on top of it. Anyway, I don’t have a spare machine, and VPC also does not support X64 OS .

I was thinking that everything should be fine because I did it on my Vista X64 before. But apparently not, after tweak the setting here and there, I was ended up with the following error screen:

I was sure that all pre-requisites has been installed properly, but it was still failed. My first attempt was checking IIS 7 configuration, but everything was fine. The machine passed IIS requirement checking at the beginning of the steps.

I tried the last resort, just checking my luck. Since I saw that SQL Native Client was failed to install, I tried to download the latest SQLNCLI and voila! It was installed successfully after I updated the native client. Moral of the story: Windows 7 X64 RC does not come with the latest SQLNCLI, and the one bundled in SQL 2005 CD is not compatible with Mr. 7.

To sum up, this is the steps to install SQL 05 X64 on Win 7 x64 RC:

Download and install the latest SQL Native client, it’s bundled under SQL 2005 Feature Pack family. I use the one from February 2007 and worked like a magic.

Second, make sure all pre-requisites are installed such as IIS 7 components (ASP.NET, CGI, IIS6 compatibility, etc). This is the case if we want to configure Reporting Services as well.

Once installation finished, update to SQL Server 2005 SP2. Without update, it won’t work because the security policy kicked out Mr. Administrator from SQL sysadmin role .

That’s all, happy testing …

I had a bad experience with transaction log file, it was 8 years ago. But thanks to Mr.Bad, I learnt very important lesson as newbie DBA at that time. This is the story: my ERP application which sitting on top of SQL Server 2000 was fine at Friday afternoon. I went home for a weekend, only to find everybody was starred at me at Monday morning. What had happened? The ERP was crashed, actually it couldn’t start at all.

I went to my SQL Server box, and the guy was stopped. It’s not started. I checked everything from security permission to possibility of virus, nil. Then I found that the disk was out of space because of transaction log explosion. The accounting guy was doing quarterly stock journal in the system, and it caused transaction log growth exponentially. There was an audit table which recording all journal operation, and obviously the INSERT operation was the source of problem.

Moral of the story: make sure you have automated way to manage transaction log growth.

There are 2 way to shrink the log:

• Detach database, delete the big log, and re-attach without log file. SQL Server will create a new, fresh log file with minimum initial size. It is 2MB by default. Disadvantage: the database will not be available during the process. Offline duration depends on how fast is the detach – attach operation takes time.
• Better approach: use SHRINKFILE of DBCC command. This is better because I do not need to bring the system OFF.

Regardless of the approach, make sure to do full database backup. Shrinking the log deletes historical transaction that may be needed to restore database to specific point of time. Just in case, make sure to do full backup. You have been warned right .

These are the steps to shrink a huge log file:

First, obtain the log filename, simply use sp_helpdb as follow:

sp_helpdb 'AdventureWorks'

It returns all files in AdventureWorks:

Then shrink AdventureWorks log file to 2 MB size:

USE AdventureWorks
GO
DBCC SHRINKFILE (AdventureWorks_Log, 2)
GO

The outcome is 20 GB log file has been shrunken to 2 MB:

I usually run DBCC SHRINKFILE on scheduled basis, by attaching SQL script to SQL Agent job. This is easier and painless .

I never think that this is a difficult or exceptional. I never configure it before, because my job does not really require to deploy such configuration. Someday in the morning, one student asked my this question: Why I cannot run Reporting Service 2005 on my Vista? This guy used Vista as his development machine and SQL Server was running fine except the SSRS.

Well, I saw some documentation so I’m sure there is away to do it. The problem is not Vista specific. This is IIS 7 matter instead of Vista. Vista runs IIS 7, and so does Win Server 2008. IIS 7 has several breaking changes compare with IIS 6, and some developers do not really know how to deal with some changes.

I decided to configure it on my Vista 64 bit desktop and worked like a charm. Nothing special, just couple of IIS 7 configurations and security setting. Here are the steps:

Step one: Make sure the Vista machine already upgraded to SP1. In Win Server 2008, It comes with SP1 by default.

Step two: Configure role services on IIS 7. This menu is accessible from IIS 7 setting or Control Panel > Program and Features. Make sure that the following features are activated:

IIS 6 Management Compatibility:
IIS 6 WMI Compatibility
IIS Metabase and IIS 6 configuration compatibility

Application Development Feature:
ASP.NET
ISAPI Extensions
ISAPI Filters

HTTP Features:
Default Document
Directory Browsing
HTTP Errors
HTTP Redirection
Static Content

These are the screen capture in my Windows Server 2008 machine:

The next step is to install SQL Server 2005, including Reporting Services. proceed with SQL Server SP2 after that. After the installation finished, browse http://localhost/reports. The reporting services should run properly.

However, I noticed there are several small issues that may varies from machine to machine. If you got trouble to load SSRS after the above procedure, do some checking as follows:

Make sure the Reporting Services web sites (both reports and report server) run on top of the classic mode application pool. IIS 7 comes with 2 mode to run the pipeline: classic and integrated. The application pool for SSRS has to be in classic mode.

The complete step by step guide from Microsoft is here.