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

Scripts to Drop and ReCreate Indexes in SQL Server 2005

SQL Server 2005 “GENERATE SCRIPTS” wizard does not have the option to generate the Index scripts alone from the database. The below script can be used to DROP and recreate the Indexes from the database.

Steps to be followed

1. Create a function to retrieve the list of key columns used in the Index definition (this function is useful for retrieving the multiple index columns in case of composite Index)

CREATE FUNCTION fGetIndexCols
(
@objname VARCHAR(50),
@indid INT
)
RETURNS NVARCHAR(131)
AS
BEGIN

DECLARE @keys NVARCHAR(2126)
DECLARE @i int, @thiskey NVARCHAR(131) , @objid BIGINT

SELECT @objid = object_id(@objname)
SELECT @keys = index_col(@objname, @indid, 1), @i = 2

IF (indexkey_property(@objid, @indid, 1, 'isdescending') = 1)
SELECT @keys = @keys + '(-)'
SELECT @thiskey = index_col(@objname, @indid, @i)
IF ((@thiskey is not null) and (indexkey_property(@objid, @indid, @i, 'isdescending') = 1))
SELECT @thiskey = @thiskey + '(-)'
WHILE (@thiskey is not null )
BEGIN

SELECT @keys = @keys + ', ' + @thiskey, @i = @i + 1
SELECT @thiskey = index_col(@objname, @indid, @i)
IF ((@thiskey is not null) and (indexkey_property(@objid, @indid, @i, 'isdescending') = 1))
SELECT @thiskey = @thiskey + '(-)'

END
RETURN (@KEYS)

END

2. Execute the below script to generate the CREATE INDEX statements from the database. I have written this script to generate the scripts for NON CLUSTERED indexes for clustered indexes change the where clause type_desc = ‘CLUSTERED’

SELECT 'CREATE ' +
CASE IS_UNIQUE WHEN 1 THEN 'UNIQUE ' ELSE ' ' END +
'NONCLUSTERED ' + ' INDEX '+
NAME + ' ' +' ON ' +
OBJECT_NAME (OBJECT_ID) +
'('+
DBO.fGetIndexCols (object_NAME(OBJECT_ID), index_id) +
')'+
' ON ['+
( SELECT GROUPNAME
FROM SYSFILEGROUPS
WHERE GROUPID = DATA_SPACE_ID
) + ']' IndexScript
FROM SYS.INDEXES
WHERE NAME IS NOT NULL
AND Is_Primary_Key =0
AND type_desc ='NONCLUSTERED'
AND OBJECT_ID > 97

3. Execute the below script to generate the DROP INDEX statements

SELECT ' DROP INDEX' + OBJECT_NAME (OBJECT_ID) +'.'+ NAME
FROM SYS.INDEXES
WHERE NAME IS NOT NULL
AND Is_Primary_Key =0
AND type_desc ='NONCLUSTERED'
AND OBJECT_ID > 97
I have continued this article with a modified query to generate index using included columns http://samsudeenb.blogspot.com/2009/07/this-article-is-continuation-of_09.html

SQL Server 2008 CTP is released

Microsoft has released its much-awaited SQL Server 2008 (known popularly as “Katmai”) CTP version that is designed to meet the Data Platform vision of the Microsoft. The SQL Server 2008 capabilities deliver on the four key areas of the data platform vision.

• Mission-Critical Platform –SQL Server 2008 Declarative management Framework (DMF) will allow you to manage your SQL Server configuration across many databases and servers by defining policy rules that are automatically applied, monitored and enforced. SQL Server 2008 also protects valuable information in existing applications and disconnected devices. In addition, SQL Server 2008 delivers predictable query performance with an optimized platform.
• Dynamic Development – SQL Server 2008 along with the .NET Framework enables developers to build the next generation of applications. Developers are more productive because they work with business entities instead of tables and columns. They can build applications that enable users to take their data with them and synchronize their data with back-end servers.
• Beyond Relational Data – SQL Server 2008 supports developers to consume any type of data, from XML to documents and build applications that incorporate location awareness that can solve existing globalization problems.
• Pervasive Business Insight – SQL Server 2008 provides a scalable infrastructure that can manage reports and analysis of any size or complexity while at the same time empowering users because of its close integration with the Microsoft Office System. This enables IT to drive business intelligence throughout the organization. SQL Server 2008 makes great strides in data warehousing, enabling users to consolidate data marts in an enterprise data warehouse.

To know more about Microsoft Data Platform Vision and how SQL Server 2008 meets the needs of the next generation of data-driven applications please find the below white paper from Microsoft

http://www.microsoft.com/sql/techinfo/whitepapers/sql2008Overview.mspx

Microsoft also allows the free download of the CTP version for those registered with the SQL Server 2008 CTP program.

MARS (Multiple Active Result Sets)

