The SQL Dude!

On-Disk & In-Memory Pages that teach you the SQL way of life!

Posts Tagged ‘SQL Server’

TSQL script to find Foreign Key Dependencies on entire database

Posted by Sudarshan Narasimhan on February 14, 2012



 

Often in the life of a DBA/Developer when we have to test something on a database, we are not always aware of the schema/design since it was created by someone else or you inherited the project. Lets says you want to test a particular SP execution time, but the data sample is too big for you to wait for the restore, so you want to insert some sample data. More often that not, the constraints defined do not let you to modify/add/remove data from the base tables. Well, you can’t pull up the entire database diagram and start studying the design.

It is not practical to do this on the entire database with Management Studio even though it offers a nice View dependencies option, but this is on a table by table basis. You would need to use T-SQL for this.

 

Here is a script which you can run on the entire database to understand which tables are dependent on your Table X,Y,Z and what are the columns being referenced (foreign keys) and what is the action on update/delete. You can uncomment the filter in the WHERE clause if you want to specify only some table names.

Script Modified: April 18, 2012

 

SELECT ROW_NUMBER() OVER (ORDER BY OBJECT_NAME(fk.referenced_object_id), clm1.name) as 'S.No',
       fk.referenced_object_id as ReferencedObjID,
       constraint_column_id as ColumnID,
       OBJECT_NAME(fk.referenced_object_id) as [ReferencedTable(Parent)],
       SCHEMA_NAME (CAST(OBJECTPROPERTYEX(fk.referenced_object_id,N'SchemaId') AS tinyint)) as [ParentSchema],
       clm2.name as ReferencedColumnName,
       OBJECT_NAME(constraint_object_id) as ConstraintName,
       fk.parent_object_id as ReferencingObjID,
       OBJECT_NAME(fk.parent_object_id) as [ReferencingTable (Foreign)],
       clm1.name as ForeignKeyColumn,
       SCHEMA_NAME (CAST(OBJECTPROPERTYEX(fk.parent_object_id,N'SchemaId') AS tinyint)) as [ForeignSchema],
       [Action on Update] = CONVERT(varchar,CASE OBJECTPROPERTY(constraint_object_id,'CnstIsUpdateCascade')  
                                        WHEN 1 THEN 'CASCADE'
                                        ELSE 'NO_ACTION'
                                      END), 
       [Action on Delete] = CONVERT(varchar,CASE OBJECTPROPERTY(constraint_object_id,'CnstIsDeleteCascade')  
                                        WHEN 1 THEN 'CASCADE'
                                        ELSE 'NO_ACTION'
                                      END)
FROM sys.foreign_key_columns fk
       JOIN sys.columns clm1 
         ON fk.parent_column_id = clm1.column_id 
            AND fk.parent_object_id = clm1.object_id
       JOIN sys.columns clm2
         ON fk.referenced_column_id = clm2.column_id 
            AND fk.referenced_object_id= clm2.object_id
 --WHERE OBJECT_NAME(fk.referenced_object_id) = 'TBL_TEST'  --- table name which is being referenced by other tables via Foreign Keys
 ORDER BY OBJECT_NAME(fk.referenced_object_id)

 

Here is the how the output looks like on the AdventureWorks database

image

Advertisement

Posted in T-SQL | Tagged: , , , , , , | 9 Comments »

SQL Server Setup: How to validate downloaded image/media before installation

Posted by Sudarshan Narasimhan on January 27, 2012


You might be wondering why I am posting about media validation. Isn’t this a given thing, after all you are downloading straight from downloads.microsoft.com, so there should be no issues with the downloaded media (.ISO), right? Wrong!

Even if you are downloading from a trustworthy source, always ensure that you have validated the media once the download is complete. For SQL Server ISO images which you downloaded from MSDN or ordered a DVD, it is a best practise to verify that the media is clean & complete. Don’t believe me, have a look at this article for the sort of setup issues like MSI Error: 2337, that you can run into, if you don’t want to spend 5 minutes to verify media integrity 🙂


Various errors may occur when you try to install SQL Server 2008 R2
http://support.microsoft.com/kb/2449398

 

Save yourself the time & trouble with troubleshooting a failed setup. Do the following simple steps to validate SQL Server setup media:-

1. Verify ISO is valid

A) Once you have downloaded an ISO from MSDN, use Microsoft File Checksum Integrity Verifier aka FCIV.exe to verify the checksum. This tool computes and verifies the hashes of any file and will give you a MD5 or SHA1 hash key as the output.

