Downgrade SQL Server database - from a higher version to a lower version

A backup of a Sql server database that was taken from a older version can be restored to a new version with ease (in most cases).
However, a database cannot be resotred into an older version. for example you cant restore a database backup that was taken in SQL Server 2008 R2 and restore it on SQL Server 2008 or SQL Server 2005.

If you are unfortunate enough to be in a situation where you have a backup of a newer version and you want to restore it onto an older version of SQL Server, here are the steps you need to do;

For this you will need access to two instances of sql server (They can be running on the same box or on seperate boxes);

• Instance 1: running the old version say SQL 2005, I will call it Destination2005
• Instance 2: running the new version say SQL 2008R2, I will call it Source2008R2

1. On the destination server, create a linked server called Source2008R2
2. Create an empty database on the Destination2005 server (this will become your database).
3. Using a tool like Redgate SQL Compare script the schema of the database on Source2008R2 and run it against the new (empty) database you have created in the previous step.
4. Create a script to disable the Foriegn key constraints and another script to enable foriegn key constraints. There are many blogs on the web for this, I got this from http://www.sqlmag.com/forums/aft/70529

Disable all Constraints
exec sp_MSforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'

Disable all Triggers
exec sp_MSforeachtable 'ALTER TABLE ? DISABLE TRIGGER ALL'

## execute updates here...

Enable all Constraints
exec sp_MSforeachtable 'ALTER TABLE ? CHECK CONSTRAINT ALL'

Enable all Triggers
exec sp_MSforeachtable 'ALTER TABLE ? ENABLE TRIGGER ALL'

1. 5. Now you have an empty database on Destination2005 instance with the same schema as the one on Source2008R2 and with all constraints disabled.
2. 6. Start importing the data by running the following script on Destination2005. This script will generate the insert statements required to import the data from the source.

/*

YB: A script to copy data from one database to another.

Run it in the destination database. AT YOUR OWN RISK..

*/

DECLARE

@TABLENAME NVARCHAR(100) ,
@ColumnName  NVARCHAR(100) ,
@HasIdentity INT,
@SQL1 NVARCHAR(MAX),
@SQL2 NVARCHAR(MAX),
@NewLine NVarchar(4)

SET @newline=NCHAR(13) + NCHAR(10)
DECLARE ParentCursor CURSOR FOR
SELECT Tab.TABLE_NAME, HasIdentity =CASE WHEN Ident.INCREMENT_VALUE IS NULL THEN 0 ELSE 1 END
FROM
INFORMATION_SCHEMA.TABLES Tab
LEFT OUTER JOIN SYS.IDENTITY_COLUMNS Ident ON Tab.TABLE_NAME=OBJECT_NAME(OBJECT_ID)
WHERE
TABLE_TYPE='BASE TABLE'
and TABLE_CATALOG='MyDatabaseName'
OPEN ParentCursor;
FETCH NEXT FROM ParentCursor INTO @TABLENAME, @HasIdentity
WHILE @@FETCH_STATUS =0
BEGIN
-- PRINT 'HAS Identity : ' + convert (VARCHAR(10), @HasIdentity);
SET @SQL1=N''
SET @SQL2=N''
DECLARE ChildCursor CURSOR FOR
SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
WHERE
TABLE_CATALOG='MyDatabaseName'
AND TABLE_NAME=@TABLENAME
AND DATA_TYPE <> 'Timestamp'
ORDER BY
Ordinal_position
OPEN ChildCursor;
FETCH NEXT FROM ChildCursor INTO @ColumnName
WHILE @@FETCH_STATUS =0
BEGIN
SET @SQL2 = @SQL2 +'['+ @ColumnName + '],'
FETCH NEXT FROM ChildCursor INTO @ColumnName
END
CLOSE ChildCursor;
DEALLOCATE ChildCursor;
   -- Remove the last , from @SQL2
SET @SQL2=LEFT ( @SQL2, LEN(@SQL2) -1)
SET @SQL1=@SQL1 + 'TRUNCATE TABLE [MyDatabaseName].[dbo].[' + @TABLENAME + ']' + @NewLine + 'GO' + @NewLine ;
IF @HasIdentity=1
BEGIN
SET @SQL1 =@SQL1 + 'SET Identity_INSERT [' + @TABLENAME + '] ON' + @NewLine
END
ELSE
BEGIN
SET @SQL1 =@SQL1 + ' '
END
SET @SQL1=@SQL1 + 'INSERT INTO [MyDatabaseName].[dbo].[' + @TABLENAME + '] (' + @SQL2 + ')' + @NewLine;
SET @SQL1=@SQL1 + 'SELECT ' + @SQL2 + ' FROM [Source2008R2].[MyDatabaseName].[dbo].[' + @TABLENAME + ']' + @NewLine;

