When you are hit with a problem in SharePoint 2010 you get notified to use a correlation id to check for further details in the trace logs (ULS logs).

So you hopefully start up ULS Viewer and open up the relevant logs and search like a mad man after this specified correlation id. Sometimes you don’t find the provided correlation id in the log file you opened. Is there a easier way?

Yes and that is to use the SharePoint PowerShell command Merge-SPLogFile. According to the description of Merge-SPLogFile on Microsoft TechNet:

“Combines trace log entries from all farm computers into a single log file on the local computer.”

Let me explain how you can use this powerful command.

Basic Usage

Lets visualise a simple farm.

As the example Farm image above we have two frontend servers, application server and database server. If I wanted to consolidated the last hour of SharePoint log entries from the front-end server and application server I would open up SharePoint Management Shell on one of the servers and type the following command:

Merge-SPLogFile –Path “E:\SPLogs\MergedLogs.log” –Overwrite

Now that was a very basic example to merge the various servers log files. More parameters are available on the Microsoft TechNet article for Merge-SPLogFile. For example Area, Category, Level, Message, StartTime, EndTime and more.

Finding Correlation ID

What the following example show is how to find the log entries for a specific correlation id:

Merge-SPLogFile –Path “S:\SPLogs\MergedLogs.log” –Correlation 3ae2a6c0-da14-43a1-afda-5bb6bbff3d43 -Overwrite

Since the log files are merged with just the specified correlation id you can load the log file in ULS Viewer to easily view the entries. Please be aware that the log can be empty if the logging level is set to low for the SharePoint category. Just go to SharePoint Central Administration to increase the logging level.

As you can see in the image above the correlation id has 19 matching entries in the log file. Much less log entries to scroll through.

Summary

I hope this helps you on finding SharePoint errors quicker and easier. Let me know what your favourite commands and tools are for debugging SharePoint.

@cecildt

As of SQL Server 2012 it is easier to slipstream the latest service packs and cumulative updates to create new SQL Server installations. It is a slow process to first install SQL Server and then apply the latest updates afterwards.

The Usual Approach

The usual approach to slipstream SQL Server 2008 and SQL Server 2008 R2 has been documented on MSDN blogs where you needed to download update packages for all architectures and extract to a directory and copy over to the original SQL Server Setup files. Make changes to the configuration files to specify the updates and then proceed with the installation of SQL Server. It was basically allot of steps to do.

The Change Approach

Microsoft decided to change the way updates are applied with SQL Server 2012 installations. The new approach is much better. The change is documented on MSDN article about “Deprecated SQL Server Features of SQL Server 2012”.

The steps is as follow. Download the latest cumulative update for example CU2 for SQL Server 2012. Then you need to run the downloaded file to extract to a directory C:\SQLUpdates for example.

The CU2 download filename is “449398_intl_x64_2012_SQL_CU2.exe” and when you run the file it exact an file with the name “SQLServer2012-KB2703275-x64.exe”. You do not have to extract the the file again.

Now open command prompt and navigate to the original SQL Server 2012 setup files. In command prompt type the following command to start the setup:

Setup.exe /Action=Install /UpdateEnabled=TRUE /UpdateSource="C:\SQLUpdates"

The additional parameters are self explanatory, but lets go through them:

• Action = Specify that we want to perform an install of SQL Server 2012.
• UpdateEnabled = Specify that the setup should include updates in installation.
• UpdateSource = Specify location where updates are available

After running the command you will see the default install screens for SQL Server 2012. After the setup checks screens you will see the Product Updates screen that list available updates in the specified directory.

That is basically it. The SQL Server Setup will only use the updates that are available in the specified directory and automatically extract them. You don’t have to download all the available architectures, just the ones that you need.

Summary

In summary you can see that the new approach is much easier than before. You can still use the ConfigurationFile.ini and DefaultSetup.ini to specify available updates as well. I hope this change encourage you to always stay up to date with the latest service packs and cumulative updates as they come out.

@cecildt

I was busy setting up a new VM with SQL Server 2012 with the latest cumulative update (Build: 11.0.2325.0) and all the features enabled for installation. On the end of the installation summary page it show that SQL Server Analysis Services failed. The installation did install the Analysis Service but viewing the SQL Server Services in SQL Server Configuration Manager it show the Analysis Service did not start, that is why the installation show failed.

Problem

I went and look into Windows Application Event Viewer for further details and the following error has been logged:

Message-handling subsystem: The message manager for the default locale cannot be found.
The locale will be changed to US English. Message-handling subsystem: The message manager for the default locale cannot be found. The locale will be changed to US English. File system error: The following error occurred during a file operation: Access is denied. . (\\?\E:\Data\CryptKey.bin).

At first I thought there is some server locale issue. As you can see at the end of the message there is an “Access is denied” issue. Which is related to permissions to some resource. To understand why I get this error I need to explain my server setup.

