Author Archives: Amit Banerjee

Missing Indexes Feature in SQL Server 2005

Posted on by

Indexes are essential in making sure that your queries have efficient query plans and for SELECTS, you don’t end up doing searches on HEAPS.

One of the biggest improvements in SQL Server 2005 is that it tracks all the transactions happening on the server and makes a list of indexes which could prove beneficial for those queries. Of course, one index might be beneficial for one query but detrimental to another. So, it is highly essential that we test the feasibility of implementing these indexes on a production environment before rolling out changes to our indexes.

The missing indexes feature is on by default. No controls are provided to turn the feature on or off, or to reset any of the tables returned when the dynamic management objects are queried. When SQL Server is restarted, all of the missing index information is dropped. This feature can only be disabled if an instance of SQL Server is started by using the -x argument (one of the startup parameters) with the sqlservr command-prompt utility. However, along with the missing indexes, a lot of other useful events for performance troubleshooting will not be captured which is why this startup option is not recommended.

So, the first thing I would do if a query or a set of queries are running slowly in SQL Server, I would query the DMVs related to these missing indexes and find out if there are an indexes related to the tables on which those slow running queries are executing. This feature becomes highly useful when the following conditions are true:
1. NO CPU bottleneck
2. NO Blocking on the server
3. NO Disk bottleneck

You can view a list of all the missing indexes using Performance Dashboard which can be used with SQL Server 2005 Service Pack 2 and above.

You can get the CREATE INDEX commands for the missing indexes recommended on your instance using the query mentioned here.

If you want to test an automatic determination of the index candidates for your workload, then have a look at the post below:
http://blogs.msdn.com/b/queryoptteam/archive/2006/06/01/613516.aspx

Related Links for Missing Indexes
About Missing Indexes
http://msdn2.microsoft.com/en-us/library/ms345524.aspx
Finding Missing Indexes
http://msdn2.microsoft.com/en-us/library/ms345417.aspx
Limitations of this feature
http://msdn2.microsoft.com/en-us/library/ms345485.aspx

WMI Script for Changing SQL Server 2000 and 2005 passwords

Posted on by

Our domain security policy requires us to change our Domain Account passwords every once in a while. This security feature is a good practice but the change of password requires me to change my SQL Server component startup accounts to be changed also as most of them run under my Domain Account.

Tired of accomplishing this task which requires me to type out my user name and password (twice for the reconfirmation 🙂 )  brought forward the need for automating this task. Once I did this, I thought it would be prudent for me to share the same with the SQL community. WMI is quite powerful and a number of tasks can be achieved through it which you shall see in my future blog posts. I have created a category in my blog completely for WMI.

Addition: October 24th, 2011

If you are using SQL Server 2008 R2, then you need to have this fix applied so that SetServiceAccount can work:

2397020 FIX: “Access is denied. [0x80070005]” error message occurs in SSCM when you try to change the password of an account of SQL Server 2008 R2 in Windows Vista or in a later version of Windows
http://support.microsoft.com/default.aspx?scid=kb;EN-US;2397020

If you use SetServiceAccount to change the password, then this will result in a SQL Server service restart. The script provided on the Technet Gallery is for changing both the service account and the password. If only the password needs to be changed for the service account, then use SetServiceAccountPassword. This will change the password and will not automatically restart your SQL Server service.

Download the file: pwdchange file and change the name to pwdchange.vbs. This can be executed from command prompt using the following command (provided you have Windows Script Host which almost all Windows Servers have):

cscript pwdchange.vbs

The script has comments written to help you understand the same. This script makes use of the following namespaces:

\root\Microsoft\SqlServer\ComputerManagement (SQL Server 2005)

\root\CIMV2 (SQL Server 2000)

The reason I do not use SQL Server 2000 WMI namespace as it is not installed by default with a SQL Server 2000 default installation. It has to be installed separately. So, I decided to use CIMV2 which has Win32_Service for achieving the same purpose. In the script you would have to provide the following details:

