Insufficient data from Andrew Fryer

The place where I page to when my brain is full up of stuff about the Microsoft platform

What's Missing from SQL Server 2008

What's Missing from SQL Server 2008

  • Comments 5
  • Likes

I have just arrived on Eileen Brown's team as the Data IT Pro evangelist. During my extensive  interview process I was asked by Keith Burns about the common language runtime (CLR) in SQL server 2005 and I replied with a quotation from his blog "that if it can't be done in SQL it isn't worth doing".  That would seem to rule out blogging but with the arrival of SQL server 2008 and it's ability to handle the vast amounts of unstructured data we encounter both at home and work this could well turn out to be absolutely true!

The other point about this remark is our digital immortality.  At our internal technical conference last week, one of the speakers talked about the first computer he had to use, a secondhand missile guidance system which he had to hook up to a mass spectrometer. After spending many sleepless nights getting the system working he prepared a comprehensive demo for the chemistry lab staff.  They weren't impressed at all by his technical wizadry and just wanted to get on and use the mass spectrometer. So he slightly 'modified' the code to make the machine crash randomly and added a lot of extra unnecessary steps to the startup instruction manual than was necessary.  This all came back to haunt him when the machine kept hanging as his random number generator turned out not to be so random and he had to use his old time consuming instructions to continually restart the system in order to debug his code. 

So not only are your finest moments preserved forever but also our worst mistakes.  Which needs me neatly onto one of the darker corners of SQL Server and that is deprecation of features as occasionally we need to tidy up some of the the throwbacks to earlier versions of the product.

There are two parts to this - features that won't be supported in SQL server 2008 as shown below:

Category

Deprecated feature

Replacement

Backup and restore

DUMP statement

BACKUP

Backup and restore

LOAD statement

RESTORE

Backup and restore

BACKUP LOG WITH NO_LOG

None.

The transaction log is automatically truncated when the database is using the simple recovery model. If you need to remove the log backup chain from a database, switch to the simple recovery model.

Backup and restore

BACKUP LOG WITH TRUNCATE_ONLY

None.

The transaction log is automatically truncated when the database is using the simple recovery model. If you need to remove the log backup chain from a database, switch to the simple recovery model.

Backup and restore

BACKUP TRANSACTION

BACKUP LOG

Backup and restore

BACKUP { DATABASE | LOG } WITH PASSWORD

None.

Backup and restore

BACKUP { DATABASE | LOG } WITH MEDIAPASSWORD

None.

Backup and Restore

RESTORE { DATABASE | LOG } … WITH DBO_ONLY

RESTORE { DATABASE | LOG } … … WITH RESTRICTED_USER

Backup and restore

RESTORE { DATABASE | LOG } WITH PASSWORD

None.

Backup and restore

RESTORE { DATABASE | LOG } WITH MEDIAPASSWORD

None.

Compatibility levels

60 and 65 compatibility levels

None.

Compatibility levels

70 compatibility level

None.

DBCC

DBCC CONCURRENCYVIOLATION

None.

Extended stored procedure programming

srv_getuserdata

srv_setuserdata

Use CLR Integration instead.

Full-text search

sp_fulltext_service action values clean_up, connect_timeout, and data_timeout return zero.

None.

Instance options

SET REMOTE_PROC_TRANSACTIONS

sp_configure 'remote proc trans'

Use linked servers and distributed queries.

sp_addlinkedserver

Remote servers

Use of remote servers

sp_addserver to create remote servers

Use linked servers.

sp_addlinkedserver to create linked servers

Security

sp_addalias

sp_dropalias

sp_addgroup

sp_changegroup

sp_dropgroup

sp_helpgroup

Superseded by roles

Security

SETUSER

EXECUTE AS

System tables

syssegments

None.

And features that will be deprecated in future releases SQL server after the 2008 version.

http://msdn2.microsoft.com/en-us/library/ms143729.aspx

So careful planning is needed if you plan to migrate to SQL server 2008.

Comments
  • Well our new Data Evangelist has finally started blogging. Andrew Fryer will be looking after all things

  • My first ever blog post was about the features of SQL Server 2000 that won’t work in SQL Server 2008,

  • My first ever post was about the features in SQL Server 2000 that were deprecated in SQL Server 2005

  • I have just started looking at upgrading to SQL 2008, and found that at least one necesseary statement (sp_addgroup) that is used in a fundamental (for my business) db-script is gone in 2008. Do you have any suggestions on work-arounds to make deprecated features from 2005 work in 2008, other than re-writing the scripts, obviously?

  • You need to start using roles instead. There are fixed roles created as part of the database.  You can add your own and then assign users to them as well as NT groups.

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment