Restricting Quest Spotlight access

You may need to restrict access defining who can use Quest Spotlight and at what level. For example, you may want to have junior DBAs read only access on spotlight and reserve the administrative rights with in the senior folks only.

Spotlight for SQL Server version 8.x and above supports this requirement. Please follow below steps to configure security of spotlight:

1.    Go to Go Control Panel -> Administrative Tools-> Computer Management -> Local Users and Groups on the server designated as “Diagnostic server”.

2.    Add users as member below group as per their desired level of rights:

a.    Members of “Quest Diagnostic Administrators” have administrative access.

b.    Members of “Quest Diagnostic Users” have user access.

c.     Members of “Quest Diagnostic Read-Only Users” have read-only access.

Note: As a best practice, do not add any user id directly to these groups. It is always advised to create Active Directory DL for each permission level and then add those DL’s to relevant groups. Individual users should be added or deleted from the Active Directory DL only.

Finding Job Name from Job ID & More Details about Jobs

You may notice that you get Job ID instead of job name in output of SQL trace or in some log. This long value rarely helps us to understand the actual job name. However this Job ID is the unique identifier for the job with in the server.

You may use below code to identify the actual Job Name from Job ID:

SELECT *

FROM msdb.dbo.sysjobs
WHERE job_id = ''

Example:

Below example is taken from a trace output where Job ID is reported as “0xEE3F000650732D4DB127DF7C7F95ED44”. (Please note that you should replace 0xEE3F000650732D4DB127DF7C7F95ED44 with the Job ID you are working with.)

EXECUTE msdb.dbo.sp_sqlagent_log_jobhistory @job_id = 0xEE3F000650732D4DB127DF7C7F95ED44, @step_id = 2, @sql_message_id = 15457, @sql_severity = 0, @run_status = 1, @run_date = 20100909, @run_time = 113501, @run_duration = 0, @operator_id_emailed = 0, @operator_id_netsent = 0, @operator_id_paged = 0, @retries_attempted = 0, @session_id = 180, @message = N'Executed as user: <> Configuration option ''Agent XPs'' changed from 1 to 1. Run the RECONFIGURE statement to install. [SQLSTATE 01000] (Message 15457). The step succeeded.'

You can find the Job Name whose Job ID is 0xEE3F000650732D4DB127DF7C7F95ED44 using below code:

SELECT *
FROM msdb.dbo.sysjobs
WHERE job_id = '0xEE3F000650732D4DB127DF7C7F95ED44'

Querying msdb.dbo.sysjobs gives you more interesting information about the jobs too. For example, you may use below code to find the improtant facts about jobs like when the job last ran, what was the last execution time of the job etc. You may even use a where clause to further find details about the a specific job with below code:

SELECT [sysjobs].[name] AS N'Job Name',
[sysjobsteps].[step_name] AS 'Step Name',
[sysjobsteps].[command] AS N'Step',
[sysjobsteps].[database_name] AS 'Database Name',
[sysjobsteps].[output_file_name] AS 'Output File',
[sysjobsteps].[last_run_date] AS 'Last Execution Date',
[sysjobsteps].[last_run_time] AS 'Last Execution Time',
[sysjobs].[date_modified] AS 'Job Last Modified',
[sysjobs].[version_number] AS 'Version Number'
FROM [msdb].[dbo].[sysjobsteps]INNER JOIN [msdb].[dbo].[sysjobs]ON [msdb].[dbo].[sysjobsteps].[job_id] = [msdb].[dbo].[sysjobs].[job_id];

Please refer to http://msdn.microsoft.com/en-us/library/ms189817.aspx for more details on msdb.dbo.sysjobs and related information.

New Licensing Model of SQL Server 2012 (Denali), how to handle its impact on IT budget and plan of action

Denali is now formally announced as SQL Server 2012 and will be released in 2012. SQL 2012 will have many reasons for companies to upgrade but it will come with a major catch! “Cost Increase”. 