strAccount1 = “Account1” ‘ For alias@domain.com
strAccount2 = “Account2” ‘ For domain\alias naming convention
strAccount3 = “Account1” ‘ To cover any other naming convention
strAlias = “Alias” ‘ Provide the alias
strDomain = “DomainName” ‘ Provide the domain name
strPassword = “Set Password Here” ‘ Put the new password here

SQL Server is not configured for Remote Connections

Posted on by

This is one of the most common error messages when users use default settings to connect to a SQL Server Express Instance.

ERROR
Sqlcmd: Error: Microsoft SQL Native Client: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.


Please refer the following article to make sure that your SQL Express Instance is configured to accept Remote Connections:
http://support.microsoft.com/kb/914277

Also, make sure from the SQL Server Configuration Manager that TCP/IP and Named Pipes are enabled and you have VIA protocol disabled.

Another thing you might want to check is if SQL Server Browser Service is up and running.

Federated Databases

Posted on by

This is something that not many people are aware of. Hence, I thought about putting up a brief overview of this side of SQL Server.

To achieve the high levels of performance required by the largest Web sites, a multitier system typically balances the processing load for each tier across multiple servers. SQL Server 2005 shares the database processing load across a group of servers by horizontally partitioning the data in a SQL Server database. These servers are managed independently, but cooperate to process the database requests from the applications; such a cooperative group of servers is called a federation.

A federated database tier can achieve very high levels of performance only if the application sends each SQL statement to the member server that has most of the data required by the statement. This is referred to as collocating the SQL statement with the data required by the statement. Collocating SQL statements with the required data is not a requirement unique to federated servers. It is also required in clustered systems.

Federated Server Tier

There is one instance of SQL Server on each member server.

Each member server has a member database. The data is spread through the member databases.

The tables from the original database are horizontally partitioned into member tables. There is one member table per member database, and distributed partitioned views are used to make it appear as if there was a full copy of the original table on each member server.

The application layer must be able to collocate SQL statements on the member server that contains most of the data referenced by the statement.

Backing Up and Restoring Federated Database Servers

In a federated-database-server tier that is built by using distributed partitioned views, the member servers form one logical unit. Therefore, you must coordinate the recovery of the member databases to make sure that they remain synchronized correctly.

SQL Server 2005 does not require that you coordinate backups across member servers. Backups can be independently taken from each database, without regard for the state of the other member databases. Because the backups do not have to be synchronized, there is no processing overhead for synchronization and no blockage of running tasks.

The most important aspect of recovering a set of member databases is the same as recovering any other database: Plan and test the recovery procedures before you put the databases into production. You must set up processes to restore all the databases to the same logical point in time. SQL Server includes features to support the recovery of all member databases to the same point in time.

Pros

1. Federated servers if implemented correctly are a great way to load balance a database server environment and is very similar to a database farm implementation.

2. This would greatly allow you to distribute the load on your servers based on any of the following criteria:

a. Geographic location

b. Traffic in terms of users

c. Traffic in terms of transactions

d. Database table size

3. Also, federated servers give you the option on partitioning data across servers with the help of distributed partitioned views

4. This gives you the option of horizontally partitioning the data across various servers which ultimately leads to greater throughput

5. It lets you control the traffic coming in and also helps in maintaining the load thresholds across the entire setup

6. Furthermore, if we have a middle tier in the entire setup, then design changes in the federated server environment will not affect the client side applications in any manner as they would be connecting to the middle tier and the middle tier will connect with the database server

Cons

1. One of the major drawbacks is disaster recovery. If one of the member server fails, there needs to be a failback plan in place which could cause minimum hindrance to the normal operations while implementation.

2. Also, if distributed partitioning is being implemented, then rules and constraints need to be strong enough to prevent any sort on inconsistencies from arising due to data modification

3. Also, if the performance of one of the member servers takes a hit, most of the distributed partitioned views would also take a hit in terms of operations done on them

4. The middle tier should be designed in such a robust manner that there is no ambiguity in resolving which server in the environment needs to targeted based on the query coming into the server

5. The backup/restore scenarios need to be designed and planned in such a way that all the member servers are all synchronized at all times

Useful Articles

The following whitepaper and TechNet articles should help a great deal here:

Planning, Implementing, and Administering Scaleout Solutions with SQL Server 2005 Solution

Scaling Out SQL Server with Data Dependent Routing

Designing Data Tier Components and Passing Data Through Tiers

SQL Server 2005 Upgrade

Posted on by

Upgrading from SQL Server 2000 to SQL Server 2005 is always a daunting task if you have had a chance to contemplate as to what needs to be done before and after the upgrade process. On the other hand, if you had a chance to plan out everything before hand, then it is very unlikely that you would land yourself in a soup unless and until the Gods choose to be very unkind towards you on that fateful day. 🙂

So, first let us understand what are the two procedures of upgrading from SQL Server 2000 to SQL Server 2005.
1. IN PLACE UPGRADE
2. SIDE BY SIDE UPGRADE

So, before I go ahead and explain these two upgrade procedures, let us dwell on the pre-requisites for upgrading to SQL Server 2005.
1. Testing: Make sure all your applications are thoroughly tested and function as expected on a SQL Server 2005 environment before deciding to take the final step
2. Fall-back plan: This is what I call "insurance". It’s a hassle but when a problem does happen, this is what takes care in helping you out of the problem. I believe it is better to be safe than sorry.
3. Backups: This should have been included in Point 2 but this is something of utmost importance, so I thought I would re-iterate this again. We need to have backups of all the user and system (YES! we need them.) databases in case we need to use our fall-back plan.
4. Upgrade Advisor: Run the upgrade advisor on your existing SQL Server 2000 installation and find out if there are any REDFLAGS in upgrading to SQL Server 2005 based on your current configuration.

In-place upgrade process
The upgrade process which refers to upgrading all the existing installed components of SQL Server 2000 to SQL Server 2005 on the same server.
Side-by-side upgrade process
The upgrade process which refers to leaving your existing SQL Server installation intact and installing a fresh SQL Server 2005 instance on the same server or a different server and then migrating over all your databases. Once, all the components (Databases, Plans, DTS Packages etc.) have been migrated the SQL 2000 installation can then be phased out.
Both these upgrade processes have their PROs and CONs which are clearly documented in the TechNet Article mentioned below.

Upgrade Handbook for SQL Server 2005
http://www.microsoft.com/technet/prodtechnol/sql/2005/sqlupgrd.mspx
Upgrading to SQL Server 2005
http://www.microsoft.com/sql/solutions/upgrade/default.mspx
Technet Article on Upgrading SQL Server 2000 to SQL Server 2005
http://www.microsoft.com/downloads/details.aspx?FamilyID=3d5e96d9-0074-46c4-bd4f-c3eb2abf4b66&DisplayLang=en

So, what do we need to take care of when upgrading to SQL Server 2005 from SQL Server 2000?
1. Replication: If you do have replication configured (subscriber/publisher/distributor), you need to find out if your replication topology is compatible and will continue to function smoothly if the current server is upgraded?
2. DTS Packages: These need to migrated to SQL Server 2005 using the Migration Wizard. Related Article: http://msdn2.microsoft.com/en-us/library/ms143501.aspx
3. Maintenance Plans: Again they are different from their SQL Server 2000 counterparts because in SQL 2005, we use SSIS in the background.
4. Logins/Linked servers: In case of an side-by-side upgrade, then these need to be migrated too.

These are a few of prime areas of concern that come to my mind. A whole bunch of other considerations are mentioned in the above mentioned Technet Whitepaper. Please do read through that if you are planning to upgrade to SQL Server 2005. One of the biggest reasons that we get support calls regarding failed upgrades is that some best practice was not followed due to which the entire setup failed.

Remember that the support team for SQL has a skilled set of engineers who are trained on troubleshooting problem scenarios with SQL Server but they do not hold magic wands. If your SQL Server setup is so botched that it cannot be recovered, then you will have to end up reinstalling the instance from scratch after performing a manual cleanup.

Please think for a moment: Which is the better option?

a. Not reading the lengthy whitepaper

b. OR taking a chance (50-50) that the upgrade will happen smoothly but if it fails, then we have a whole set of headaches to take care of.