MARS for (Multiple Active Result Sets) is a new feature supported in SQL Server 2005 Data access that allows multiple requests to interleave in the server. It allows execution of multiple requests within a single connection through allowing request to run and, within the execution, allows another requests to execute. However execution of MARS is interleaving and not performing parallel execution.

The MARS infrastructure allows multiple batches to execute in an interleaved fashion, though execution can only be switched at well-defined points. As a matter of fact, most statements must run atomically within a batch. The following statements are supported for MARS

• SELECT


• FETCH


• READTEXT


• RECEIVE


• BULK INSERT (or BCP interface)

The behavior of MARS with more than one request running under the same transaction under different scenarios is explained in detail

Conclusion

Support for Multiple Active Result Sets (MARS) in Microsoft SQL Server 2005 increases the performance tuning options of the application. It brings the cursor-programming model closer together with the performance and power of the default-processing mode of the relational engine. However it cannot be considered as a replacement for cursor programming.

Online Indexing in SQL Server 2005

Online Indexing is a new feature available in SQL Server 2005. In SQL Server 2005, DBAs can create, rebuild, or drop indexes online. The index operations on the underlying table can be performed concurrently with update or query operations. During these offline operations, the indexing operations hold exclusive locks on the underlying table and associated indexes. During online index operations, SQL Server 2005 eliminates the need of exclusive locks
Online Index Operation

The online index operation can be divided into three phases Preparation, Build and Final which are explained in detail

Preparation Phase
During the preparation phase, the following activities take place:

• A snapshot of the source table is acquired. When taking a snapshot of the table, a shared lock is obtained on the table. This is done for transactional level read consistency.
• An Intent share lock is also obtained. This lock is maintained until the final phase.
• A resource lock INDEX_BUILD_INTERNAL_RESOURCE is acquired to prevent concurrent DDL operations
• Metadata is created. The logical metadata consists of Index ID, Index name, keys, and attributes

Build Phase

During the build phase:

• The new index structure created in the preparation phase is populated with the sorted data from existing data source.
• The shared lock acquired in the preparation phase is released. Concurrent DML operations can be performed during the Build phase.
• If the clustered index is created or rebuilt online, a temporary nonclustered index, called the mapping index, is created in the build phase.
  

When a clustered index is created or dropped and non-clustered indexes exist for that table, the preparation and build phases are repeated twice; once for the new clustered index and again for the nonclustered indexes

Final Phase

The Final phase is used to inform the system that the index is ready. These are the following activities that take place during this phase:

• The IS lock is upgraded to a Schema Modify (Sch-M) or Shared(S) lock depending on the type of index operation.
• If a clustered index is created, then all the nonclustered indexes are rebuilt. Due to the locks held on the table, concurrent DML operations are not allowed on the table at this time.
• The Sch-M or S lock is released once the index is in the ready state.
  

Online indexing can be performed through the following T-SQL statement as shown below

CREATE INDEX IX_Patient on PDS.Patient (Identifier) (ONLINE=ON)

Best Practices

The following points should be considered before using the online indexing feature

• Backup the transaction log and truncate it before running large-scale index operations online.
• It is recommended to have the SORT_IN_TEMPDB option set to ON. This will separate the index operations and the user transactions.
• Have the recovery model set to SIMPLE or bulk logged so that minimal logging of index transactions takes place.
• Do not run the online index operation in an explicit transaction. The log of the user database cannot be truncated until the explicit transaction ends.

The Online Indexing feature is available only in the Enterprise Edition of SQL Server 2005.

Performance tuning using Include columns in SQL Server 2005

SQL Server 2005 extends the functionality of non clustered indexes by adding non key columns to the leaf level of the non clustered index using the INCLUDE option in the CREATE INDEX statement. These INCLUDE index option is a slight variation of covering index for improved performance. By including non key columns, you can create non clustered indexes that cover more queries. The benefits of using the INCLUDE option (also called non key non clustered index) in the INDEX are

Advantages

• All data types are supported, except text, ntext, and image. So more data type options than a covering index.
• The maximum number of columns that can be included is 1024, where as only 16 in covering indexes.
• Included Columns are not considered by the Database Engine when calculating the number of index key columns or index key size.The actual index is narrower so the key can be more efficient and can offer better performance where as in covering index all of the columns are part of the key.

The include columns indexes are also having the same disadvantages of the covering indexes such as

• More space is required to store indexes with non key columns. Non key column data is stored at both the leaf level of the index and in the table itself.
• Larger indexes mean fewer rows can fit on a page, potentially increasing disk I/O.
• Index maintenance is increased for data modifications, potentially hurting performance if non key columns are large and the database experiences a high level of data modifications.
  

Syntax for INCLUDE column

CREATE INDEX IX_INDEX1
ON dbo.Employee (KEYCOLUMN1)
INCLUDE (NONKEYCOLUMN1, NONKEYCOLUMN2, AND NONKEYCOLUMN3);

However care should be taken before converting the covering indexes to non key / include column indexes. The execution plan of both the INDEX options should be compared before deciding the best INDEX option.