All things SQL Server Related....

Finally a whitepaper released regarding virtualising SQL Server.  

This white paper describes a series of test configurations Microsoft ran, which represented a variety of possible scenarios involving SQL Server running in Hyper-V. The paper discusses our results and observations, and it also presents our recommendations. Our test results showed that SQL Server 2008 on Hyper-V provides stable performance and scalability. We believe Windows Server 2008 Hyper-V is a solid platform for SQL Server 2008 for the appropriate workload. It is practical to run production workloads under a Hyper-V environment, as long as the workload is within the capacity of your Hyper-V guest virtual machine.

Link to whitepaper: Running SQL Server 2008 in a Hyper-V Environment - Best Practices and Performance Recommendations.

Cumulative Update 1 contains hotfixes for the Microsoft SQL Server 2008 issues that have been fixed since the release of SQL Server 2008.

Note This build of the cumulative update package is also known as build 10.00.1763.00.

We recommend that you test hotfixes before you deploy them in a production environment. Because the builds are cumulative, each new fix release contains all the hotfixes and all the security fixes that were included with the previous SQL Server 2008 fix release. We recommend that you consider applying the most recent fix release that contains this hotfix.

For more information visit: http://support.microsoft.com/kb/956717/en-us

Please do come by and say HI!! I will be presenting a session on PowerShell integration with SQL Server 2008 at:

11:45-1pm

Where: Parkside 110A, Convention Centre, Darling Harbour 

Link to Presentation: http://cid-895a9e8d1c0f1cd0.skydrive.live.com/self.aspx/Public/DAT361|_spawar.pptx

Link to Poweshell Demo Scripts: http://cid-895a9e8d1c0f1cd0.skydrive.live.com/self.aspx/Public/Powershell|_Demos.zip

Also if you are attending, I would appreciate it if you can nominate me as an expert (of course thats if you think I am one :) ) To do this:

• Log into CommNet, ask a question and nominate you as the expert.

Note: There are incentives (little giveaways) during my presentation so if you are at Teched 2008, Sydney please do come by - who knows you may get something :).

The ninth Cumulative Update for SQL Server 2005 SP2 is available and it contains 40 fixes. For further information refer to the following KB article - http://support.microsoft.com/kb/953752/en-us

The version of SQL Server for this build is 9.00.3282.

After enabling TDE take a backup of the transaction log, as the current transactions in the transactions logs do not get encrypted immediately.

If you are looking at conducting a migration to SQL Server from some other platforms (as you should be :)) then you need to look at the newly released Migration Assistant tools:

Sybase

New! Download SSMA 2005 for Sybase v4.0 - This release has many enhancements in the SQL conversion engine, has a new Tester component, and fixes multiple bugs.
 
New! Download SSMA 2008 for Sybase v4.0 -In addition to multiple improvements on SSMA 2005 V4, this release automates migration to SQL Server 2008.

Access

New! Download SSMA 2005 for Access v4.0 - This release now supports Microsoft Office Access 2007 databases.
 
New! Download SSMA 2008 for Access v4.0 - This release supports Office Access 2007 databases and automates migration to SQL Server 2008
 
Oracle

New! Download SSMA 2005 for Oracle v4.0 - SSMA to automate migration of Oracle database to SQL Server 2005. This release greatly increases the SQL conversion engine in addition to making many improvements across the product.
 
New! Download SSMA 2008 for Oracle v4.0 - In addition to multiple improvements on SSMA 2005 for Oracle V4.0 this release automates migration of Oracle database to SQL Server 2008.
 

The three tools that can help to detect and minimise the threat from SQL Injection attacks are:

• HP Scrawlr
• UrlScan version 3.0 Beta
• SQL Source Code Analysis Tool.

For further information refer to the following security advisory note:

http://www.microsoft.com/technet/security/advisory/954462.mspx

As much as possible, I do not recommend virtualisation of SQL Server or any other RDBMS system, due to the impact on performance, and the availability of other alternatives such as multiple-instances. However following is documentation from RC0 of SQL Server 2008 Books Online disccussing the use of Hyper-V with SQL Server 2008:

SQL Server 2008 is supported in virtual machine environments running on the Hyper-V role in Windows Server 2008 Standard, Enterprise and Data Center editions. The virtual machine must run an operating system supported for the specific SQL Server 2008 edition listed later in this topic.

In addition to resources required by the parent partition, each virtual machine (child partition) must be provided with sufficient processor resources, memory, and disk resources for its SQL Server instance. Requirements are listed later in this topic.²

Within the Hyper-V role on Windows Server 2008, a maximum of 4 virtual processors can be allocated to virtual machines running Windows Server 2008 32-bit or 64-bit editions. A maximum of 2 virtual processors can be allocated to virtual machines running Windows Server 2003 32-bit editions. For virtual machines hosting other operating systems, a maximum of 1 virtual processor can be allocated to virtual machines.

Notes:

·         It is recommended that SQL Server be shut down before shutting down or terminating the virtual machine.

·         Guest failover clustering (configuring failover clustering in SQL Server) is not supported in a Hyper-V environment.

·         Failover clustering defined in the Hyper-V role in Windows Server 2008 (also referred to as Quick Migration) is supported, with SQL Server 2008 running in a virtual machine, when the configuration adheres to all the Hyper-V requirements.

