DMV to identify database features restricted to specific edition of SQL Server 2008

If you have configured the user database any of below feature of SQL Server 2008 Enterprise Edition

-- Data Compression
-- Partitioning
-- Transparent Data Encryption
-- Change Data Capture

Above list of features will internally change the way the database engine stores information within the database files. If a database configured to use any of the above features which are specifically designed to be used in Enterprise Edition of SQL Server 2008, it cannot be moved to other editions of SQL Server 2008.

You can check the above feature by querying the sys.dm_db_persisted_sku_features DMV against database.

[sourcecode language="sql"]
SELECT feature_name FROM sys.dm_db_persisted_sku_features ;
GO[/sourcecode]

How to enable Dedicated Administrator Connection Feature in SQL Server 2008?

DAC feature added from the SQL Server 2005 version. Using DAC feature a Database Administrator can connect to a SQL Server Instance when the database engine is not responding to regular connections and troubleshoot the issue.

Advantage of using DAC is DBA can connect to a SQL Server Instance and execute T-SQL commands to troubleshoot and fix issues rather than rebooting the SQL Server which could lead to database corruption or other problems.

By default DAC is disable, it is a best practice to enable the DAC.
We can enable the DAC using below T-SQL command.
[sourcecode language="sql"]
Use master
GO
sp_configure 'show advanced options' , 1
GO
sp_configure 'remote admin connections', 1
GO
RECONFIGURE
GO
[/sourcecode]

In SQL Server 2008 to enable the DAC, right click on SQL Server and Select facets and from facets drop down Select Surface Area Configuration.

How to Turn Off SSMS Auto Recovery Feature

Problem
By default the Auto Recovery feature is enabled for SSMS and because of this when opening SSMS it may hang or become unresponsive for some time if the previous session was closed unexpectedly. There is not a way to turn this feature off from within SSMS, but we will look at how this can be done by modifying some registry entries.

Solution
http://www.mssqltips.com/tip.asp?tip=2352

Steps to Learn Basic SQL

Problem: I have been often posted with a question that, I want to learn T-SQL and I don't know any basics of T-SQL.

Solution:
To start with the Basic T-SQL and practice, there is very good site W3Schools.com You can learn and practice SQL there.

http://www.w3schools.com/sql/default.asp

Change Setting for Recently Used Files in SSMS

Check  it out on http://www.mssqltips.com/tip.asp?tip=2331

Again Awarded as SQL Server MVP

Dear All,

I am again awarded as SQL Server MVP. Keep reading!!! Post Comments!! and give me your suggestion.



Thanks,

Jugal Shah

How to be DBA?

Dear Readers,

I have added a new page to SQLDBPool.com which will guide you for your DBA career.

You can post your comments or DBA Career related question on that page.



Thanks, Jugal Shah

What is .TUF file in Log Shipping?

TUF file is a Microsoft SQL Server Transaction Undo file. .TUF File contains the information regarding any modifications that were made as part of incomplete transactions at the time the backup was performed.

A transaction undo(.TUF) file is required if a database is loaded in read-only state. In this state, further transaction log backups may be applied.

How to be SQL DBA?