(Although this is first major price hike of SQL Server since SQL 2005. So I guess this hike is not unfair. There was a minor hike for 2008 R2 also. Please refer to http://tonymackelworth.wordpress.com/2010/04/24/pricing-increase-sql-server/ for details on the same.)

Here are some major changes in SQL Server 2012’s licensing model which will have impact on budget:

1. SQL Server license is now “Per Core” based. For example, if you have a server running with 2 quad- core processor processors, you will need to license 8 cores (4*2=8) instead of licensing 2 physical processors.

2. Enterprise edition will no more support CAL based license. That means you will need to migrate licensing of all Enterprise edition instances to “per core “licensing model even if your present instance is licensed on CAL.

3. Standard Edition will support CAL and core based licensing.

4. Business Intelligence Editions is a new edition for SQL 2012. It will support only CAL based licensing.

Impact on budget:

No matter how we do the mathematics, SQL 2012 is going to be more expensive compared to previous versions. 

Example of price change for Level A licensing agreements:

Business Intelligence Editions -> $7026/Server (Same as Enterprise Edition)
Standard Editions -> $734/Server (No cost increase)
CALs -> $207/CAL (This is a $45 increase/CAL)

Let us use below possible scenarios to analyze the situations:

Possibility 1: You have a Standard Server with 100 CAL. Compared to today’s cost, you will spend $4500 ($45*100) more for CAL licensing.

Possibility 2: You have an Enterprise Server with 100 CAL. For moving to SQL 2012, you need to move to per core license. 

How your SA (Software Assurance) program can save your budget:

Microsoft did not forget old customers. This is good news indeed. If you have valid software assurance (SA) for your SQL Servers, you can minimize the impact on your budget. Contact with your Microsoft Reseller or Rep as soon as possible to discuss about your options. Generally if you complete the upgrade to SQL 2012 while you have a valid SA and within a prescribed time (which can be decided with you and your MS Rep/re-seller) then you can get up to 4 free core licenses per processor. For example, if you have a SQL Enterprise Edition running on 2 physical quad core processors, then you do not pay anything extra as long as you have your existing edition licensed for 2 processors. Depending on your relationship with Microsoft, you may actually get all the required number of core licenses free even if you are running with more powerful processors.

That is, if you are having 8 core/processor in the above example, Microsoft may actually license your 16 cores for free. (8 cores * 2 processors = 16 core license required). This can save a good amount.

You should start discussing with your MS rep/reseller to discuss how to move from CAL based license to per core based license (if applicable) and how to minimize impact of the price impact of CAL. You may have to pay the price difference per CAL depending on your relationship with Microsoft.

Recommended Plan of action to minimize impact on budget:

If you are DBA or the person involved with licensing, you should be active now to minimize impact from this change. I recommend taking below steps as soon as you can:

Do an inventory (or revalidate it) of your SQL Server and licenses and engage with MS Rep to figure out best possible option for you.

Although SQL 2012 comes with a higher price tag that does not mean that your budget will be seriously impacted depending on your relationship with Microsoft. So consider to prioritize your SQL 2012 migrations to take full benefits of SA. (Per my best knowledge, we will get around 36 months after the release of 2012 to maximize the opportunities to avoid cost increase).

SQL 2012 Enterprise Edition comes with true unlimited virtualization. Consider exploring this option for future expansion if you are running data center editions of Windows.

How to find last backup times of all databases on a SQL Server?

Please use below code to find last backup times of all databases hosted on a SQL Server instance:

-- Code to print last backup time of all database on a server
SELECT db.Name AS DatabaseName,

MAX(bus.backup_finish_date) AS LastBackUpTime

FROM sys.sysdatabases db

LEFT OUTER JOIN msdb.dbo.backupset bus ON bus.database_name = db.name

GROUP BY db.Name

order by LastBackUpTime asc

Note: This code is tested on SQL 2005/2008/2008R2 and 2012

SQL Server 2000 DTS Designer components are required to edit DTS packages. Install the special Web download, "SQL Server 2000 DTS Designer Components" to use this feature. (Microsoft.SqlServer.DtsObjectExplorerUI) – SQL Server 2008/2008 R2 Management Studio throws error while opening a DTS package

If you try to open a DTS package in SQL 2008/2008 R2 Managgment studion, you may get an error as below which prevents you even from opening the package:

SQL Server 2000 DTS Designer components are required to edit DTS packages. Install the special Web download, "SQL Server 2000 DTS Designer Components" to use this feature.

An example screenprint of the error message is as below:

Another common error we may experience while opening DTS in SQL 2008/2008 R2 SSMS is as below:

The DTS host failed to load or save the package properly

Please follow below steps to resolve this problem and to open, design,modify and execute DTS
packages with Legacy components on SQL 2008 / 2008 R2

1. Install SQL 2000 DTS Designer Components on the machine where you are working (that is where SSMS is installed). Select SQLServer2005_DTS.msi  file from http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=11988 to download this tool.

2. Install Sql Server 2005 backward compatibility components available with Microsoft SQL Server 2008 Feature Pack. Select appropriate file depending on your architecture (x86,x64 and IA64) from http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=16177 and intstall on the machine where you are working (that is where SSMS is installed).

3. Manually Copy following files
%Program Files%\Microsoft SQL Server\80\Tools\Binn\semsfc.dll
%Program Files%\Microsoft SQL Server\80\Tools\Binn\sqlgui.dll
%Program Files%\Microsoft SQL Server\80\Tools\Binn\sqlsvc.dll
%Program Files%\Microsoft SQL Server\80\Tools\Binn\Resources\%lang_id%\semsfc.rll
%Program Files%\Microsoft SQL Server\80\Tools\Binn\Resources\%lang_id%\sqlgui.rll
%Program Files%\Microsoft SQL Server\80\Tools\Binn\Resources\%lang_id%\sqlsvc.rll
To the following destination respectively
%Program Files%\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\semsfc.dll
%Program Files%\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\sqlgui.dll
%Program Files%\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\sqlsvc.dll
%Program Files%\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\%lang_id%\semsfc.dll
%Program Files%\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\%lang_id%\sqlgui.dll
%Program Files%\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\%lang_id%\sqlsvc.dll
%Program Files%\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Resources\%lang_id%\semsfc.rll
%Program Files%\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Resources\%lang_id%\sqlgui.rll
%Program Files%\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Resources\%lang_id%\sqlsvc.rll

4. Repair Sql Server 2005 backward compatibility by reexecuting the file you downladed at Step#2 or from control panel’s add/remove program.

Note:

1. If above step does not work then most likely you need to replace stardds.dll as per Step#5 of http://blogs.msdn.com/b/sqlserverfaq/archive/2009/07/09/error-sql-server-2000-dts-designer-components-are-required-to-edit-dts-packages-install-the-special-web-download-sql-server-2000-dts-designer-components-to-use-this-feature-microsoft-sqlserver-dtsobjectexplorerui-ssms.aspx

2. If you are facing issues for opening DTS in BIDS (Buisness Intelengience Development Studio), then please refer to Step 3 at http://blogs.msdn.com/b/sqlserverfaq/archive/2009/07/09/error-sql-server-2000-dts-designer-components-are-required-to-edit-dts-packages-install-the-special-web-download-sql-server-2000-dts-designer-components-to-use-this-feature-microsoft-sqlserver-dtsobjectexplorerui-ssms.aspx for the list of files you need to copy from %Program Files%\Microsoft SQL Server\80\Tools\Binn to %Program Files%\Microsoft Visual Studio 9.0\Common7\IDE and it’s subdirectory.

3. I tested the steps listed above and I works fine. However please understand this is a workaround suggested by Microsoft PSS at http://blogs.msdn.com/b/sqlserverfaq/archive/2009/07/09/error-sql-server-2000-dts-designer-components-are-required-to-edit-dts-packages-install-the-special-web-download-sql-server-2000-dts-designer-components-to-use-this-feature-microsoft-sqlserver-dtsobjectexplorerui-ssms.aspx but Microsoft does not support this procedure formally.

4. I do not recommend to execute these steps on a server’s management studio. You should execute these steps and work on DTS from a workstation where SQL 2008 / 2008 R2 management studio (SSMS) is installed.

5. If you are running DTS on a SQL 2005 or later server then you should consider migrating to SSIS.

SQL 2012 on Windows Core Edition

One good reason for moving to SQL 2012 will be it’s capability to run on Server Core Editions.

As core servers need less mainteaince and offer reduced attack surface, this will be a direct benefit on SQL Server 2012 also if it the instance is hosted on a core server.  Basically all the advantegs of running a server core will be passed to SQL Server too.

For more details on advantages of running a core server please refer to http://msdn.microsoft.com/en-us/library/ee391628(v=vs.85).aspx

For details on installing SQL 2012 on a server core please refer to http://msdn.microsoft.com/en-us/library/hh231669(v=sql.110).aspx

Msg 3283, Level 16, State 1, Line 1 – Restore fails for a database which was encrypted using TDE (transparent data encryption) but now encryption is disabled

When you disable encryption on a database which was using TDE (Transparent data encryption) and then take it’s backup and try to restore it on another instance (which does not have the certificate of original encrypted instance), you may experience below error:

Msg 3283, Level 16, State 1, Line 1

The file " " failed to initialize correctly. Examine the error logs for more details.

Msg 3013, Level 16, State 1, Line 1

RESTORE DATABASE is terminating abnormally.

Although restore process completes, database never starts and essentialy backup file remains unusable.

Reason for this issue:

(Microsoft’s statement at https://connect.microsoft.com/SQLServer/feedback/details/351548/rc0-engine-disabling-tde-from-a-database-does-not-allow-it-to-be-attached-restored-later):

When a key is added to the database it is required to start the database. Just turning encryption off does not remove the key and it should be removed manually after the need for it is removed. Which brings up the other point. The log is encrypted on a logical file granularity so the data is encrypted/decrypted by the scan, but the log must wait for the logical log to switch to the next file to change keys/encryption. There may still be a need for the key if the last checkpoint or open transaction at the last checkpoint goes back into the prior logical log files as they need to be read to perform recovery. The encryption status from the DMV will indicate if a scan is still in progress and what the current keys are - it will show in progress until the logical log file rolls over.

As a general rule ake sure to keep all keys/certs around as you will need them for any backup spanning when they were active.

Please follow below steps to resolve this situation:

Step 1: Make sure that your SQL Servers are updated appropiately. If you are running with latest service pack as of today (SP1 for 2008R2 or SP3 for 2008) then you should not have to do anything on this step. Please refer to http://support.microsoft.com/kb/2300689 for details from Microsoft on this known issue.

Step 2: Disable TDE completely on the database before you take the backup.

Execute below list of commands to completely disable TDE on the database in question:

A.

SELECT DB_NAME(database_id) AS DatabaseName, key_algorithm,key_length,encryption_state FROM sys.dm_database_encryption_keys

where DB_NAME(database_id) = 'Database Name’

You should receive encryption_state of the dabase as 3 which confirms database in encrypted.

B.

USE [master]

GO

ALTER DATABASE [Database Name] SET ENCRYPTION OFF

GO

C.

SELECT DB_NAME(database_id) AS DatabaseName, key_algorithm,key_length,encryption_state FROM sys.dm_database_encryption_keys

where DB_NAME(database_id) = 'Database Name’

Now you should receive encryption_state of the dabase as 1 which confirms decryption is complete. This process may take sometime for a bigger database based on your environment.

D.

Caution: Execute this step only when C is completed.

use [Database Name]

drop database encryption key

go

E.

SELECT DB_NAME(database_id) AS DatabaseName, key_algorithm,key_length,encryption_state FROM sys.dm_database_encryption_keys

where DB_NAME(database_id) = 'Database Name’

Now this should not return any data as key is dropped succesfully. At this point database is completely restorable to any other comtable system.

Step 3: Follow your standard backup-restore procedure and you will not need any special measure to restore this database on any other compatible instance. However please be advised that any backup taken before you disabled TDE completely (Per Step 2 of this post) remains encrypted and can not be restored with out restoring original certificate from source server to destination one.


Note: When you received the original error, although the database never started (ie. Never came online), they were technically restoring. So you will see them in your database list with a status of “RECOVERY_PENDING” . You may want to drop such database to clean your instance.

Moved

I changed job and moved to a great new country. This was the reason for my long pause on this blog. However I expect to work on this blog from now again and try to share my experience with you all.

Thanks again for your patronage.

SSIS is copying files from/to network path (share) while running from business studio (BI) but failing when executed from SQL Server as job (Error Code : 0xC020200E)

Most likely you have a SSIS package to copy file (data) to or from a network location (which is a mapped drive at your server) and the package works perfectly when you run it from business development studio. But problem starts when you run it as a SQL Server job and it fails throwing errors like below:

Executed as user: [ ]. Microsoft (R) SQL Server Execute Package Utility  Version 10.50.1600.1 for 64-bit  Copyright (C) Microsoft Corporation 2010. All rights reserved.    Started:  [ ]  Error: [ ]  Code: 0xC020200E     Source: Data Flow Component Flat File Source [33]     Description: Cannot open the datafile "[  ]".  End Error  Error: [ ]     Code: 0xC004701A     Source: Data Flow Component SSIS.Pipeline     Description: component "Flat File Source" (33) failed the pre-execute phase and returned error code 0xC020200E.  End Error  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  [ ]  Finished: [ ]  Elapsed:  [ ].  The package execution failed.  The step failed.

Solution to this file is actually using UNC path of the network file instead of mapped drive name is flat file connection manager.

For example, if your network file is at \\Servername\Path\File.txt and mapped as Z$ on your server, then in Flat file connection manager, use \\Servername\Path\File.txt instead of  z:\File.txt to resolve this problem.

Wide tables in SQL Server 2008

SQL Server table supports up to 1024 columns per table which is typically good enough for most of the cases. However if you need to expand a table beyond 1024 columns then you need to use a special table types named as Wide Tables. Wide tables are tables which can have more than 1024 columns and can actually have up to 30,000 columns. For in depth details on wide table, you may want refer http://msdn.microsoft.com/en-us/library/ms186986.aspx

In this post I will discuss using wide tables in practical life without diving deep into the architecture:

Should I use/encourage using wide tables?

Simple Answer to this is No. Wide table uses sparse columns to increase the total of columns. This technology creates issues like performance problems, complications with index management, limits execution of DML statements, reduce performance for switch partition operations, prevents using compression etc. Hence unless absolutely necessary, it is not advisable to use wide tables. You may recommend using other technologies like joins or xml instead of using wide tables.

For more details on possible performance consideration for wide tables please refer to http://msdn.microsoft.com/en-us/library/cc645884.aspx

How to create a wide table? (How to create a table which will have more than 1024 columns?)

Step 1:

Create a column set on the table using below code:

[Note: This must be first step before adding any sparse column to the table. If the table already has any sparse column then you cannot add column set. Also if you cannot add a column set then your table can not have more than 1024 columns even if you attempt to add new columns as sparse column]

ALTER TABLE [table name]

ADD CS XML COLUMN_SET FOR ALL_SPARSE_COLUMNS ;

GO

Example:

ALTER TABLE dbo.table1

ADD Widecolumn1 varchar(10) SPARSE NULL ;

GO

Step 2:

Now you can add columns and widen the table as you need.

[Note: New columns must be sparse columns assuming you already have 1024 traditional (non-sparse) columns. You cannot add more than 1024 non-sparse columns even if the table has column set.]

ALTER TABLE [table name]

ADD [Column Name] [Datatype] SPARSE NULL ;

GO

Example:

ALTER TABLE dbo.table1

ADD Widecolumn1 varchar(10) SPARSE NULL ;

GO

What I need to know while using sparse column?

Please refer to http://msdn.microsoft.com/en-us/library/cc280604.aspx for details. However below tips are useful for immediate references:

1. geography, text, geometry, timestamp, image, user-defined data types, ntext cannot be used as a sparse column data type.
2. SELECT…INTO statement does not copy over the sparse column property into a new table.
3. A sparse column must be nullable and cannot have the ROWGUIDCOL or IDENTITY properties
4. A sparse column cannot have a default value or cannot be bound to a rule.
5. A sparse column cannot be part of a clustered index or a unique primary key index.
6. Although a computed column can contain a sparse column, a computed column cannot be marked as SPARSE

How to use data (read/write/alter) from a wide table?

You can use traditional statements to read/write/alter data in a wide table. However you should prefer using column set for data modification or retrieval for all sparse columns in a wide table instead of using traditional statements. Please refer to http://msdn.microsoft.com/en-us/library/cc280521.aspx for details on using column set.