If you have been into database administration & development for a while now, what is the first thing that comes to your mind when someone asks to modify database options like setting the database read-only, or taking it offline? Answer: The sp_dboption system stored procedure.

The typical way to modify these database options would be:

~~~–"Before" state
SELECT ‘Before’,is_read_only,state,state_desc,* FROM sys.databases db WHERE db.name = ‘TestDB’
GO
–Set the database to READ-ONLY mode
sp_dboption ‘TestDB’,’read only’,’TRUE’
GO
–"After" state
SELECT ‘Post Read Only’,is_read_only,state,state_desc,* FROM sys.databases db WHERE db.name = ‘TestDB’
GO
–Set the database OFFLINE
sp_dboption ‘TestDB’,’offline’,’TRUE’
GO
–"After" state
SELECT ‘All done’,is_read_only,state,state_desc,* FROM sys.databases db WHERE db.name = ‘TestDB’
GO~~~

SQL Server 2012 – Replacement – ALTER DATABASE…SET

However, starting SQL Server 2012, attempting to change the database options this way would produce the following results.

Msg 2812, Level 16, State 62, Line 1

Could not find stored procedure ‘sp_dboption’.

The system could not find the system stored procedure! That’s simply because it has been deprecated! The replacement is the ALTER DATABASE statement, and the existing sp_dboption calls need to be modified as demonstrated in the script below:

~~~–"Before" state
SELECT ‘Before’,is_read_only,state,state_desc,* FROM sys.databases db WHERE db.name = ‘TestDB’
GO
–Set the database to READ-ONLY mode
ALTER DATABASE TestDB SET READ_ONLY
GO
–"After" state
SELECT ‘Post Read Only’,is_read_only,state,state_desc,* FROM sys.databases db WHERE db.name = ‘TestDB’
GO
–Set the database OFFLINE
ALTER DATABASE TestDB SET OFFLINE
GO
–"After" state
SELECT ‘All done’,is_read_only,state,state_desc,* FROM sys.databases db WHERE db.name = ‘TestDB’
GO~~~

The sp_dboption system stored procedure was marked as “discontinued” ever since SQL Server 2005. Yet, I have seen  a lot of production code this year itself that uses the sp_dboption statement. All of this code will break unless it is replaced with ALTER DATABASE…SET statement.

References:

• sp_dboption – http://msdn.microsoft.com/en-us/library/ms187310(v=sql.90).aspx
• ALTER DATABASE statement – http://msdn.microsoft.com/en-us/library/bb522682.aspx

Until we meet next time,

Be courteous. Drive responsibly.

There would hardly be a database developer who hasn’t written triggers in their career. It is fairly common knowledge that you can have more than one trigger of the same type, i.e. INSERT/UPDATE/DELETE on the same table. Therefore, the following is perfectly valid:

USE tempdb
GO
CREATE TABLE dbo.AppendTriggerTest (RowId INT IDENTITY(1,1),
                                    RowValue VARCHAR(50)
                                   )
GO

CREATE TRIGGER dbo.MainTrigger
ON dbo.AppendTriggerTest
FOR INSERT
AS
BEGIN
  PRINT '<<<< INFO >>>> Main Trigger fired...'
END
GO

CREATE TRIGGER dbo.AppendTrigger
ON dbo.AppendTriggerTest
FOR INSERT
AS
BEGIN
  PRINT '<<<< INFO >>>> Append Trigger fired...'
END
GO

However, this was not the case in the days of SQL Server 6.5. In order to have a more than one trigger of the same type on the same table, the WITH APPEND clause needs to be used, which is effectively interpreted as AFTER. In the days of SQL Server 6.5, the trigger “AppendTrigger” will need to be defined as:

CREATE TRIGGER dbo.AppendTrigger
ON dbo.AppendTriggerTest
FOR INSERT
WITH APPEND     --NOTICE THE WITH APPEND CLAUSE HERE, 
                --DEPRECATED FROM SQL 2012 ONWARDS
AS
BEGIN
  PRINT '<<<< INFO >>>> Append Trigger fired...'
END
GO

Starting SQL Server 2012, the compatibility levels lower than 90 (i.e. SQL Server 2000 and below) are no longer supported. In accordance to this, the WITH APPEND clause has also been deprecated.

I assume that you are not using this in your production code, if you are, please upgrade! There is no workaround/alternate to this.

Reference:

• SQL 2000 Books-On-Line page for CREATE TRIGGER: http://msdn.microsoft.com/en-us/library/aa258254(v=sql.80).aspx

Until we meet next time,

Be courteous. Drive responsibly.

There would hardly be a database developer who hasn’t written triggers in their career. It is fairly common knowledge that you can have more than one trigger of the same type, i.e. INSERT/UPDATE/DELETE on the same table. Therefore, the following is perfectly valid:

~~~USE tempdb
GO
CREATE TABLE dbo.AppendTriggerTest (RowId INT IDENTITY(1,1),
RowValue VARCHAR(50)
)
GO

CREATE TRIGGER dbo.MainTrigger
ON dbo.AppendTriggerTest
FOR INSERT
AS
BEGIN
PRINT ‘<<<< INFO >>>> Main Trigger fired…’
END
GO

CREATE TRIGGER dbo.AppendTrigger
ON dbo.AppendTriggerTest
FOR INSERT
AS
BEGIN
PRINT ‘<<<< INFO >>>> Append Trigger fired…’
END
GO~~~

However, this was not the case in the days of SQL Server 6.5. In order to have a more than one trigger of the same type on the same table, the WITH APPEND clause needs to be used, which is effectively interpreted as AFTER. In the days of SQL Server 6.5, the trigger “AppendTrigger” will need to be defined as:

