Formula1 is the pinnacle of motorsports – a test of performance, integrity & robustness. The cars on the race track are technological marvels – they contain technologies that would build the cars of the future – technologies like KERS to harness braking energy for a power burst and DRS to reduce drag being the latest additions to the list this year.

I am a fan of the sport, but little did I know that the subject of my passion – Microsoft SQL Server – is closely associated to it.

A modern F1 car uses a lot of embedded electronics. It has an onboard Electronic Control Unit (ECU) that connects to 100+ onboard sensors which monitor and control critical operations like engine configurations, transmission, pit-lane communications and a lot more. As the car moves along the track at around 200miles per hour, it generates and transmits data to the engineers in the pit-garage at a rate of 100kilobytes to 0.5megabytes per second – totaling to about 2GB/race.

This streaming data is analyzed in almost real-time by the engineers who, with using historical data provide near instant analysis help shape the race strategy and ultimately lead a driver to become a World Champion and earn a Constructors trophy for a manufacturer.

The Case Study

Together with its technical partner Microsoft, McLaren Electronics is the official supplier of engine control units (ECUs) and associated data systems to all of the teams competing in the Fédération Internationale de L’Automobile Formula One World Championship annually between March and November.

Business Problem: “McLaren Electronics needed to replace file-based storage with a relational database for the more than 2 terabytes of engine control unit (ECU) data collected each year by a typical Formula One racing team.”

Solution: “The company is using the FILESTREAM feature of Microsoft® SQL Server® 2008 to store ECU data in a relational database while providing rapid access to the data streams for users.”

How SQL Server helps analyze F1 Racing Data: http://www.microsoft.com/casestudies/Case_Study_Detail.aspx?CaseStudyID=4000001476

How SQL Server helps access huge data stores for F1 racing teams: http://www.microsoft.com/casestudies/Microsoft-SQL-Server-2008-R2-Enterprise/McLaren-Electronic-Systems/Firm-s-Solution-Accelerates-Access-to-Huge-Data-Stores-for-Formula-One-Race-Teams/4000009547

A brief video from Microsoft Showcase: http://www.microsoft.com/showcase/en/us/details/be728b1e-7f78-470f-be7e-4003b023ff1c

This week-end…

This week-end (September 09-11, 2011), when you enjoy the race at Monza in Italy (are you a Ferrari fan? If yes, this is your home race!), do not forget that deep inside the pit-garages are many instances of Microsoft SQL Server crunching huge amounts of data that help shape the race, and bring tomorrow’s technology on the road, today.

By the way, how many of you are coming over next month to the first ever Grand Prix of India at the Buddh International Circuit at Delhi, India?

Finally, remember that your road to/from work is not a Formula1 race-track. Until we meet next time,

Be courteous. Drive responsibly.

Microsoft SQL Server is home to many myths and legends, and I have developed an interest in knowing about them. It is really a very interesting exercise to know about these myths, and prove them incorrect if the situation permits.

The Myth

One such myth is that a table name in a Microsoft SQL Server database cannot be the same as the database name.

The application that I work on does not have such a scenario. I had never ever heard about anything remotely similar, and therefore, I had to investigate this one. So, let’s see if this one is true or not via a simple DIY ("Do It Yourself") test.

The DIY Test

I have always believed that a simple test can go a long way in clearing out the concepts. So, let’s begin by connecting to a SQL Server 2008 R2 instance, and checking if SQL Server allows us to create a table of the same name as a database.

/*
    WARNING - THIS CODE IS PRESENTED AS-IS AND WITHOUT WARRANTY.
              PLEASE DO NOT USE IT IN PRODUCTION/QA ENVIRONMENTS
*/
USE AdventureWorks2008R2
GO

CREATE TABLE AdventureWorks2008R2 (MyKey INT)
GO

Next, let’s insert a few values:

/*
    WARNING - THIS CODE IS PRESENTED AS-IS AND WITHOUT WARRANTY.
              PLEASE DO NOT USE IT IN PRODUCTION/QA ENVIRONMENTS
*/
USE AdventureWorks2008R2
GO
INSERT INTO AdventureWorks2008R2 VALUES (1), (2), (3)
GO

Finally let’s attempt to select from this new table followed by necessary cleanup:

/*
    WARNING - THIS CODE IS PRESENTED AS-IS AND WITHOUT WARRANTY.
              PLEASE DO NOT USE IT IN PRODUCTION/QA ENVIRONMENTS
*/
USE AdventureWorks2008R2
GO
SELECT * FROM AdventureWorks2008R2
GO