The newly created VM has a C drive and an additional E drive. As you know the C drive is for OS and program files. The E drive is where I put all the SQL Server databases files and folders. On the E drive I created the following directory structure:

For each SQL Server Service I create dedicated accounts:

• SQL Engine Service: sql_eninge_service
• SQL Analysis Service: sql_analysis_service
• SQL Reporting Service: sql_report_serivce
• SQL Agent Service: sql_report_service

Solution

You get the picture. Now back to the “Access is denied” error. I had to give the SQL Analysis Service account write access on E:\Data and E:\Logs directory and then the SQL Analysis service started up successfully.

Summary

In summary if you create your own service accounts and have your own predefined folder structure you can get permission errors from SQL Server. Always keep your eye on Windows Application Event Viewer to see any SQL Server permission errors and give the right permissions to each service account.

Cheerio!

I had a issue with SQL Server Agent Service that cannot start up after creating a new clean installation of SQL Server 2012 with the latest cumulative update (Build: 11.0.2325.0). My first step was to look at trusty old Windows Application Event Viewer to see what messages get logged when I try to start the SQL Server Agent Service.

Problem

In Windows Application Event Viewer the following error message is logged:

OpenSQLServerInstanceRegKey:GetRegKeyAccessMask failed (reason 2).

The problem is that the default account that is created by the SQL Server installation that is used by the SQL Server Agent Service does not have the correct permissions.

Solution

The easy solution is to use the SQL Server Configuration Manager to change the SQL Agent Service account to Local Service and then the SQL Agent Service will start up successfully.

My preferred approach is to create a dedicated domain account for example: sql_agent_service. Assign this account to the SQL Agent Service via SQL Server Configuration Manager. When you start the SQL Agent Service you will see that the service start up successfully.

If you go back and view the Windows Application Event Viewer you will see the error again, but additional messages are logged where an advance configuration setting has been automatically changed in SQL Server 2012. The Agent XPs configuration option has been enabled which allows the Agent Service to start up successfully.

To make sure the error is gone, clear the Windows Application Event Viewer. Stop and start the SQL Server Agent Service and make sure there are no errors logged again.

By default the advance Agent XPs configuration option is disabled on new installations. You should enable it to make sure the SQL Agent Service work correctly.

Here is the TSQL to enable Agent XPs manually:

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Agent XPs', 1;
GO
RECONFIGURE
GO

Summary

In summary you should define dedicated accounts for each SQL Server Service up front before you install SQL Server 2012. This will allow you to know which account has permission issues when a SQL Server service cannot start up successfully. Always keep your eye on the Windows Application Event Viewer for SQL Server errors!

Cheerio!

Virtualization of SQL Server these days are becoming more a reality and common practice. Usually if you you mention SQL Server and Virtualization in one sentence people will look at you as if you are a mad man.

With this post I would like to talk about a couple of notes you need to consider when you virtualize SQL Server. When using a hypervisor to consolidate your SQL Servers into a virtualization environment you need to think about the impact on CPU, Memory, Storage and Network performance and management.

Let’s break it a bit down further with some checkpoint.

CPU

• Always ensure that you disable power management for CPUs in the BIOS of the physical servers. You want the full power of your CPU available at all times.
• In your Guest VMs make sure that the Power Management Plan is set to High performance.
• The different levels of CPU cache has a big performance impact. Try to use the latest CPUs that have big level 3 cache. Level 3 cache in the latest Intel CPU are shared among the individual cores. This allow the hypervisor to reuse the same CPU to schedule work among the cores for your SQL Server virtual machine where certain SQL Server data is cache.
• Assign minimal virtual cores to your SQL Server virtual machine and scale up the CPU count as required when workload increases. This allow the hypervisor the reuse the same CPU socket where certain data can remain cache rather than distribute across all available CPU sockets where you will be hit by cache misses.
• Monitor for throttling of the CPU.
• Avoid affinity masking.
• Use CPU-Z to make sure your CPU is performing optimally.

Memory

• From your hypervisor reserve memory to your SQL Server virtual machine. This allow other virtual machines not to consume your memory and allow the Balloon driver not to force your SQL Server virtual machine under memory pressure to release memory.
• Set your SQL Server minimum and maximum memory according to your reserve memory. Don’t assign all available memory you must leave some head room for the Windows Operating System.

Storage

• Still apply the same best practices for SQL Server database files.
• Try and stick to RAID10.
• You can use Pass-Trough disks in your hypervisor. Which give you slightly better performance than virtual disk drives.
• You can use Shared or Dedicated LUNs via your hypervisor. Depended on your business requirements.
• Correctly configure multi-pathing on your SAN.
• Avoid 1GB iSCSI for storage, try going higher.

Network