Today I just had a question from someone with regards to SQL Server Change Data Capture and database compatibility level. The questions was will CDC work with databases with compatibility level 80 or 90 on SQL Server 2008. And the answer is.......

YES!!! I conducted some basic testing today with databases with compatibility level 80 and 90 and I was able to use CDC features and capture the changes on a table.

 Another question I get is that can this be used on SQL Server 2005, and the answer unfortunately is NO.....; but there is some good news, you can look at using 2008 transactional subscriber to SQL Server 2005 system and enable CDC to capture the incremental changes that way. This may not be the most optimised way of ahcieving it but could be used in an upgrade/migration process.

REMIX08 registrations are now open for Sydney, 20 May and Melbourne, 22 May. REMIX08 will be packed with demos, Customer presentations and how-to’s for Web Developers and Designers, building next generation experiences through Silverlight. This year's keynote will be delivered by Mark Pesce, from ABC TV’s New Inventors, and those in the internet community will recognise Mark from his writings on freedom, email and free/meshed wireless. Mark is going to provide some thought provoking insights on this web-centric world.

If you a heap (table without indexes) configured for page-level compression, pages receive page-level compression only in the following ways:

• Data is inserted by using the BULK INSERT syntax
• Data is inserted using INSERT INTO... WITH (TABLOCK) syntax
• A table is rebuilt by executing the ALTER TABLE....REBUILD statement with the PAGE compression option

When you do normal inserts (i.e. none of the above), the new rows will not be compressed, unless the rows are inserted on pages that are already compressed.  For instance, suppose you have a heap with 4 pages. Initially, the pages are not compressed. Next you do ‘ALTER TABLE … REBUILD’ and the 4 pages get compressed to 2 pages. Suppose now new inserts happen, and the first insert goes to one of the existing pages. Because the page is already compressed, the new row will be compressed. However, when the two compressed pages get full, a new page will be allocated, which will not be compressed.

To recompress the heap, you can do ALTER TABLE heap REBUILD WITH (DATA_COMPRESSION=PAGE).

The reason we cannot compress heaps during normal inserts, has to do with space reservation for heaps.

So the new records will not be "PAGE" compressed, i.e. it won't be prefix or dictionary compressed, but the values will still be "ROW" compressed. So there is still benefit of the compression but not the full page level compression.

Note: This only affects heaps, if the table is converted to have a clustered index this is not a issue.

Ever wondered whether filestream will be supported with replication? Well before I give the answer to that, let me just give you a brief background on FILESTREAM:

Much of the data that is created every day is unstructured data, such as text documents, images, and videos. This unstructured data is often stored outside the database, separate from its structured data. This separation can cause data management complexities. Or, if the data is associated with structured storage, the file streaming capabilities and performance can be limited.

FILESTREAM integrates the SQL Server Database Engine with an NTFS file system by storing varbinary(max) binary large object (BLOB) data as files on the file system. Transact-SQL statements can insert, update, query, search, and back up FILESTREAM data. Win32 file system interfaces provide streaming access to the data.

FILESTREAM uses the NT system cache for caching file data. This helps reduce any effect that FILESTREAM data might have on Database Engine performance. The SQL Server buffer pool is not used; therefore, this memory is available for query processing.

When to Use FILESTREAM?

Filestream and Replication

Yes replication of filestream data is supported. For more information see BOL topics:  

http://msdn2.microsoft.com/en-us/library/bb895334(SQL.100).aspx#Replication

http://msdn2.microsoft.com/en-us/library/ms186225(SQL.100).aspx

Note: FILESTREAM is not limited to 4GB, it is limited only by the volume size of the files systems, and this applied in replication as well. 

The SQL Server Customer Advisory Team, Best Practices group just released four projects to CodePlex at https://www.codeplex.com/Release/ProjectReleases.aspx?ProjectName=SQLSrvAnalysisSrvcs&ReleaseId=11950

These projects are:

·         SQL Server Analysis Services Performance Monitoring and Visualization Methodology - This methodology collects and processes SQL Server Analysis Services trace files by using SQL Server Integration Services and SQL Server stored procedures. It then utilizes SQL Server Reporting Services reports to visualize performance issues related to MDX queries and processing jobs. This methodology includes components to work with both SQL Server 2005 and SQL Server 2008 CTP6.

·         Compress Many-to-Many C# Utility - This project creates a utility that enables you to easily implement the many-to-many compression technique discussed in the Analysis Services Many-to-Many Dimensions: Query Performance Optimization Techniques best practices white paper. You can download this white paper from the SQL Server Best Practices web site at: http://technet.microsoft.com/en-us/sqlserver/bb671430.aspx. Many thanks to Eugene Asahara for his work on this project.

·         Create a Processing Log Script Project - This project contains a script that enables you to create a system-wide processing log file for monitoring / auditing purposes. It also contains a script that enables you to delete a system-wide processing file. Many thanks to Dave Wickert for his work on this project.

·         Powershell Scripts for SQL Server 2008 Analysis Services DMVs - These sample scripts demonstrate how to use Powershell to query SQL Server 2008 Analysis Services DMV's. The four sample scripts enable you to cancel all queries running longer than a specified length of time, cancel a particular session/SPID/connection, retrieve the Analysis Services version, or return the results of any DMV query. Many thanks to Dave Wickert and Edward Melomed for their work on this project.