How to upgrade database from SQL Server 2005 to SQL Server 2008

The following steps should be carried out upgrade a database from SQL Server 2005 to SQL Server 2008

Step 1:
Detach the database files from SQL Server 2005 and attach to SQL Server 2008 Server

Step 2:
Execute the below command to update the index and table Statistics in the newly attached database
SP_UPDATESTATS
It is recommended to execute update statistics separately for each table with full scan as given below
UPDATE STATISTICS . WITH FULLSCAN, NORECOMPUTE

Step 3:
Execute the below command to rest all the counters DBCC UPDATEUSAGE('')

Step 4:
Change the Database Compatibility Level as specified
Go to Database Properties à Options à Compatibility Level to à SQL Server 2008(100)

Paging using LINQ

SQL Server supports many ways of implementing paging in the applications. With the introduction of LINQ (Language Integrated Query) support in VS 2008 paging is further made simple. Please follow the below steps to implement paging for Employee List screen

Step 1 :
Write a method to retrieve the employee list from the table “Employee” using LINQ as shown in the method “GetEmployeeList”

public List GetEmployeeList(int PageNo ,out int TotalPages)
{
Int NoofRecordsPerPage =10;
List oResultOut = new List();
var query = (from p in dc.Employees
select p).ToList();
if (PageNo == 0)
TotalPages = query.Count / NoofRecordsPerPage;

oResultOut = query.Skip(PageNo * NoofRecordsPerPage)
.Take(NoofRecordsPerPage)
.ToList();

}
The parameter “PageNo” is used to determine the current page of Employee List view. It is often required to know the total number of pages, the List view will span during the first execution of the query. The above method will fill the total number of pages using the “query.Count” attribute.
The query.Skip() will filter the records of the previous pages and the query.Take() method will show only the records qualified for the current page. The number of records to be displayed on each page can be controlled using the variable “NoofRecordsPerPage” .

Step 2 :
Calling the “GetEmplyeeList” from the UI based on current page selected by the user in the Employee List view

// assume the user sees the first page
List oResult = new List();
int TotalPages =0;
oResult = GetEmployeeList( 0, out Totalages);

// assume the user sees the fifth page
List oResult = new List();
int TotalPages =0;
oResult = GetEmployeeList( 5, out Totalages);

We will see the benefits of using LINQ and how to implement paging for retrievals using the stored procedures using DLINQ in my next post

How to generate ROWNUM in SQL Server SELECT

It is often required to create unique values for every row in a result set query in SQL Server. But SQL Server doesn’t have any built in functions such as ROWNUM in oracle to support this feature. There are many ways in which we can generate unique numbers in the select query as shown in the below scenario

Consider the Employee table with EmployeeName and EmployeeID columns and the scenario is to select records from the table based on EmployeeID with uniqueid for each row in the result set. The select statement can be written in below ways

Create table Employee ( EmployeeID INT , EmployeeName Varchar(30))
ROWNUM using ROW_NUMBER
SELECT ROW_NUMBER () OVER (ORDER BY EmployeeID) AS RowNumber, EmployeeName
FROM Employee ORDER BY EmployeeID
ROWNUM using IDENTITY
SELECT IDENTITY(int, 100, 1) AS RowNumber , EmployeeName
INTO #tmp
FROM Employee ORDER BY EmployeeID
SELET RowNumber , EmployeeID FROM #tmp
ROWNUM using NEWID
SELECT NEWID() ,AS RowNumber, EmployeeName FROM Employee
ORDER BY EmployeeID
Related article:
http://samsudeenb.blogspot.com/2009/06/how-to-generate-sequence-number-in-sql.html

Script to RESEED Database in SQL Server 2005

It is often required to RESEED the database tables with new range of identifiers especially when the table’s Primary Key column is designed with data types such as BIGINT/INT.

The following script allows us to RESEED the database tables with the new SEED value for all the user tables with identity column.

-- Declaration statement to capture the new SEED value
DECLARE @DesiredSeed VARCHAR(20)
SET @DesiredSeed = '5000000000'

DECLARE @TableName VARCHAR(256), @SQLStatement VARCHAR(1000)

-- Cursor to get the list of all user tables which have identity columns
DECLARE curIdentityTables CURSOR
FOR
SELECT b.TABLE_SCHEMA +'.'+ OBJECT_NAME (a.[id])
FROM sysobjects a INNER JOIN INFORMATION_SCHEMA.COLUMNS b
ON OBJECT_NAME(a.id) = b.TABLE_NAME and b.ORDINAL_POSITION = 1
WHERE OBJECTPROPERTY (a.[id], 'IsUserTable') = 1
and OBJECT_NAME (a.[id]) <> 'dtproperties'
and OBJECTPROPERTY (a.[id], 'TableHasIdentity') = 1
ORDER BY a.[name]

--Open the cursor to loop through the table list and reseed it
OPEN curIdentityTables
FETCH NEXT FROM curIdentityTables INTO @TableName

WHILE @@FETCH_STATUS = 0
BEGIN