IF @HasIdentity=1
BEGIN
SET @SQL1=@SQL1 + 'SET Identity_INSERT [' + @TABLENAME + '] OFF' + @NewLine;
END
ELSE
BEGIN
SET @SQL1 =@SQL1 + ' '
END

SET @SQL1=@SQL1 + 'GO' + @NewLine;
 

PRINT @SQL1

FETCH NEXT FROM ParentCursor INTO @TABLEName, @HasIdentity
END

CLOSE ParentCursor;
DEALLOCATEParentCursor;

Error: 30087, Severity: 17, State: 5

I noticed this error in the log of one of my servers that hosts around 100 databases and heavily uses Full text indexes.
the error messages says :

Error: 30087, Severity: 17, State: 5.
2012-08-20 16:24:24.60 spid40s Data coming back to the SQL Server process from the filter daemon host is corrupted. This may be caused by a bad filter. The batch for the indexing operation will automatically be retried using a smaller batch size.

I also noticed a couple of dump files which appeared to be related to the above error message. I googled a bit, but did not find anything useful then opened the dump file and started looking for clues.

This is an extract from th edump.

The highlighted text in the log, reads;

IFTS Encountered memory pressure. decreasing the batch size, number of simultaneous crawlers and/ or ISM size can help alleviate the problem. Global\ISM

This was a sign that the full text deamon was consuming too much memory running out of memory.
The server has 12 CPUs and plenty of memory, but the max server memory was set at default which meant that Sql server was taking all the memory.

To resolve the issue, I performed the following;

- Set a limit to Max server memory that sql server can use, this will give the full text deamon and other services running on the server to have a share of the memory.

Finding the correct value for Max server memory is different for each server and depends on many factors including what else is running on the box along side sql server. The following article gives a formula to calculate the amount of memory that is needed for fdhost.exe

Performance Tuning and Optimization of Full-Text Indexes

The second thing I did was to set the Max full-text crawl range, this is a configuration option that optimises CPU utilisation when performing a full crawl.

The optimum value for  Max full-text crawl range is the number of CPUs on the server, in my case I have 2 sockets with 6 core each

sp_configure 'max full-text crawl range', 12
Reconfigure

The third thing you should do is reorganise and compact the FT indexes using
ALTER FULLTEXT CATALOG [CatalogName] REORGANIZE;

Encrypting password in sql server

Despite that fact that everyone knows storing passwords in a plain text is evil, I am convinced that there are many large organisations that store customer passwords in plain text.

Recently a large company was attacked by a group of hackers and they used sql injection to get customer password which they then used to login to a portal and do bad things..

In this post I will show you a simple way to store passwords securely in your database.

There is an important difference between encrypting passwords and encrypting other information such as customer salary or credit card information. In the case of passwords, in most cases the DBA or any user does not need to know the customers password, so all you need is a way to encrypt the password (aka one way hash).  But credit card numbers need to be seen in the plain form by the Finance team and employee salary by the Account team so you need to be able to encrypt and decrypt this information.

If you are encrypting the password data using EncryptByPassPhrase or EncryptByKey and a hacker/ or an “non-authorised internal user” got hold of your key or passphrase “which usually will be found on the server“ then your passwords can be decrypted using DecryptByPassPhrase or DecryptByKey.  

In order to do that, I have written a function called password () which tales a string as a parameter and returns a string of the hash value. The function is inspired by the php function password() .. php rocks ;-)

Create a function

CREATE FUNCTION [dbo].[password]

(

          @stringToHash VARCHAR(128) 

)

RETURNS VARCHAR(128)

AS

/*********************************************************************************

    Name:       dba_parseString_udf

    Author:     Y Bamarni

    Purpose:    A function to hash a string

    Date        Initials    Description

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

    2011-09-06           Initial Release

*********************************************************************************

Usage:           

    SELECT dbo.password ('mypassword') as encryptedString

*********************************************************************************/

BEGIN

      DECLARE @hash VARCHAR(10),

                  @EncryptedValue   VARCHAR(128)

                 

      SET @hash='SHA1'

      SELECT @EncryptedValue=CONVERT(VARCHAR(128), HASHBYTES(@hash, @stringToHash),2)  -- the convert is to remove the begining two characters

      RETURN @EncryptedValue;

END

GO

Now in order to test this, we will create a test table called Test

