What's Missing from SQL Server 2008

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.

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

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

Comments

• Anonymous
  August 03, 2007
  Well our new Data Evangelist has finally started blogging. Andrew Fryer will be looking after all things

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

• Anonymous
  May 27, 2008
  My first ever post was about the features in SQL Server 2000 that were deprecated in SQL Server 2005

• Anonymous
  November 03, 2008
  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?

• Anonymous
  November 04, 2008
  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.