SET @SQLSTatement = 'DBCC CHECKIDENT (''' + @TableName + ''', RESEED, ' + @DesiredSeed + ')'
EXEC (@SQLSTatement)
FETCH NEXT FROM curIdentityTables INTO @TableName
END

CLOSE curIdentityTables
DEALLOCATE curIdentityTables

Index maintenance using DMV’s in SQL Server 2005

SQL Server 2005 provides more flexible ways handling index maintenance activities using DMV (Dynamic Management View’s). The DMV’s can be effectively used to identify the index status such as

• Indexes that requires maintenance activities such as reindexing / reorganizing
• List of indexes that are going for index scan
• List of not used indexes

Tables with indexes that require maintenance

SELECT OBJECT_NAME(OBJECT_ID) TableName,
( SELECT NAME FROM SYS.INDEXES
WHERE OBJECT_ID = A.OBJECT_ID
AND INDEX_ID = A.INDEX_ID) IndexName
FROM SYS.DM_DB_INDEX_USAGE_STATS A
WHERE USER_SEEKS >0 AND USER_SCANS >0
AND OBJECT_ID > 97
AND DATABASE_ID = 5
ORDER BY USER_UPDATES, USER_SEEKS DESC

Tables with Indexes going for Scan

SELECT OBJECT_NAME(OBJECT_ID) TableName ,
( SELECT NAME FROM SYS.INDEXES
WHERE OBJECT_ID = A.OBJECT_ID
AND INDEX_ID = A.INDEX_ID) IndexName
FROM SYS.DM_DB_INDEX_USAGE_STATS A
WHERE INDEX_ID <> 0
AND OBJECT_ID > 97
AND DATABASE_ID = 5
AND USER_SCANS > 0 ORDER BY USER_SCANS DESC

Tables with not used indexes

SELECT OBJECT_NAME(OBJECT_ID) TableName,
(SELECT NAME FROM SYS.INDEXES
WHERE OBJECT_ID = A.OBJECT_ID
AND INDEX_ID = A.INDEX_ID) IndexName
FROM SYS.DM_DB_INDEX_USAGE_STATS A
WHERE USER_SEEKS = 0
AND USER_SCANS =0
AND USER_LOOKUPS =0
AND USER_UPDATES = 0
AND OBJECT_ID > 97
AND INDEX_ID <> 0
AND DATABASE_ID = 5

Parallel Data Loading using SQL Server 2005 partition techniques (BCP)

It is common in Enterprise scale projects to simulate the test environment with very large-scale databases. The preparation of real time data and loading consumes considerable amount of time during the environment setup. The SQL Server 2005 partition techniques allow parallel data loading into the tables using BCP / Bulk Insert statements. The data loading of 10 Million records into “Sales” tables using the partition and non-partition methods is explained below.

Data loading with out Partition

CREATE TABLE Sales
(
UniqueID BIGINT,
ItemName VARCHAR(100),
SaledAmount NUMERIC(10,3),
SalesDate DATETIME
)on [Primary]

The data loading for this table can be done using the BCP / Bulk Insert options. But we cannot load the data in parallel, as it will lead to table locking

Data loading with Partition

CREATE TABLE Sales(
ItemName VARCHAR(100),
SaledAmount NUMERIC(10,3),
SalesDate DATETIME,
PartitionID INT
)on fPartitionID(PartitionID)

The table “Sales” is partitioned using the column PartitionID into 10 different partitions (say PartitionID accepts value between 1.10)
As the table is split into 10 different partitions, data loading of this table can be done in parallel follows

• Generate the data into to 10 X 1 Million files
• Load data into the table using 10 BCP / Bulk Insert instances in parallel

Conclusion

The performance of the data loading can be improved up to 10 times if the table is partitioned. This option can be preferred only if there are sufficient hardware resources. As CPU usage of BCP is very high, the number of parallel instances can be reduced as per the resource availability

Restrict SQL Server Login –SQL Server 2005

There is always a need to restrict the user logins into the SQL Server databases. Many DBA’s face this difficulty, as the restriction of logins has to be done for different users with different requirements. SQL Server 2005 introduces an option called “LOGON” triggers which is an easy way restrict the number of user logins as per our requirements.

This Logon trigger is created directly on the database server and registered on the master database. The below sample demonstrates the use of login triggers to restrict the user “john” from accessing the database using “SQL Query Analyzer” window.


USE master
GO
CREATE TRIGGER trgRestrictUser
ON ALL SERVER WITH EXECUTE AS 'sa'
FOR LOGON
AS
BEGIN
IF (ORIGINAL_LOGIN()= 'john' AND APP_NAME() = 'Microsoft SQL Server Management Studio - Query')
ROLLBACK;
END;

This logon trigger can be used for various auditing purposes in SQL Server. This is a new feature introduced in the SQL Sever 2005 Service Pack 2.We need to upgrade to SP2 to use this feature.

Below links can give more information about Logon triggers
http://msdn2.microsoft.com/en-us/library/bb326598.aspx

SQL Server TempDB useful tips

Moving Temp Database Files
Step 1:
Move the tempdb files to a new physical location (mostly on disk drive in RAID protection 1, 1 + 0 or 5 )
use master
GO
Alter database tempdb modify file (name = tempdev, filename = '\tempdev.mdf')
GO
Alter database tempdb modify file (name = templog, filename = '\templog.ldf')
GO
Step 2:
Restart the SQL Server service
Step 3:
On Restart the tempdb will be created with the new location specified
Step 4:
Remove the data and log file of the tempdb from the old location

Starting SQL Server without TempDB
SQL Server cannot operate with out the tempdb database.When the tempdb filesare corrupted / deleted accidentaly we can restart the SQL Server using the following commandline utlitity
SQLSERVER.exe –s -f -c -T3609

TempDB Best Practices
The below link explains some of the best practices on using the tempdb
http://www.mssqltips.com/tip.asp?tip=1432