create table Test (id int identity, username varchar(50), pass varchar(128))

To insert a new record, use the new password function to convert the password string to an encrypted string

insert into Test (username, Pass) values ( 'Yasir1',  dbo.password('mypassword'))

insert into Test (username, Pass) values ( 'Yasir2',  dbo.password('mypassword2'))

insert into Test (username, Pass) values ( 'Yasir3',  dbo.password('mypassword3'))

insert into Test (username, Pass) values ( 'Yasir4',  dbo.password('mypassword4'))

If you run a SELECT, you will notice that the password is encrypted

select top 100 * from Test;

If you want to update the password (say you need to reset a password)

update Test set pass =dbo.password('myNewPassword') where id=1

If you want to check if a password supplied by the user matches the password hash in the database.

SELECT * from Test WHERE username ='Yasir3' and pass=dbo.password ('WrongPassword')             -- deny

SELECT * from Test WHERE username ='Yasir3' and pass=dbo.password ('myNewPassword3')            -- grant

using a check constraint to enforce values in a column based on values in another table

You can enforce business rules in a column based on values in another column thats in another table by creating a check constraint with a UDF function.

On scenario that you may need to do this is imagine you have a global table that holds states. this table holds stateIds for all entities in your database (for instance customer stateIds, order stateIds, sale stateIds...etc)

In this case you may not be able to use a foriegn key constraint ( because you need to restrict a subset of the stateIds per table)

Here is an exanple on how you can do tha.


Assume your global table that holds stateIds is called State

CREATE TABLE t1 (stateId INT NOT NULL)

go

CREATE FUNCTION dbo.fnc_IsValidState(@StateId INT, @TypeId Int) RETURNS BIT
AS
BEGIN

DECLARE @flag BIT = 1

IF NOT EXISTS (SELECT 1 FROM state WHERE stateId =@StateId AND ClassId=@TypeId)
BEGIN

SET @flag = 0

END

RETURN @flag
END



go


ALTER TABLE dbo.[t1] WITH NOCHECK ADD CONSTRAINT [CK_StateCHeck] CHECK ((dbo.fnc_IsValidState(StateId,1)=1))

Check if a database is a snapshot

You can quickly check if a database is a Snapshot or not by examining the source_database_id column of the sys.databases view

if the database is a snapshot, the source_database_id will contain the database_id of the original database. but if the database is a normal database, this field will be NULL.

-- Check if a database has a snapshot
DECLARE @DBname sysname
SET @DBname='MyDatabaseName'

-- check if a database is a snapshot or not
SELECT CASE WHEN source_database_id IS NULL THEN 'Not a snapshot' ELSE 'Snapshot' END AS Database_type , source_database_id, database_id
FROM sys.databases
WHERE Name=@DBname

You can use the same field to find all the snapshots for a specific database.

SELECT * FROM sys.databases
WHERE source_database_id =
(SELECT database_Id FROM sys.databases WHERE Name=@DBname)

This is useful if you have a script that regularly restore a database (say on a dev server) and needs to drop any snapshots before performing the restore.

SET VS SELECT

There are a couple of differences between using SET or SELECT statement when it comes to assigning values to local variables. Some are trivial and others may cause bugs that are hard to find in your code;

One of the issues that you need to be aware of is when using SELECT to assign value to a variable, if the select returns more than one record, it will assign one of the returned values to your local variable which may not be what you want.

But, SET will return an error if there are more than one records returned which in my openion is a better option. Here is a demo;


DECLARE @Id INT
DECLARE @Temp TABLE (ID INT , NAME VARCHAR(25))

INSERT INTO @temp (id, name) VALUES (1,'one')
INSERT INTO @temp (id, name) VALUES (2,'one')
INSERT INTO @temp (id, name) VALUES (3,'Two')
INSERT INTO @temp (id, name) VALUES (4,'Three')

-- With select you dont get an error

SELECT @Id =id FROM @Temp WHERE Name='one'
SELECT @Id AS RETURNS_one_of_the_records_Without_any_errors

--You get NO errors, and ONE of the records is returned which may not be what you want.

-- With SET you will get an error if there are more than one records returned
SET @Id =(SELECT id FROM @Temp WHERE Name='one')

--You get error, Which is better
SELECT @Id AS RETURNS_errors_as_There_Are_More_than_one_records

In the SET statement, you can check for @@Error and handle accordingly or if you have to use SELECT, you may be able to pick the record that you need by using TOP 1 and order by in the SELECT statement.

