Author Archives: Amit Banerjee

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.

The need for a sound Backup Strategy

Posted on by

Backup strategy needs to be designed in such a manner that it causes minimum amount of downtime in case during a disaster recovery scenario if backups need to be restored. The backup strategy depends on the following:
1. Size of the database – This would determine how often a FULL BACKUP can be taken
2. Recovery Model – This would determine if transaction log/differential backups are possible
3. The importance of the database – This would determined how often transaction log backups need to be taken if the database is in full recovery model.

If the database is in “simple” recovery model, then the only option that we would have is to take full backups. If the database is in FULL/BULK-LOGGED recovery models, then we have the option of taking transaction log/differential backups.

Now the next question is how often to take a transaction log backup. Depending of the importance of the database, transaction log backups can be taken every 10-15 minutes or even every hour. This needs to be determined at your end. Furthermore, if the transaction log backups are being taken very frequently and a full backup is taken once a week, then it is advisable to take differential backups during the middle of the week. This would ensure that the process of restore becomes less cumbersome as after the full backup, then differential backup can be applied followed by the transactional log backups.

In case of full/bulk-logged recovery models, the following backup strategy would be ideal:

1. Full backup

a. Differential backup(s)

i. Transaction log backup(s)

b. Differential backup(s)

i. Transaction log backup(s)

2. Full backup (and repeat the above cycle)

However, any backup strategy that you design needs to be compliant with your business needs.

Furthermore, for creating sound fail-over strategies for disaster recovery scenarios, the following can be considered:
1. Log shipping

2. Database Mirroring (For SQL 2005 SP1 and above)

3. Replication (Snapshot, Transactional, Merge)

4. Failover Clustering

5. Or an in-house disaster recovery mechanism envisioned by your IT & DBA team which could be a combination of one or more of the above methods.
Also, it is considered a good practice to restore a backup on a test server and a CHECKDB run on the restored database to make sure that the backup is in good shape. This becomes of utmost importance when it is not possible to run a CHECKDB before taking a backup. In SQL Server 2005, you have the option to perform a piece meal restore which would allow you to restore up to the page level in case you have database corruption.

The following articles could be helpful:

Overview of Restore and Recovery in SQL Server
http://msdn2.microsoft.com/en-us/library/ms191253.aspx

SQL Server 2005 provides the option of performing an online restore which is available for SQL Server 2005 Enterprise Edition. Furthermore, you have option of performing Piece Meal Restores i.e. page level restores, single file restore for a secondary data file in a filegroup while keeping the database online etc.

Please refer the following for more information:
Performing Online Restores
http://msdn2.microsoft.com/en-us/library/ms188671.aspx
Storage Top 10 Best Practices
http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/storage-top-10.mspx
Using Recovery Models
http://msdn2.microsoft.com/en-us/library/aa173678(SQL.80).aspx

How to update statistics for a VLDB

Posted on by

Statistics are something that the SQL Optimizer depends on to generate a plan. The more outdated your statistics are, greater are the chances that your query optimizer will land up with a sub-optimal plan.
So what do you do when you have a database which is highly transactional in nature and is quite large in size. So, you might land up in a scenario where the database is online 24X7 and there is no downtime window where you can update your statistics with a FULLSCAN. 

Before we get into what my opinion is about updating statistics of a database is, lets make sure we understand what I mean by statistics being “up-to-date“. Statistics being up-to-date is the sampling rate used while updating statistics was 100% or they were updated with a FULLSCAN. Statistics updated using AUTO UPDATE STATISTICS property of a database will not update statistics with a FULLSCAN contrary to popular belief. The sole reason for this being is that suppose a table has 1 GB of data and a query on the database initiated a AUTO UPDATE of a STAT on this database, then if the sampling was 100%, then your query duration would take an eternity. 

So, if you have a large database:
1. Ensure that the AUTO UPDATE STATS is turned on for the database. This would provide for some relief
2. Identify which tables need to have their statistics updated with a fullscan. Only those would be required which have the maximum number of queries running on them
3. Next identify a period when the database usage is low. This is the time when you can use to update the stats of the other tables which did not qualify the list in #2.
4. Use a script to update statistics of the tables identified step # 2 probably based on rowmodctr values (ROWMODCTR: Counts the total number of inserted, deleted, or updated rows since the last time statistics were updated for the table. In SQL Server 2005, this is not going to be always helpful [Ref: http://msdn2.microsoft.com/en-us/library/ms190283.aspx]. But in SQL 2000, this could be used as a deciding factor) 

Sample Script
============
Let’s say I had identified 4 tables in STEP 2:
CREATE TABLE UPD_STATS_TBL (tblname varchar(10), dt_updated datetime, rowmodctr bigint) 

SELECT * FROM UPD_STATS_TBL 

tblname dt_updated rowmodctr 

TBL1 2007-10-24 21:13:46.123 20000
TBL2 2007-10-23 21:13:46.123 400000
TBL3 2007-10-22 21:13:46.123 508000
TBL4 2007-10-24 20:13:46.123 87000  

**************************************************  

CREATE PROC UPD_STATS_DB 

AS 

DECLARE @tbl varchar(10) 

UPDATE upd_stats_tbl SET rowmodctr = (SELECT MAX(rowmodctr) from sys.sysindexes where id=OBJECT_ID(tblname)) 

SELECT TOP 1 @tbl = tblname 

FROM UPD_STATS_TBL 

WHERE rowmodctr > 10000 

ORDER BY dt_updated 

-- For SQL Server 2005, you would have to used the STATS DATE function instead of rowmodctr values. Refer my statistics update post for more details. 

DECLARE @stmt varchar (100) 

SET @stmt = 'UPDATE STATISTICS'+SPACE(1)+@tbl+SPACE(1)+'WITH FULLSCAN' 

EXEC (@stmt) 

UPDATE upd_stats_tbl SET dt_updated = (SELECT GETDATE()) 

WHERE tblname = @tbl

**************************************************
NOTE: You could refine this down to the table statistic if you wanted to.