• Assign dedicated Network Card interfaces via you hypervisor to the SQL Server virtual machine.
• Make sure good practices are followed for network redundancy.
• Avoid multiple virtual machine using the same network interface.

These checkpoints are some of the common practices to follow when setting up SQL Server in a virtualization environment. Virtualization setup comes down to what you can afford and what your business requirements are.

The key areas to successful deployments are:

1. Always use the latest version of the vendors hypervisor.
2. Follow the vendor’s best practices.
3. Always and always monitor the physical environment and guest virtual machines for optimizing the performance.

Microsoft Hyper-V

The Microsoft Hyper-V hypervisor does have its own best practices for deploying SQL Server. One feature that you should try and avoid is dynamic memory. You should always use static memory assignment for SQL Server virtual machines.

Here are some documents to review if you are going to use Microsoft Hyper-V.

1. Running SQL Server with Hyper-V Dynamic Memory Best Practices and Considerations
2. Running SQL Server 2008 in Hyper-V Best Practices and Performance Recommendations
3. High Performance SQL Server Workloads on Hyper-V

VMware vSphere

The VVMware vSphere hypervisor has its own best practices for deploying SQL Server. A very nice feature that vSphere has is memory compression. This feature is where multiple pages in memory that are the same is removed from memory. Only keeping one copy in memory. This is a great benefit for Operating System pages in memory, but no real benefit specifically towards SQL Server. 

Here are some documents to review if you are going to use VMware vSphere.

1. Availability Guide for Deploying SQL Server on VMware vSphere
2. Performance and Scalability of SQL Server on VMware vSphere
3. SQL Server on VMware Best Practices Guide

Conclusion

On the end there are allot of guidance from the individual vendors for optimal deployment of SQL Server in an virtualization environment. There are many benefits like cost savings and management in using virtualization. I would really recommend that you try setup a test environment to see the benefits.

Performance is always a concern, but at the rate at which hardware performance are increasing the little bit of performance impact you experience right now with virtualization will be a thing of the past with new hardware. Especially with enterprise SSD disk drives that are coming out at a good price.

One person that I follow that provides brilliant content on SQL Server virtualization is Brent Ozar. He has many articles and videos on virtualization.

If you have any comment or questions about SQL Server virtualization please feel free to contact me.

Cheerio!

Today is the start of a very exciting and challenging journey to become a Microsoft Certified Master on SQL Server 2008. This post is about my planning on achieving the certification.

The Motivation

Over the years I have worked in various environments that involved development with SharePoint, BizTalk, Integration, Web and custom development. All of them had one thing in common and that is they all use SQL Server. I always enjoy and have allot of passion working within SQL Server. Always learning the little bits about the ins and outs of SQL Server is fun. Working with SQL Server all over the years came to a point that I ask myself  how can I improve myself to be better at what I do and know. That has put my focus on achieving the Microsoft Certified Master on SQL Server 2008.

The Schedule

How am I going to achieve this and by when? My personal schedule is to write the MCM on SQL Server Knowledge Exam in the first week of May 2012. If I pass that exam then it will onto the MCM on SQL Server Lab exam. From today it leaves me with roughly 3 months for studying.

As mentioned by other MCMs that studying alone is not enough. You do need years of experience working with SQL Server.

The Resources

Where are the resources? There are various books, whitepapers and blogs that needs to be studied that will help with the exams. On the official page and from SQLskills.com you get links  to the recommended resources. There are also training programs available that can be attended for preparation to the Microsoft Certified Master on SQL Server 2008 exams. 

The OneNote

There are so many resources available on the internet that it can be a big tasks to get these resource organized. I took the time in my planning to organise the resources into a Microsoft OneNote document. You can download it from here.

The OneNote document is divided into sections namely:

• Videos
• Stairways Series
• Books
• Blogs
• SQLSkills Training

The videos section includes the links to the recommended readiness videos for Microsoft Certified Master on SQL Server 2008. The Stairways Series section is links the various topics about SQL Server that is provided by SQLServerCentral.com. The Books section has links to the recommended books for reading. The White Papers section are the whitepapers that must be read for the exams. The Blogs section is links to some additional reading for the exam. Lastly the SQLskills Training section has been broken down to sub sections with the various resources links that is mentioned on SQLskills MCM page.

If you are also going to do the Microsoft Certified Master on SQL Server 2008 exams I hope this document can help you on your journey. Also if you find resources that I might have missed let me know and I will update the document.

Again here is the full link to the document:  http://dl.dropbox.com/u/4406115/MCM%20SQL/SQL%20MCM%20Training.one

Conclusion

In conclusion as I mentioned in the beginning of this post, it is going to be a very exciting and challenging journey to be come a  Microsoft Certified Master on SQL Server 2008. I will continue to put up posts as my learning progresses on the topics of SQL Server. For those who also attempting these exams as well, best of luck and see you at the other end.

Cheerio!