~~~CREATE TRIGGER dbo.AppendTrigger
ON dbo.AppendTriggerTest
FOR INSERT
WITH APPEND –NOTICE THE WITH APPEND CLAUSE HERE,
–DEPRECATED FROM SQL 2012 ONWARDS
AS
BEGIN
PRINT ‘<<<< INFO >>>> Append Trigger fired…’
END
GO~~~

Starting SQL Server 2012, the compatibility levels lower than 90 (i.e. SQL Server 2000 and below) are no longer supported. In accordance to this, the WITH APPEND clause has also been deprecated.

I assume that you are not using this in your production code, if you are, please upgrade! There is no workaround/alternate to this.

Reference:

• SQL 2000 Books-On-Line page for CREATE TRIGGER: http://msdn.microsoft.com/en-us/library/aa258254(v=sql.80).aspx

Until we meet next time,

Be courteous. Drive responsibly.

For anybody who has worked with software products or recently bought a new piece of computer hardware, the terms “32-bit support” and “64-bit support” would be familiar.

One of the prime differences between the 32-bit & 64-bit systems is the amount of memory that can be addressed by the operating system. 32-bit systems cannot address a memory space that is greater than 3GB. While 64-bit systems can run a 32-bit application under the Windows-On-Windows shell, these applications would not be able to take advantage of the increased memory addressing capabilities of the underlying operating system. Insufficient memory on a SQL Server host puts the system under undue memory pressure . Memory management is therefore a tricky and critical issue for 32-bit applications running on 64-bit environments.

SQL Server 2005 introduced a switch on the SQL Server instance that allows the instance to use “AWE” (Address windowing extensions) for memory allocation. What this switch does is that it allows a 32-bit instance of SQL Server to access memory that is greater than 3GB. The screen-show below shows the location of this switch in the “Server Properties” dialog of the SSMS for SQL Server 2008.

Server properties dialog for SQL Server 2008

However, things are changing with SQL Server 2012 – Address Windowing Extensions (AWE) is no longer supported.

Server properties dialog for SQL Server 2012

This switch also has an equivalent advanced option called – “awe enabled”. Attempting to access this option in SQL Server 2012 results in an error.

–Display advanced configuration options

sp_configure ‘show advanced options’,1

RECONFIGURE

GO

–Attempt to fetch the value of the AWE flag

sp_configure ‘awe enabled’

GO

–Hide advanced configuration options

sp_configure ‘show advanced options’,0

RECONFIGURE

GO

Here’s the result:

Configuration option ‘show advanced options’ changed from 0 to 1. Run the RECONFIGURE statement to install.
Msg 15123, Level 16, State 1, Procedure sp_configure, Line 62
The configuration option ‘awe enabled’ does not exist, or it may be an advanced option.
Configuration option ‘show advanced options’ changed from 1 to 0. Run the RECONFIGURE statement to install.

Soltuions

There are no workarounds to this (in my opinion, teams shouldn’t even be thinking in this direction). It is imperative that the 64-bit edition of the SQL Server must be used in order to access over 4GB of physical memory.

References:

• On SQL Server Books On Line: Enabling AWE memory for SQL Server
  
• Pinal Dave (B|T) post: AWE Explained in simple words

Until we meet next time,

Be courteous. Drive responsibly.

For anybody who has worked with software products or recently bought a new piece of computer hardware, the terms “32-bit support” and “64-bit support” would be familiar.

One of the prime differences between the 32-bit & 64-bit systems is the amount of memory that can be addressed by the operating system. 32-bit systems cannot address a memory space that is greater than 3GB. While 64-bit systems can run a 32-bit application under the Windows-On-Windows shell, these applications would not be able to take advantage of the increased memory addressing capabilities of the underlying operating system. Insufficient memory on a SQL Server host puts the system under undue memory pressure . Memory management is therefore a tricky and critical issue for 32-bit applications running on 64-bit environments.

SQL Server 2005 introduced a switch on the SQL Server instance that allows the instance to use “AWE” (Address windowing extensions) for memory allocation. What this switch does is that it allows a 32-bit instance of SQL Server to access memory that is greater than 3GB. The screen-show below shows the location of this switch in the “Server Properties” dialog of the SSMS for SQL Server 2008.

Server properties dialog for SQL Server 2008

However, things are changing with SQL Server 2012 – Address Windowing Extensions (AWE) is no longer supported.

Server properties dialog for SQL Server 2012

This switch also has an equivalent advanced option called – “awe enabled”. Attempting to access this option in SQL Server 2012 results in an error.

~~~–Display advanced configuration options
sp_configure ‘show advanced options’,1
RECONFIGURE
GO
–Attempt to fetch the value of the AWE flag
sp_configure ‘awe enabled’
GO
–Hide advanced configuration options
sp_configure ‘show advanced options’,0
RECONFIGURE
GO~~~

Here’s the result:

Configuration option ‘show advanced options’ changed from 0 to 1. Run the RECONFIGURE statement to install.
Msg 15123, Level 16, State 1, Procedure sp_configure, Line 62
The configuration option ‘awe enabled’ does not exist, or it may be an advanced option.
Configuration option ‘show advanced options’ changed from 1 to 0. Run the RECONFIGURE statement to install.

Soltuions

There are no workarounds to this (in my opinion, teams shouldn’t even be thinking in this direction). It is imperative that the 64-bit edition of the SQL Server must be used in order to access over 4GB of physical memory.

References:

• On SQL Server Books On Line: Enabling AWE memory for SQL Server
  
• Pinal Dave (B|T) post: AWE Explained in simple words

Until we meet next time,

Be courteous. Drive responsibly.