Lets say you downloaded the media file for SQL Server 2008 R2 Standard Edition en_sql_server_2008_r2_standard_x86_x64_ia64_dvd_521546.iso to C:\SQL\ folder from MSDN website.

Download FCIV.exe from the above link and use it as shown below to validate the above ISO file.

image


Note down the SHA1 hash value highlighted above.

B) Login to your MSDN subscription and go to the Subscriber Downloads page and identify the package with same version and edition you downloaded. Click on the Details button and you will see a SHA1 hash value displayed there, like shown below.

image

This value (step A) should match the one of your downloaded ISO package (step B), which you found out using FCIV.exe If it does, then the downloaded package is valid and has no errors/issues/inconsistencies in files etc. You can begin setup using this package and it is a valid & verified stable ISO package.

 

2. Extraction from the ISO Package

The most common practice once you have downloaded an ISO image is to extract its contents to a folder structure. Most of you will have to perform these installations on multiple machines and you will extract this and put it on a common file share. I have seen issues where the extraction of these ISO packages sometimes causes inconsistencies in the media like missing files, invalid files etc. This is usually brought about by the software you used to extract from the ISO like WinRAR, WinZip, MagicISO and others.

Q. What is the correct software to use to extract this media?
Ans. My favourite answer – “It depends”. Smile

Let me explain. I am not going to suggest one piece of software over another. Instead, I’ll take a different approach to this issue.

1. Extract the verified ISO package using any of the above mentioned software. Lets assume that the folder which contains the extracted media is called “C:\SQL\SQL2008R2Media”.

2. Use FCIV.exe to generate a hash database for this extracted folder, like show below,

fciv.exe -add "C:\SQL\SQL2008R2Media" -r -XML C:\SQL\db.xml

image

What this does is computes a SHA1 hash for each file under this directory and stores it in the db.xml file. This is the way to validate an entire directory and its contents.

3. Install the SQL Server using this media. It is a successful installation and things go fine, then you know 100% that this media copy/folder/package is a good one to use for further installations.

This also proves that the extraction software used in step #1 above can be used again (same version of the s/w to be specific).
In future if you are copying this media for installation to any other machine or USB drive or on DVD etc etc, you have to validate the new destination by comparing it with the db.xml that we created above.

Here is how you do that,

Location of SQL Server directory: C:\Program Files\Microsoft SQL Server\
Location of my db.xml file: C:\SQL

Navigate to the SQL Server folder in the command prompt
1) cd C:\Program Files\Microsoft SQL Server\
2) fciv -v -xml C:\test\db.xml

where –v is to verify the hashes and –xml is to use the db.xml which we know is a good source and compare that for the files in the SQL Server folder.

That’s all there is to it. Your SQL Server media is verified clean and green. Don’t forget to put a ISO stamp on it Winking smile. Stay tuned for more SQL’lly stuff.

Posted in Setup | Tagged: , , , , , , , , , | 8 Comments »

SQL Saturday # 116: Be there, it’s the real deal!

Posted by Sudarshan Narasimhan on January 7, 2012


For all SQL Server folks in India, please bookmark your calendar for the SQL Saturday #116 (organised by PASS and Microsoft) as this is the first time a SQL Saturday event is being organized in Bangalore at the Microsoft Office on January 7th, 2012.

I will be attending this event and will also be a part of the SQL Server Clinic, which starts @ 2:30 pm – 5:30 pm IST. SQL Clinic is an open event where you, your colleagues and other SQL enthusiasts can come, meet, discuss and ask questions to MSFT CSS engineers. I will be one of those engineers whom you can pester with any questions on SQL Server Smile. You are free to bring real-world SQL problems that you face in your work and we will be happy to shed some light on them. If you have a specific SQL problem that has been bugging you for weeks, this is THE Forum to discuss them! It helps if you bring the details like logs, traces, dump files etc., as live troubleshooting and analysis is very much a part of SQL Clinic. Come, join in…


Directions to the Microsoft GTSC Campus in Bangalore

Bing Maps Link

Google Maps Link

A favourite quote of mine
“Unfortunately, no one can be told what the Matrix is. You have to see it for yourself. So in a world of 1s and 0s…are you a zero, or The One?”

Posted in SQL News | Tagged: , , , , , , | Leave a Comment »

How To: SQL Server Bulk Insert with Constrained Delegation (Access is Denied)

Posted by Sudarshan Narasimhan on December 30, 2011