--Cleanup
DROP TABLE AdventureWorks2008R2
GO

Surprised? Don’t be. It’s perfectly legal to have a table name same as the database name.

So, what’s going on here?

Great question! That’s what one should be after.

A table is ultimately a database object. The database object name is referred to as its identifier. If we look into MSDN for the rules of naming identifiers (http://msdn.microsoft.com/en-us/library/ms175874.aspx), we see that the rules for naming database objects can be summarized as under:

1. First character must be one of:
   • A Unicode letter (Latin characters from a-z, A-Z and characters from other languages)
   • The Underscore ("_"), at sign ("@") or the number sign ("#")
2. Subsequent characters can be
   • Letters
   • Decimal numbers
   • Dollar sign ("$"), Number sign ("#") or underscore ("@")
3. The identifier must not be a Transact-SQL reserved word. SQL Server reserves both the uppercase and lowercase versions of reserved words
4. Embedded spaces or special characters are not allowed
5. Supplementary characters are not allowed

Nowhere is it mentioned that identifiers cannot be the same as the database name.

NOTE: When identifier names conflict with reserved words, they must be enclosed in square braces. So, a table name like "TABLE" is illegal, while "[TABLE]" is perfectly legal.

Special Thanks to…

This post has been inspired from Pinal Dave’s (blog) series – SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – Efficient Query Writing Strategy – Day 4 of 35

Until we meet next time,

Be courteous. Drive responsibly.

It is a well-known recommendation and best practice that the transaction log of any database must be on a drive different than the data files are on. This is especially useful to improve transaction log file performance, which manifests itself as a high value for the LOGBUFFER wait type. Refer Pinal’s post (blog) on the LOGBUFFER wait type here. Pinal has demonstrated this at multiple community meets (Tech Ed 2011, CTD – June 2011) and every time he performs the demo, the crowd erupts in admiration.

So, all’s well. But, the question that kept coming back to me was – WHY? Why does moving the transaction log to it’s own dedicated drive benefit the performance of the SQL Server?

To understand the WHY behind this best practice, it is imperative for us to understand the differences in the physical architecture of the transaction log and the data files.

SQL Server uses something called as a Write Ahead Log (WAL) mechanism. What this means is that even before data is persisted to the disk/data file, data is written to the transaction log. When data is written to a database, it moves from the memory (where the manipulation happened) to the transaction log. Later, when background check-pointing happens, this data is written from the log to the data file. Therefore, the data file performance does not directly affect the throughput of the database. The transaction throughput of the database ultimately depends upon the performance of the transaction log.

Since users can read or write any data from the data files, the read & write activity is essentially random in nature. Physically, the read-write heads inside of the disk are jumping around all over the place moving from one sector to another randomly – which slows down the drive, reducing the throughput.

The transaction log on the other hand, is written to serially. This is one of the reasons why instant file initialization cannot be used for transaction logs (refer Paul Randal’s post here), but that’s a different story. Because the transaction log is written to serially, and read from only during check-pointing, a log backup or a restart recovery, it is much more beneficial to place the transaction log on a drive that does not need it’s heads to move around randomly.

This is why moving the transaction log to it’s dedicated drive benefits the SQL Server performance wise.

You can read more on the physical architecture of the transaction log in Books On Line at: http://msdn.microsoft.com/en-us/library/ms179355.aspx

Now that I understand the reason why this arrangement works, I feel much more confident in implementing the same in my development, quality assurance and production environments.

Until we meet next time,

Be courteous. Drive responsibly.

Recently, a friend told me that they were experiencing performance issues in production when importing data from 3rd party interfaces at the end of the day. The entire import was done via BCP and at a time when no users were logged into the system. Also, since it was a disconnected import, the entire import could be repeated again in case of any errors or issues.

After some discussion and research on MSDN, we came across the following page: Considerations for Switching from the Full or Bulk-Logged Recovery Model. Sure enough, the issues were resolved by a simple process change and addition of less than 10 lines of T-SQL code his end.

The Secret of faster Bulk operations

At this point, allow me to mention that detailed discussion on recovery models within SQL Server is out-of-scope of this post. Please refer Books-on-line for more details on each recovery model.

What the MSDN page suggests is that while FULL recovery model is ideal for normal usage, the BULK_LOGGED recovery model might help speed-up large bulk imports. So, the sequence that BOL recommends is:

1. Under the FULL recovery model, take a log backup (in addition to the normal scheduled backup)
2. Switch to the BULK_LOGGED recovery model
3. Undertake the bulk operation
4. Switch back to the FULL recovery model
5. Take a log backup (in addition to the normal scheduled backup)
6. Database available for normal operations

While under the BULK_LOGGED recovery model, the scheduled database, log and differential backups can continue to occur.

The secret lies in the fact that under the BULK_LOGGED recovery model, some operations are minimally-logged whereas the same operations are fully logged under the FULL recovery model. The fact that these operations are minimally logged means that the bulk operation would complete faster under the BULK_LOGGED model than under than the FULL recovery model.

These most commonly used operations which can benefit from the BULK_LOGGED recovery model are bulk import operations (BCP, SELECT…INTO, BULK INSERT and INSERT… SELECT).

IMPORTANT:

While following these steps it is strongly recommended that:

1. No users be there in the system
2. No modification be made that cannot be recovered without a log or a full database backup. Simply re-executing the bulk operation should be sufficient to restore the database from last backup if necessary

What this would not benefit

There are many myths & legends that surround SQL Server. One of them is that switching to the BULK_LOGGED recovery model would speed up log shipping because the transaction log file would be small. This myth has been busted here – http://blogs.msdn.com/b/sqlserverfaq/archive/2011/01/07/using-bulk-logged-recovery-model-for-bulk-operations-will-reduce-the-size-of-transaction-log-backups-myths-and-truths.aspx

I hope that the above has helped you in setting up your bulk import processes.

Until we meet next time,

Be courteous. Drive responsibly.

Data is one of the most critical and valuable components for any industry and it’s protection is every individual’s responsibility. Data lives within Microsoft SQL Server, and therefore, it is the duty of DBAs to ensure that their SQL Server is safe from unwanted detection and intrusion.

Today, I will demonstrate a method by which you can isolate your SQL Server instance from unwanted detection. I believe that this is a best practice and must be followed for all production systems.

SQL Server Configuration Manager

Per Books-On-Line,

• SQL Server Configuration Manager is a tool to manage the services associated with SQL Server, to configure the network protocols used by SQL Server, and to manage the network connectivity configuration from SQL Server client computers
• It is basically a Microsoft Management Console (MMC) snap-in and for SQL 2008 utilizes the SQLServerManager10.msc snap-in component
• System administrators: please note that the configuration manager uses WMI to monitor and change SQL Server settings

Managing Protocol access

After all, client connectivity to access, manipulate and store data is all the SQL Server is about. SQL Server Configuration Manager allows you to configure server and client network protocols, and connectivity options.

The SQL Server configuration manager allows you to control how applications on the network access your database – what protocol is to be used, whether remote connections are allowed or not and also control the IP addresses & ports that SQL server would listen on for incoming requests. However, there is one key aspect that can be controlled when configuring protocols – the discovery of an SQL Server instance on the network.

How applications “discover” SQL Server?

Applications on a network discover a SQL Server instance by sending out something called as an “enumeration request”. If a SQL Server is visible on the network, the SQL Server Browser service would respond to this enumeration request and allow the SQL Server instances on the server to be listed amongst the list of available instances to connect to.

Hiding a SQL Server instance

In a production environment, you may want to hide the production instances of SQL Server to avoid detection. Essentially, all that needs to be done is to instruct the SQL Server Browser not to respond to an enumeration request for that particular SQL Server instance. Here are the brief steps on how you can go about achieving this objective:

1. Launch the SQL Server Configuration Manager
2. Expand the node for “SQL Server Network Connection”
3. Browse out to “Protocols for <<SQL Server instance name>>”
4. Right-click and go to “Properties”
5. 
6. In the “Protocols for <<SQL Server instance name>>”, choose “Yes” from the drop-down to hide this instance from detection over the network
7. 

As you can see that in just 5 steps (2 are images, so they don’t count) you can hide your SQL Server. Users and applications can still connect to this instance normally, but anyone attempting to enumerate through the network for a list of available SQL Servers would not find this instance on their list.

Other “How-to” topics

You can do a lot with the SQL Server Configuration Manager. You can find a list of common operations, and how to go about them on the Books-On-Line page here: http://msdn.microsoft.com/en-us/library/ms188707.aspx

By the way…

I have often seen developers using their trusted Windows Services applet to restart the SQL Server service. This was acceptable till SQL 2008 came about. Starting SQL 2008, this practice should not be followed.

This is because SQL Server uses a service master key for all it’s operations and this key needs to be regenerated upon service restart. These keys are not re-generated if the services are restarted using the Windows Services applet. The SQL Server Configuration manager is the right place to go to if you need a SQL Server service restart.

Until we meet next time,

Be courteous. Drive responsibly.