The second issue is when the SELECT returns NULL, it does not assign the NULL to the variable. So if your variable had a value of X before the SELECT statement was executed, and the SELECT returned NULL, your variable value will still be X as shown below.


DECLARE @Id INT
DECLARE @Temp TABLE (ID INT , NAME VARCHAR(25))

-- Assume @Id had a vlaue of 100
SET @Id =100

-- Get the new value of @id from a table using SELECT
SELECT @Id =id FROM @Temp WHERE Name='Does not exist'

--You get wrong value. the value of @id should be Null and not 100
SELECT @Id AS Id_Value_Should_be_Null

SET @Id =(SELECT id FROM @Temp WHERE Name='Does not exist')

--This will show correct value i.e Null
SELECT @Id AS Id_Value_Should_be_Null
SELECT @Id =(SELECT id FROM @Temp WHERE Name='Does not exist')

--This will show correct value i.e. null
SELECT @Id AS Id_Value_Should_be_Null

Hopefully knowing these settle differences will save you time debugging !

Why “Extent Scan fragmentation” remains high after rebuilding index

According to BOL, extent scan fragmentation is; “Percentage of out-of-order extents in scanning the leaf pages of an index. This number is not relevant to heaps. An out-of-order extent is one for which the extent that contains the current page for an index is not physically the next extent after the extent that contains the previous page for an index.”

It is also referred to as “physical fragmentation”, which basically means that the extents on the disk are not contagious.
If rebuilding an index did not bring this value down, it means your physical volume is fragmented.

In my case, I have a sql server running on a netapp san (FAS 3140)

I have a table called objects containing 12+ million records and a clustered index and a couple of non clustered indexes.

When running the following command, I get extent scan fragmentation of 60.59%
dbcc showcontig (Objects,IX_ObjectsExpires)

DBCC SHOWCONTIG scanning 'Objects' table...
Table: 'Objects' (309576141); index ID: 12, database ID: 17
LEAF level scan performed.
- Pages Scanned................................: 30593
- Extents Scanned..............................: 3852
- Extent Switches..............................: 3917
- Avg. Pages per Extent........................: 7.9
- Scan Density [Best Count:Actual Count].......: 97.63% [3825:3918]
- Logical Scan Fragmentation ..................: 0.88%
- Extent Scan Fragmentation ...................: 60.59%
- Avg. Bytes Free per Page.....................: 683.2
- Avg. Page Density (full).....................: 91.56%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Alter rebuilding the index

Alter index IX_ObjectsExpires ON [Objects] Rebuild;
The value of extent scan fragmentation is gone up!

DBCC SHOWCONTIG scanning 'Objects' table...
Table: 'Objects' (309576141); index ID: 12, database ID: 17
LEAF level scan performed.
- Pages Scanned................................: 31109
- Extents Scanned..............................: 3889
- Extent Switches..............................: 3888
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 100.00% [3889:3889]
- Logical Scan Fragmentation ..................: 0.01%
- Extent Scan Fragmentation ...................: 69.17%
- Avg. Bytes Free per Page.....................: 806.1
- Avg. Page Density (full).....................: 90.04%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

-- as can be seen, the logical fragmentation has dropped to 0, but the extent fragmentation has gone up!!

I run “reallocate” command on the volume containing the database. please note that this is a Netap specific command that reorders the block on a volume. Your SAN vendor will provide a different command.

reallocate start -f /vol/ascio_otherdb_data_vol

During reallocate, cpu gone up from 1 to around 27 % on the filer.
But no performance hit was noticed on the database. After the reallocation was completed, I rebuild the index again;

alter index IX_ObjectsExpires ON [Objects] Rebuild;

and hooray ..extent fragmentation set back to almost 0.15%

DBCC SHOWCONTIG scanning 'Objects' table...
Table: 'Objects' (309576141); index ID: 12, database ID: 17
LEAF level scan performed.
- Pages Scanned................................: 31109
- Extents Scanned..............................: 3889
- Extent Switches..............................: 3888
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 100.00% [3889:3889]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 0.15%
- Avg. Bytes Free per Page.....................: 806.0
- Avg. Page Density (full).....................: 90.04%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.


-- for a volume size of 400 GB, it took around 1 hour t complete the reallocation command on the filer.

Mon Mar 7 17:28:52 GMT [LDN-HA-SAN01: wafl.scan.start:info]: Starting file reallocating on volume ascio_otherdb_data_vol.
Mon Mar 7 18:26:41 GMT [LDN-HA-SAN01: wafl.reallocate.full.done:info]: Full reallocation on '/vol/ascio_otherdb_data_vol' is complete.