Well folks, I’ve not been able to blog for sometime as I was caught up with work. But fear not, these past 2 months have given me enough subject material for multiple blog posts. Let me start off with one regarding setting up SQL Server for bulk inserts from a network file share when constrained delegation is setup in Active Directory.

For SQL Server folks out there, I don’t expect you to know about Delegation let alone Constrained vs. Unconstrained.

Q&A

Q: What is Delegation?
A: A client connected to an instance of SQL Server can connect to another instance of SQL Server or another machine by forwarding the credentials of an authenticated Windows user.

E.g. Domain\User1 on Client1 connects to –> SQL Server SQL1 and accesses a remote file on –> Server2, using his own credentials i.e. Domain\User1

It is the role of the SQL Server machine SQL1 to impersonate/delegate that user when communicating with Server2. For this to happen, the SQL Server has to be configured to allow delegation (in Active Directory).

Q: What is Constrained Delegation?
A: This feature allows administrators to specify exactly which services a server or a domain account can access when using an impersonated user’s security context. More on this later. If there is no such restriction configured, then it is called as unconstrained delegation, where every service for that user can perform impersonation aka delegation. It is a security best practice to perform constrained delegation as it reduces the surface area of any attacks.

Environment

Here is a brief layout of the environment that I am going to use in this post as a reference.

image

This is a basic 3 machine architecture that is very common. Here are the various parties involved:-

  1. Client Computer – Windows PC/Workstation/Application Server: machine name TRINITY1, Client account Domain\appadmin
  2. SQL Server – Windows Server 2003+ running SQL Server 2005/2008/2008 R2 standalone instance: machine name NEOSQL, SQLSvc account Domain\sqladmin
  3. File Share on another Windows Server (basically a shared folder): machine name MORPHEUS1, where user Domain\appadmin has Full Control on the shared folder.

 

Scenario/Requirement

In an ideal scenario, here is how I want things to work. When a client application runs the SQL BULK INSERT command logged in as Domain\appadmin account to SQL Server, I want the same appadmin account to access the remote file share and read the data.

1. If a user uses a SQL Server login to connect to SQL, then the SQL Service account credentials are used to access the remote file share.

2. If a user uses a Windows account, then his own account is used to access the file share and for this to work successfully, delegation has to be configured.

BULK INSERT appdb.dbo.bulktbl

FROM ‘\\morpheus1\share\data.txt’

If not configured correctly then you will get this error when running the above bulk insert command.

Msg 4861, Level 16, State 1, Line 3

Cannot bulk load because the file "\\morpheus1\share\data.txt " could not be opened. Operating system error code 5(Access is denied.).

Configuring Unconstrained Delegation

1. Configuring permissions on the shared folder on Morpheus1. As you can see the appadmin windows account has Full Control and the sqladmin account has Read/Write permission (if any sql login is going to be used, this is not mandatory).

image

2. I am assuming that the SQL Server is running under the service account Domain\sqladmin. Login to the Domain Controller with Domain Admin rights and open up Active Directory Users and Computers MMC snap-in.

a. Make sure that the following is NOT checked.

image

b. The SQL Service account needs SPN’s (Service Principal Names) to be created before it can be configured for delegation. You can use the SetSPN.exe tool that is available with Windows SDK or sysinternals toolkit to create the SPN’s. This tool is also available bundled along with Windows Server 2008.

We need to create 4 SPN’s for the account running SQL Server service as shown below. (2 with netbios names and 2 with FQDN). Note: The command below is for a standalone default instance of SQL Server running on default port 1433. Please modify as applicable to your environment.

Setspn -A MSSQLSvc/neosql thematrix\sqladmin
Setspn -A MSSQLSvc/neosql:1433 thematrix\sqladmin
Setspn -A MSSQLSvc/neosql.thematrix.sudarn.com thematrix\sqladmin
Setspn -A MSSQLSvc/neosql.thematrix.sudarn.com:1433 thematrix\sqladmin

Once done you can query the SPN’s using setspn.exe and it should list you these 4 SPN’s.

image

c. Switch to the Delegation Tab and select the radio button by Trust this computer for delegation to any service (Kerberos only). We are setting up unconstrained delegation if this option is chosen. If you do not see the Delegation tab available, then there was some issue with creating the SPN’s in step (b) listed above.

image

d. Next find the actual machine account in Active Directory for the SQL Server machine NEOSQL and set the computer account to be trusted for delegation, as we did above.

image

e. Next, we need to check the file server where the file we want to import is located, i.e. MORPHEUS1. We need to verify that this machine has the normal 2 HOST SPN’s registered.

image

