Microsoft SQL Server 2008: What's New with R2?

Are you ready for Microsoft SQL Server 2008 R2? This white paper highlights some of the major features included in SQL Server 2008 R2 due to be released in the first half of 2010.

Microsoft SQL Server 2008- What's New with R2-

Windows Authentication Users Domain Will not Change when Server Name Changed

in practice, you need to change the server name due to many reasons. There are few challenges you need to face as Database administrator. This is one of them.

After changing the server name, you need to run following stored procedures.

sp_dropserver SERVER2

GO

sp_addserver SERVER3, local

GO

SERVER2 being the old server name and SERVER3 is the new server name.

After this if you can login to the server with SERVER3\Administrator if you have configured SERVER2\Administrator user before. However, if you closely look at there is no user called SERVER3\Administrator instead user is SERVER2\Administrator.

When server name is changed, I believe these windows authentication users also changed. Now you need to run following script to change user names accordingly.

/****** Object: Login [SERVER2\Administrator] Script Date: 04/06/2010 14:51:51 ******/

IF EXISTS (SELECT * FROM sys.server_principals WHERE name = N'SERVER2\Administrator')

DROP LOGIN [SERVER2\Administrator]

GO

/****** Object: Login [SERVER3\Administrator] Script Date: 04/06/2010 14:51:51 ******/

CREATE LOGIN [SERVER3\Administrator] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]

GO

Changing Data Type for Derived Columns?

If you want to add a column to the existing data flow in SSIS, in normal circumstances you are using Derived Column Transformation to accomplish this task.

If you drag and drop the Derived Column Data Flow Transformation and following configuration dialog box will appear.

Unlike in SQL Server 2005, you don’t have an option of selecting the data type you need. It will select default data type for expression you type.

For example, for text data type it will be Unicode string [DT_WSTR], for positive integer’s four-byte signed integer [DT_I4], for float numeric [DT_NUMERIC].

There is a workaround for this. Go to the advanced Editor; navigate to the option shown in following image.

You can select any data type from the available combo box here and if you go back again to the previous configuration, you will see the newly selected data type there as well.

However, I feel this is a bug to be fixed.

Are You Using Template Explorer

This is an hidden feature for many DBAs and developers. See what you can do with Template Explorer in my latest blog at http://beyondrelational.com/blogs/dinesh_asanka/archive/2010/04/01/are-you-using-template-explorer.aspx

Light Weight Monitoring using Extended Events

This is my latest article on Extended Events in SQL Server 2008. This is the latest monitoring mechanism in SQL Server 2008.

Re-indexing/ Reorganizing a Table after Enabling CDC

In SQL Server 2008 there is a new feature named CDC where you can capture data changes. Sorry this blog entry is not going to discuss about how to work with CDC instead this is an issue which I faced after enabling CDC.

I enabled CDC for a table called CDC with following syntax.

EXEC sys.sp_cdc_enable_table

@source_schema = N'dbo',

@source_name   = N'data',

@role_name     = N'dbo',

@supports_net_changes = 1

GO

Then I performed a re-organize for that table.

USE [COB]

GO

ALTER INDEX [PK_Data] ON [dbo].[Data] REORGANIZE WITH ( LOB_COMPACTION = ON )

GO

Then it returned the following error.

Msg 22983, Level 16, State 1, Procedure sp_cdc_ddl_event_internal, Line 77

The unique index 'PK_Data' on source table '[dbo].[Data]' is used by Change Data Capture. To alter or drop the index, you must first disable Change Data Capture on the table.

Msg 3609, Level 16, State 2, Line 1

The transaction ended in the trigger. The batch has been aborted.

Same error will appear if I do a re-indexing.

This bug is fixed with Cumulative update package 6 for SQL Server 2008 Service Pack 1

Depreciated Features in SQL Server 2008

With every versions of SQL Server, there are new features added. With new features, there are discounted and depreciated features.

Most of the time we are not sure whether our applications are using discounted or depreciated version. Discounted versions anyway will fail, but depreciated features will work.

To identify depreciated features, now there is a new perfmon counter called, SQLServer:Depreciated Features.

This was my output, simply after adding these counters.

Above image shows SQL Server instance has 4 databases with compatibility level 90 and 2 databases with compatibility level 80. (Scale is x10). In SQL Server 2008, compatibility level 70 is discontinued while 80 and 90 compatibility levels are depreciated.

Following query has two depreciated features.

CREATE Table #

(

ID INT,

NAME TEXT,

LANGUAGE NTEXT

)

CREATE Table ##

(

ID INT,

NAME TEXT,

LANGUAGE NTEXT

)

DROP TABLE #

DROP TABLE ##

This will generate few more counters as you can see below.

This count is an aggregated counter and it will be reset to zero when the service is restated.

Functionality Change in REPLACE Function in SQL Server 2008

What is the output for the following query?

DECLARE @V CHAR(6)

SET @V = 'ABC'

SELECT REPLACE(@V,' ','L')

Guess what you have two answers. In SQL Server 2005, it will be ABC while in SQL Server 2008 it is ABCLLL.

What does this mean? They have fixed the bug in REPLACE function in SQL Server 2008. In SQL Server 2005, for char data type, before replacing it trims the variable which is incorrect. In SQL Server 2008 now it is not trimming your data before the replacing function.

If you are in the process of upgrading to SQL Server 2008 this is an point to consider.

Strange Error after DDL Trigger

I created a DDL trigger on my server (SQL Server 2008 with SP1) to pick the database name which is listed below.

CREATE TRIGGER [ddlsvrtrg_create_database_backup_paths] ON ALL SERVER

FOR CREATE_DATABASE

AS

SET NOCOUNT ON

DECLARE @xmlEventData XML,

@database_name VARCHAR(50),@path varchar(500),@fullPath varchar(500),@diffpath varchar(500)

SET @xmlEventData = eventdata()

SELECT @database_name = EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]', 'nvarchar(150)')

.

.

.

After creating this trigger, I tried to create a database from the UI and you will get the following error.

TITLE: Microsoft SQL Server Management Studio

------------------------------

Create failed for Database 'DB_DDL'. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.0.2531.0+((Katmai_PCU_Main).090329-1045+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+Database&LinkId=20476

------------------------------

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

SELECT failed because the following SET options have incorrect settings: 'ARITHABORT'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations. (Microsoft SQL Server, Error: 1934)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.00.2531&EvtSrc=MSSQLServer&EvtID=1934&LinkId=20476

------------------------------

BUTTONS:

OK

However, if I create a database from a script it will work. Also, I generated the script from the create database UI. That is still working.

Also, this issue does not exist in the SQL Server 2005 with SP3.

DDL Trigger on Database Create

I created a DDL trigger on SQL Server 2008 with SP1. Trigger is firing well for Create Database statement. However, it does not fire for the instance where you restore a database to a new database. Since, there is no DDL trigger event for Database Restore there are no other alternative.

To capture the database restore event, there was a nice suggestion by a forum member in SQL Server Central to create a DML trigger on [msdb].[dbo].[restorehistory]