This is required to use Kerberos for authentication. Delegation will not work without this, and you will receive an error otherwise. By default each machine should have 2 HOST SPN’s created for it.

 

3. On the SQL Server machine NEOSQL, open up the Local Security Policy by going to secpol.msc –> Computer Configuration\Windows Settings\Security Settings\Local Policies\User Rights Assignment, and add the sql account Domain\sqladmin to the following policies:

  • Act as part of the operating system
  • Impersonate a client after authentication

So far the configuration given above is for unconstrained delegation. We need to make sure that this setup works before we can configure constrained delegation. Once you are done till there, reboot the SQL Server machine (NEOSQL) and the file share machine (Morpheus1), so that all the changes we made in Active Directory are reflected when the machine starts up.

Testing

Make a test connection from the client machine (TRINITY1) using sqlcmd or SSMS. Open up another connection from SSMS and run the following query to find out if the connection from the client machine is using Kerberos authentication.

select b.spid, b.hostname, b.program_name, a.auth_scheme

from sys.dm_exec_connections a

inner join sys.sysprocesses b

on a.session_id = b.spid

image

The connection from the client machine should return KERBEROS. This is required for delegation to work. If it did, then you are good to run the bulk insert statement and it should work.

Configuring Constrained Delegation

So far so good. Now comes the part that is the crux of this blog post. If you were successful in getting things to work, then read on….

1. Open up Active Directory Users and Computers MMC snap-in with a Domain Admin account as before. Open up the Domain\sqladmin account and switch to the Delegation Tab.

2. Choose the option “Trust this computer for delegation to specified services only”, select “Use any authentication protocol” and click on the “Add…” button.

image

In the dialog to select the sqladmin account (or your account which runs the SQL service).

image

 

This should list the 2 SPN’s we have previously created for this account. Select both of them and click OK.

image

Click on the “Add…” button again and enter the SQL Server machine name (NEOSQL) and choose the HOST service.

image

Click on the “Add…” button again and enter the File Share machine name (MORPHEUS1) and choose the HOST Service. We are actually interested only in the CIFS and Protected Storage service. Choosing HOST will automatically choose these 2 services.

 

image

 

After adding all of these, this is how your final configuration for the sqladmin account will look like.

image

We need the SQLSvc account (Domain\sqladmin)and file share server to have CIFS service enabled for delegation because we are accessing a remote file share and it is the role of the CIFS Service (Common Internet File Share) to perform this. If we do not have CIFS (which comes when we added the HOST Service), then your account (Domain\appadmin) from the client machine (TRINITY1) will reach the file share server (MORPHEUS1) as NT AUTHORITY\ANONYMOUS LOGON and this will not have access and fail with 0x5 (Access is Denied).

I tested this by enabling auditing on the shared folder and saw this. This is a easy test to perform to check if delegation is working or not

When the bulk insert fails with access denied we will see this in the security event log of the file server (MORPHEUS1),

Log Name:      Security
Source:        Microsoft-Windows-Security-Auditing
Date:          12/20/2011 11:17:33 PM
Event ID:      4624
Task Category: Logon
Level:         Information
Keywords:      Audit Success
User:          N/A
Computer:      MORPHEUS1.THEMATRIX.sudarn.com
Description:
An account was successfully logged on.

Subject:
    Security ID:        NULL SID
    Account Name:        –
    Account Domain:        –
    Logon ID:        0x0

Logon Type:            3

New Logon:
    Security ID:        ANONYMOUS LOGON
    Account Name:        ANONYMOUS LOGON
    Account Domain:        NT AUTHORITY

That’s all folks! Keep in mind that this configuration requires a good bit of handling with Active Directory and/or policies. Please work with your Domain Administrator while making these changes. As always, stay tuned for more… Cheers and a happy new 2012 to y’all!

 

Posted in Security | Tagged: , , , , , , , , , | 34 Comments »

To SSL or not to SSL, that is the question….

Posted by Sudarshan Narasimhan on October 22, 2011


A lot of database administrators out there would have heard of SSL (Secure Sockets Layer) and the security it offers, and thus want to configure their SQL Servers to use SSL for encryption of data on the wire. Bear in mind that SSL Encryption is very different from the built-in database encryption features like TDE. SSL is used for encrypting data transmitted across the network (on the wire) between the SQL Server and a client/application. Once configured all traffic between SQL Server and a client application is encrypted which has its advantages (secure transmission) and its disadvantages (performance impact due to the encryption and extra round-trip at connection time). SSL is a powerful feature that is a given standard of most systems and will definitely feature in a list of To-Have items if any security audit or standards are enforced in your company.

In this post I am not going to talk about what the SSL/IPSec standards, but going to focus on how to get a SSL configured for your SQL Server instance. Anyone who has setup SSL for SQL Server before might have encountered a situation where the certificate from a trusted authority does not get detected in SQL Configuration Manager. I am going to elaborate how to tackle that issue as well, so read on and stay safe!


What do you need for SSL?

Certificates! You will need to purchase/provision a certificate from a certificate authority such as VeriSign or GoDaddy.com


Pre-Requisite Reading Material

Encrypting Connections to SQL Server
http://msdn.microsoft.com/en-us/library/ms189067.aspx

How to enable SSL encryption for an instance of SQL Server by using Microsoft Management Console
http://support.microsoft.com/default.aspx?scid=kb;en-us;316898

How to Enable Channel Encryption
http://blogs.msdn.com/b/sql_protocols/archive/2005/10/04/476705.aspx

SQL Server fails to start with error 17182 "TDSSNIClient initialization failed with error 0xd, status code 0x38" when server is configured to use SSL
http://support.microsoft.com/default.aspx?scid=kb;en-US;2023869

I would highly recommend you read the above mentioned articles from MSDN and only then proceed with the steps listed below. Before going further I want to highlight a few important points from the above article regarding Certificate requirements. For SQL Server to load a SSL certificate, these are the requirements.

  1. The certificate must be in either the local computer certificate store or the current user certificate store.
  2. The current system time must be after the Valid from property of the certificate and before the Valid to property of the certificate.
  3. The certificate must be meant for server authentication. This requires the Enhanced Key Usage property of the certificate to specify Server Authentication (1.3.6.1.5.5.7.3.1).
  4. The certificate must be created by using the KeySpec option of AT_KEYEXCHANGE
  5. The Subject property of the certificate must indicate that the common name (CN) is the same as the host name or fully qualified domain name (FQDN) of the server computer. If SQL Server is running on a failover cluster, the common name must match the host name or FQDN of the virtual server and the certificates must be provisioned on all nodes in the failover cluster.

Now, these are the mandatory requirements. But how do you verify these properties are satisfied before trying to load this certificate in SQL Server. Here is where the tool CERTUTIL.exe comes in very handy. (Certutil.exe comes with Windows)

I have a certificate named SUDA24322118 which I am going to check to see if the above 5 requirements are satisfied. From an elevated command prompt, run the following: certutil -v -store "my" "SUDA24322118" >certprop.txt

Open certprop.txt in Notepad and check the following values:

Validity of the certificate (Requirement #2)

image

Certificate is created for Server Authentication (Requirement #3)

image

Key Specification Property (Requirement #4)

image

Subject Name (Requirement #5)

image

The last one is the tricky one. In my case, my machine name is SUDA24322118 and hence the subject name must be the same. Once all of the above are verified, the certificate is good to be used with SQL Server.


Wild-Card Certificates

SQL Server 2008 R2 and onwards support wildcard certificates. E.g., *.ABCXYZ.com could be the CN of a certificate. This means that the SQL Server Engine (Server side components) will allow you to load a wildcard certificate for SSL. There are some caveats you need to know regarding client-side issues, so please read the accepted wildcard examples HERE.

 

Unable to see the certificate in the drop-down list box in SQL Server Configuration Manager

If you have religiously followed all the steps till now but still unable to see/pick the certificate to load in SQL Server, then you can follow the workaround given below to use the certificate. To reproduce this issue I created 2 certificates on the same lab machine SUDA24322118 which is in a WORKGROUP.

1. Create a test certificate: I used MAKECERT.exe which is available with Windows SDK. I have Win7 SDK installed and makecert.exe is available in "C:\Program Files\Microsoft SDKs\Windows\v7.1\Bin\".

makecert -r -pe -n "CN= SUDA24322118" -b 01/01/2000 -e 01/01/2036 -eku 1.3.6.1.5.5.7.3.1 -ss my -sr localMachine -sky exchange -sp "Microsoft RSA SChannel Cryptographic Provider" -sy 12

makecert -r -pe -n "CN= SUDA24322118.bplogix.com" -b 01/01/2000 -e 01/01/2036 -eku 1.3.6.1.5.5.7.3.1 -ss my -sr localMachine -sky exchange -sp "Microsoft RSA SChannel Cryptographic Provider" -sy 12

The subject name in the 1st certificate has just the hostname of the computer whereas the second one has the FQDN. Both of them are stored in the local user store called ”my”.

2. If you open you SSCM, you see that only the 1st certificate is listed and the second one is missing.

image

Surprised? Don’t be. Both of them have the same properties and satisfied all the 5 certificate requirements. You can see the certificate if you open up mmc and add the snap-in for Certificates. When asked to chose the account, select “Computer Account” for the Local computer. Both of the above certificates will be present under Personal –> Certificates.

image

How do I get SQL Server to use the certificate with FQDN? Fortunately, there is a simple workaround for this.

3. Locate the Certificate Hash Value: We make use of certutil.exe again to get the cert hash value

certutil -store "my" "SUDA24322118"

certutil -store "my" "SUDA24322118.bplogix.com"

image

You can pipe the results of the above command to a text file and copy the hash value highlighted above. Remove all the blank spaces, trailing and leading spaces. For the sample above, the final hash value would be: 278e30a6ba1748bbabd360b9b8ad1d78e9104d87

4. Add the hash to the SQL Server instance Registry: Open up the registry (regedit) and navigate to the SQL Server instance hive. For e.g. a default instance of SQL Server 2008 R2 would be HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQLServer\SuperSocketNetLib\

In the above location there would be a entry called “Certificate” which would be blank by default. Paste the hash value noted in step #3 here. This associates the certificate with this hash in the local store with this instance of SQL Server. Next time, when SQL Server starts it will load this certificate.

image

5. Enable Force Encryption to “Yes” using SQL Server Configuration Manager.

image

Note: DO NOT navigate to the Certificate tab and try to make any changes there. Just ignore the certificate tab altogether since we have directly modified the registry (which is what the tool does anyway).

6. Restart the SQL Server service. Open the latest ERRORLOG and you can see that the certificate with FQDN has been loaded successfully.

2011-10-11 22:34:10.80 Server The certificate [Cert Hash(sha1) "278E30A6BA1748BBABD360B9B8AD1D78E9104D87"] was successfully loaded for encryption.

 

7. Make a test connection from Management Studio (SSMS) and verify using the value of column encrypt_option in the DMV sys.dm_exec_connections is TRUE, meaning the connection is being encrypted. For the real techies out there you can use Netmon or Wireshark to check if the network packet is actually being encrypted.

 

That’s all folks. There is more to client-side encryption using SSL which you can read about in the above mentioned MSDN articles. Stay secure & safe. I’ll be back with more SQL Server stuff…

Posted in Security | Tagged: , , , , , , , , , , | 15 Comments »

MSDN Webcast on Optimizing and Tuning SQL Server full-text Search (FTS)

Posted by Sudarshan Narasimhan on October 4, 2011


 

In May 2011, I had delivered a live webcast on the topic “Optimizing and Tuning Full Text Search for SQL Server”. You can download the slide deck (PPT) and Video from MSDN Event library. I am sharing it here since the webcast was before the birth of theSQLDude. Refer to the links below for downloading the presentation deck and webcast video.

Note: This talk covers using Full-text search only on SQL Server 2008 and above. Nerd smile

Watch the Webcast

SQL Server Full-Text Search: Tips for Optimizing and Tuning Search for large environments from sudarshan on Vimeo.

 

Presentation Slide Deck (PPT)

You can download the slide deck along with the sample/demo script from [HERE]. It is also available on [THIS POST] @ SQLServerFAQ.

Posted in Webcast | Tagged: , , , , , , , , , , | Leave a Comment »

Getting legacy DTS Designer working with Management Studio for Pablo Picasso’s

Posted by Sudarshan Narasimhan on September 16, 2011


There are many of you SQL Developers and wizards out there still working on legacy DTS packages. Of course, we cannot live without SSMS (SQL Management Studio) and thus try to integrate the legacy SQL 2000 DTS Designer into it. But, life is not that easy and the entire procedure to get DTS Designer/Runtime working on SQL 2005/2008/R2 Management studio is quite cumbersome.

While I was working on setting this up myself, learnt quite a few tricks which I what this blog is going to talk about. At the end of this you should be able to both import, design and execute legacy SQL 2000 DTS packages (not SSIS) on a machine that has SQL 2005+ Management Studio installed. Read on and let free your picassonian designer skills with DTS packages.

Getting Started
First off, here is the link to the official documentation that talks about the support for SQL 2000 DTS packages on SQL 2008 R2. There are 2 types

1. Run-time support  (meaning I can execute DTS packages using dtsrun.exe)
2. Design-time support (meaning I can import, open & design DTS packages in SSMS)

How to: Install Support for Data Transformation Services Packages
http://msdn.microsoft.com/en-us/library/ms143755.aspx

Read the above article to get an understanding of what is possible and what is not (Don’t start/install anything yet) as this process can be quite tricky. My friends @ SQLServerFAQ have a nice post on this which is also something I would recommend reading here.

Ready, Set, GO!

 

Step-by-Step Instructions

1. You would need to install a few additional components, so please download the following packages and keep them ready.

a. Microsoft SQL Server 2000 DTS Designer Components
http://www.microsoft.com/download/en/details.aspx?id=11988

Download the package SQLServer2005_DTS.msi

b. SQL Server 2005 Backward Compatibility Components

Depending on the version of your SQL Server Management Studio (SSMS) you will have to download the corresponding package

SQL 2005 – http://www.microsoft.com/download/en/details.aspx?id=15748

SQL 2008/R2 – http://www.microsoft.com/download/en/details.aspx?id=8824

Download the package SQLServer2005_BC.msi. If you have a 64-bit machine, then you would need the x64 package because the x86 package will not let you install and you will get this error.

image

c. Make sure when you had installed Management Studio that you have chosen “Management Tools – Complete” during the setup for SQL 2008.

 

2. Install both the above packages in the same order as listed above. Once this is installed you will see that the following paths containing some DLL’s

C:\Program Files (x86)\Microsoft SQL Server\80\Tools\Binn \
C:\Program Files\Microsoft SQL Server\80\Tools\Binn\

3. Now, you need to edit the system PATH to ensure that the path for “C:\Program Files (x86)\Microsoft SQL Server\80\Tools\Binn\” comes before the ..\90\Tools or ..\100\Tools folders. You can do this by going to My Computer –> Properties –> Advanced –> Environment Variables and editing the value for "PATH” as shown below.

image

Here is a sample path value from my working machine.

PATH=C:\Windows\system32;C:\Windows;C:\Windows\System32\Wbem;C:\Program Files (x86)\Microsoft SQL Server\80\Tools\Binn\;C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\;C:\Program Files\Microsoft SQL Server\100\Tools\Binn\;C:\Program Files\Microsoft SQL Server\100\DTS\Binn\;C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\;C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\;C:\Windows\System32\WindowsPowerShell\v1.0\

4. Okay, now comes the manual part. This step is the same as given in the MSDN article.

 

32-bit Machine (x86)

a. Copy the files, SEMSFC.DLL, SQLGUI.DLL, and SQLSVC.DLL, from the %ProgramFiles%\Microsoft SQL Server\80\Tools\Binn folder to the %ProgramFiles%\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\ folder

b. Copy the files, SEMSFC.RLL, SQLGUI.RLL, and SQLSVC.RLL, from the %ProgramFiles%\Microsoft SQL Server\80\Tools\Binn\Resources\ folder to the %ProgramFiles%\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Resources\1033\ folder (for English locale). For example, for U.S. English, the lang_id subfolder will be "1033".

64-bit Machine (x64)

a. Copy the files, SEMSFC.DLL, SQLGUI.DLL, and SQLSVC.DLL, from the %ProgramFiles(x86)%\Microsoft SQL Server\80\Tools\Binn folder to the %ProgramFiles%\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\ folder

b. Copy the files, SEMSFC.RLL, SQLGUI.RLL, and SQLSVC.RLL, from the %ProgramFiles(x86)%\Microsoft SQL Server\80\Tools\Binn\Resources\ folder to the %ProgramFiles(x86)%\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Resources\1033\ folder (for English locale). For example, for U.S. English, the lang_id subfolder will be "1033".

On a system with default settings,

%ProgramFiles(x86)% –> C:\Program Files (x86)\
%ProgramFiles% –> C:\Program Files\

 

5. After this is done I would suggest a reboot of the machine to ensure that if any pending file renames are present, they will be completed on a system restart. This is not mandatory, but just a suggestion. If you cannot reboot, then you need to close all instances of SSMS and re-open to test opening a DTS package.

TESTING

From SQL Server Management Studio, navigate to the Legacy folder in Object Explorer as shown below

image

If all goes well, then when you open a SQL 2000 DTS package (test.dts), then you will see the legacy DTS designer open up as shown below.

image

Still Not Working
If things go wrong, then you will get this error when you tried to open a DTS package,

image

Error Text

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)

If you have faithfully followed the above procedure step-by-step then you will need to perform this additional step as well on those systems where you receive the above error. This is why I have not added this step as default since it does not occur on all systems.

1. Navigate to C:\Program Files (x86)\Microsoft SQL Server\80\Tools\Binn and see if there is a file called stardds.dll present there.

2. Note down the file version, date and size.

3. If your Operating System is Windows Server 2008/R2 or Windows Vista/7, then you will need to replace the file present with the one given below. This is because Windows 2008+ (including Windows 2008 R2) requires the 2005.90.4035.0 version of stardds.DLL. Windows 2003 or earlier requires the 2000.80.2151.0/2282.0 versions of stardds.dll

a. Rename the existing stardds.dll to stardds.dll.original

b. Download the version of stardds.dll that applies to your OS

For Windows 2008/2008 R2/Vista/Win7 –> Download stardds.dll.4035  from HERE
For Windows 2003/2003 R2 –> Download stardds.dll.2282  from HERE

c. Copy the downloaded DLL to the location C:\Program Files (x86)\Microsoft SQL Server\80\Tools\Binn\ (for 64-bit systems) OR C:\Program Files\Microsoft SQL Server\80\Tools\Binn\ (for 32 bit systems) and name it as stardds.dll

4. Close SQL Management Studio and re-open it and repeat the process of opening the DTS package and it should work now.

Caveat for DTS Packages contains Analysis Service (AS) Tasks
Apart from this, if any of the DTS packages have Analysis Services Cubes then you will still not be able to open these packages in SSMS. You will be faced with this error message

Error Source : Microsoft Data Transformation Services (DTS) Package
Error Description : Invalid class string

The backward compatibility package do not include design-time support for modifying DTS packages that contain the SQL Server 2000 Analysis Services Processing task and also do not include support for the SQL Server 2000 Data Mining Prediction Query task. So, if your DTS package has either "AS Processing Task" or "Data Mining Prediction Query task", then we cannot open/design these packages in SSMS 2008/R2

If you want to be able to do so, then you will have to install the following SQL Server 2000 components from SQL Server 2000 media

1. Analysis Manager
2. Decision Support Objects
3. Client Components

You don’t the AS Server component, just the above will suffice. Once you install these components you then did the get the following DLL’s present on the machine,

msmdtsp.dll   –> for Analysis Services Processing Task
msmdtsm.dll  –> for Data Mining Prediction Task

Close and re-open SSMS and you should be able to open the package now.

 

Phew! This was quite a long post indeed but I think I have covered most issues one could run into. If you come across any other issues, leave a comment behind and I will try and see if that can be resolved.

As always stay tuned to the TheSQLDude for more….

Posted in Tools | Tagged: , , , , , , , | 42 Comments »

Did you know–SQL 2000 isn’t supported on Windows 2008+ ?

Posted by Sudarshan Narasimhan on September 1, 2011


I’m not sure how many of you are aware of this but I happened to talk to quite a few DBA’s who were hell-bent on installing SQL Server 2000 on Windows 2008 (don’t ask me why). But for those of you who are planning to do this, DON’T, because it isn’t supported.

SQL Server 2000 and below are not supported on Windows Vista/Windows Server 2008 and above (which includes Windows 7/Windows Server 2008 R2). Here is the official blog post by the SQL Releases team on this,

http://blogs.msdn.com/b/sqlreleaseservices/archive/2009/10/23/sql-server-on-windows-7-and-windows-server-2008-r2.aspx

Stay out of trouble and upgrade Smile.

Posted in General Thoughts | Tagged: , , , , , | 1 Comment »

Trust No One and always ask “WHY”!

Posted by Sudarshan Narasimhan on August 13, 2011


Another good post by Paul Randal. It’s worth a read and I agree with his thoughts on Forums filled with “so-called” experts. The WWW can be a dangerous place for those looking for quick solutions. Don’t be misled into implementing something you will regret later or end up being someone else’s worst nightmare. Always ask yourself “WHY” instead of just focusing on the “WHAT TO DO” when looking for suggestions/solutions. This is a crucial part of logical troubleshooting which most people ignore. So do yourself a favour and ask “WHY” when someone asks you do to make this/that change. If they can’t explain “why”, well rest assured it is not 100% guaranteed to solve your issue. Even if it did, you still don’t know “why” it did.

Like the warrior in 300 says “Remember us, Remember WHY we died


Do yourself a favor… Trust No One
http://www.sqlskills.com/BLOGS/PAUL/post/Do-yourself-a-favor-Trust-No-One.aspx

Posted in General Thoughts | Tagged: , , , , | Leave a Comment »