Omar AL Zabir blog on ASP.NET Ajax and .NET 3.5 : sql server

Optimize ASP.NET Membership Stored Procedures for greater speed and scalability

omar — Fri, 13 Mar 2009 13:23:00 GMT

Last year at Pageflakes, when we were getting millions of hits per day, we were having query timeout due to lock timeout and Transaction Deadlock errors. These locks were produced from aspnet_Users and aspnet_Membership tables. Since both of these tables are very high read (almost every request causes a read on these tables) and high write (every anonymous visit creates a row on aspnet_Users), there were just way too many locks created on these tables per second. SQL Counters showed thousands of locks per second being created. Moreover, we had queries that would select thousands of rows from these tables frequently and thus produced more locks for longer period, forcing other queries to timeout and thus throw errors on the website.

If you have read my last blog post, you know why such locks happen. Basically every table when it grows up to hold millions of records and becomes popular goes through this trouble. It’s just a part of scalability problem that is common to database. But we rarely take prevention about it in our early design.

The solution is simple, you should either have WITH (NOLOCK) or SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED before SELECT queries. Either of this will do. They tell SQL Server not to hold any lock on the table while it is reading the table. If some row is locked while the read is happening, it will just ignore that row. When you are reading a table thousand times per second, without these options, you are issuing lock on many places around the table thousand times per second. It not only makes read from table slower, but also so many lock prevents insert, update, delete from happening timely and thus queries timeout. If you have queries like “show the currently online users from last one hour based on LastActivityDate field”, that is going to issue such a wide lock that even other harmless select queries will timeout. And did I tell you that there’s no index on LastActivityDate on aspnet_Users table?

Now don’t blame yourself for not putting either of these options on your every stored proc and every dynamically generated SQL from the very first day. ASP.NET developers made the same mistake. You won’t see either of these used in any of the stored procs used by ASP.NET Membership. For example, the following stored proc gets called whenever you access Profile object:

ALTER PROCEDURE [dbo].[aspnet_Profile_GetProperties]
    @ApplicationName      nvarchar(256),
    @UserName             nvarchar(256),
    @CurrentTimeUtc       datetime
AS
BEGIN

    DECLARE @ApplicationId uniqueidentifier
    SELECT  @ApplicationId = NULL
    SELECT  @ApplicationId = ApplicationId FROM 
      dbo.aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
    IF (@ApplicationId IS NULL)
        RETURN

    DECLARE @UserId uniqueidentifier
    DECLARE @LastActivityDate datetime
    SELECT  @UserId = NULL

    SELECT @UserId = UserId, @LastActivityDate = LastActivityDate
    FROM   dbo.aspnet_Users 
    WHERE  ApplicationId = @ApplicationId AND LoweredUserName = LOWER(@UserName)

    IF (@UserId IS NULL)
        RETURN
    SELECT TOP 1 PropertyNames, PropertyValuesString, PropertyValuesBinary
    FROM         dbo.aspnet_Profile
    WHERE        UserId = @UserId

    IF (@@ROWCOUNT > 0)
    BEGIN
        UPDATE dbo.aspnet_Users
        SET    LastActivityDate=@CurrentTimeUtc
        WHERE  UserId = @UserId
    END
END

There are two SELECT operations that hold lock on two very high read tables – aspnet_Users and aspnet_Profile. Moreover, there’s a nasty UPDATE statement. It tries to update the LastActivityDate of a user whenever you access Profile object for the first time within a http request.

This stored proc alone is enough to bring your site down. It did to us because we are using Profile Provider everywhere. This stored proc was called around 300 times/sec. We were having nightmarish slow performance on the website and many lock timeouts and transaction deadlocks. So, we added the transaction isolation level and we also modified the UPDATE statement to only perform an update when the LastActivityDate is over an hour. So, this means, the same user’s LastActivityDate won’t be updated if the user hits the site within the same hour.

So, after the modifications, the stored proc looked like this:

ALTER PROCEDURE [dbo].[aspnet_Profile_GetProperties]
    @ApplicationName      nvarchar(256),
    @UserName             nvarchar(256),
    @CurrentTimeUtc       datetime
AS
BEGIN
    -- 1. Please no more locks during reads
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

    DECLARE @ApplicationId uniqueidentifier
    --SELECT  @ApplicationId = NULL
    --SELECT  @ApplicationId = ApplicationId FROM dbo.aspnet_Applications 
    WHERE LOWER(@ApplicationName) = LoweredApplicationName
    --IF (@ApplicationId IS NULL)
    --    RETURN
    
    -- 2. No more call to Application table. We have only one app dude!
    SET @ApplicationId = dbo.udfGetAppId()

    DECLARE @UserId uniqueidentifier
    DECLARE @LastActivityDate datetime
    SELECT  @UserId = NULL

    SELECT @UserId = UserId, @LastActivityDate = LastActivityDate
    FROM   dbo.aspnet_Users 
    WHERE  ApplicationId = @ApplicationId AND LoweredUserName = LOWER(@UserName)

    IF (@UserId IS NULL)
        RETURN
    SELECT TOP 1 PropertyNames, PropertyValuesString, PropertyValuesBinary
    FROM         dbo.aspnet_Profile
    WHERE        UserId = @UserId

    IF (@@ROWCOUNT > 0)
    BEGIN
        -- 3. Do not update the same user within an hour
        IF DateDiff(n, @LastActivityDate, @CurrentTimeUtc) > 60
        BEGIN
            -- 4. Use ROWLOCK to lock only a row since we know this query
            -- is highly selective
            UPDATE dbo.aspnet_Users WITH(ROWLOCK)
            SET    LastActivityDate=@CurrentTimeUtc
            WHERE  UserId = @UserId
        END
    END
END

The changes I made are numbered and commented. No need for further explanation. The only tricky thing here is, I have eliminate call to Application table just to get the ApplicationID from ApplicationName. Since there’s only one application in a database (ever heard of multiple applications storing their user separately on the same database and the same table?), we don’t need to look up the ApplicationID on every call to every Membership stored proc. We can just get the ID and hard code it in a function.

CREATE FUNCTION dbo.udfGetAppId()
RETURNS uniqueidentifier
WITH EXECUTE AS CALLER
AS
BEGIN
    RETURN CONVERT(uniqueidentifier, 'fd639154-299a-4a9d-b273-69dc28eb6388')
END;

This UDF returns the ApplicationID that I have hardcoded copying from the Application table. Thus it eliminates the need for quering on the Application table.

Similarly you should do the changes in all other stored procedures that belong to Membership Provider. All the stroc procs are missing proper locking, issues aggressive lock during update and too frequent updates than practical need. Most of them also try to resolve ApplicationID from ApplicationName, which is unnecessary when you have only one web application per database. Make these changes and enjoy lock contention free super performance from Membership Provider!

Linq to SQL solve Transaction deadlock and Query timeout problem using uncommitted reads

omar — Sat, 07 Mar 2009 07:30:52 GMT

When your database tables start accumulating thousands of rows and many users start working on the same table concurrently, SELECT queries on the tables start producing lock contentions and transaction deadlocks. This is a common problem in any high volume website. As soon as you start getting several concurrent users hitting your website that results in SELECT queries on some large table like aspnet_users table that are also being updated very frequently, you end up having one of these errors:

Transaction (Process ID ##) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Or,

Timeout Expired. The Timeout Period Elapsed Prior To Completion Of The Operation Or The Server Is Not Responding.

The solution to these problems are – use proper index on the table and use transaction isolation level Read Uncommitted or WITH (NOLOCK) in your SELECT queries. So, if you had a query like this:

SELECT * FORM aspnet_users 
where ApplicationID =’xxx’ AND LoweredUserName = 'someuser'

You should end up having any of the above errors under high load. There are two ways to solve this:

SET TRANSACTION LEVEL READ UNCOMMITTED;
SELECT * FROM aspnet_Users 
WHERE ApplicationID =’xxx’ AND LoweredUserName = 'someuser'

Or use the WITH (NOLOCK):

SELECT * FROM aspnet_Users WITH (NOLOCK) 
WHERE ApplicationID =’xxx’ AND LoweredUserName = 'someuser'

The reason for the errors are that since aspnet_users is a high read and high write table, during read, the table is partially locked and during write, it is also locked. So, when the locks overlap on each other from several queries and especially when there’s a query that’s trying to read a large number of rows and thus locking large number of rows, some of the queries either timeout or produce deadlocks.

Linq to Sql does not produce queries with the WITH (NOLOCK) option nor does it use READ UNCOMMITTED. So, if you are using Linq to SQL queries, you are going to end up with any of these problems on production pretty soon when your site becomes highly popular.

For example, here’s a very simple query:

using (var db = new DropthingsDataContext())
{
    var user = db.aspnet_Users.First();
    var pages = user.Pages.ToList();
}

DropthingsDataContext is a DataContext built from Dropthings database.

When you attach SQL Profiler, you get this:

You see none of the queries have READ UNCOMMITTED or WITH (NOLOCK).

The fix is to do this:

using (var db = new DropthingsDataContext2())
{
    db.Connection.Open();
    db.ExecuteCommand("SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;");

    var user = db.aspnet_Users.First();
    var pages = user.Pages.ToList();
}

This will result in the following profiler output

As you see, both queries execute within the same connection and the isolation level is set before the queries execute. So, both queries enjoy the isolation level.

Now there’s a catch, the connection does not close. This seems to be a bug in the DataContext that when it is disposed, it does not dispose the connection it is holding onto.

In order to solve this, I have made a child class of the DropthingsDataContext named DropthingsDataContext2 which overrides the Dispose method and closes the connection.

   class DropthingsDataContext2 : DropthingsDataContext, IDisposable
    {
        public new void Dispose()
        {
            if (base.Connection != null)
                if (base.Connection.State != System.Data.ConnectionState.Closed)
                {
                    base.Connection.Close();
                    base.Connection.Dispose();
                }

            base.Dispose();            
        }
    }

This solved the connection problem.

There you have it, no more transaction deadlock or lock contention from Linq to SQL queries. But remember, this is only to eliminate such problems when your database already has the right indexes. If you do not have the proper index, then you will end up having lock contention and query timeouts anyway.

There’s one more catch, READ UNCOMMITTED will return rows from transactions that have not completed yet. So, you might be reading rows from transactions that will rollback. Since that’s generally an exceptional scenario, you are more or less safe with uncommitted read, but not for financial applications where transaction rollback is a common scenario. In such case, go for committed read or repeatable read.

There’s another way you can achieve the same, which seems to work, that is using .NET Transactions. Here’s the code snippet:

using (var transaction = new TransactionScope(
    TransactionScopeOption.RequiresNew,
    new TransactionOptions()
    {
        IsolationLevel = IsolationLevel.ReadUncommitted,
        Timeout = TimeSpan.FromSeconds(30)
    }))
{
    using (var db = new DropthingsDataContext())
    {
        var user = db.aspnet_Users.First();
        var pages = user.Pages.ToList();

        transaction.Complete();
    }
}

Profiler shows a transaction begins and ends:

The downside is it wraps your calls in a transaction. So, you are unnecessarily creating transactions even for SELECT operations. When you do this hundred times per second on a web application, it’s a significant over head.

Some really good examples of deadlocks are given in this article:

http://www.code-magazine.com/article.aspx?quickid=0309101&page=2

I highly recommend it.

99.99% available ASP.NET and SQL Server SaaS Production Architecture

omar — Wed, 10 Dec 2008 08:39:00 GMT

You have a hot ASP.NET+SQL Server product, growing at thousand users per day and you have hit the limit of your own garage hosting capability. Now that you have enough VC money in your pocket, you are planning to go out and host on some real hosting facility, maybe a colocation or managed hosting. So, you are thinking, how to design a physical architecture that will ensure performance, scalability, security and availability of your product? How can you achieve four-nine (99.99%) availability? How do you securely let your development team connect to production servers? How do you choose the right hardware for web and database server? Should you use Storage Area Network (SAN) or just local disks on RAID? How do you securely connect your office computers to production environment?

Here I will answer all these queries. Let me first show you a diagram that I made for Pageflakes where we ensured we get four-nine availability. Since Pageflakes is a Level 3 SaaS, it’s absolutely important that we build a high performance, highly available product that can be used from anywhere in the world 24/7 and end-user gets quick access to their content with complete personalization and customization of content and can share it with others and to the world. So, you can take this production architecture as a very good candidate for Level 3 SaaS:

Here’s a CodeProject article that explains all the ideas:

99.99% available ASP.NET and SQL Server SaaS Production Architecture

Hope you like it. Appreciate your vote.

My first book - Building a Web 2.0 Portal with ASP.NET 3.5

omar — Sun, 13 Jan 2008 20:23:00 GMT

My first book "Building a Web 2.0 Portal with ASP.NET 3.5" from O'Reilly is published and available in the stores. This book explains in detail the architecture design, development, test, deployment, performance and scalability challenges of my open source web portal Dropthings.com. Dropthings is a prototype of a web portal similar to iGoogle or Pageflakes. But this portal is developed using recently released brand new technologies like ASP.NET 3.5, C# 3.0, Linq to Sql, Linq to XML, and Windows Workflow foundation. It makes heavy use of ASP.NET AJAX 1.0. Throughout my career I have built several state-of-the-art personal, educational, enterprise and mass consumer web portals. This book collects my experience in building all of those portals.

O'Reilly Website:
http://www.oreilly.com/catalog/9780596510503/

Amazon:
http://www.amazon.com/Building-Web-2-0-Portal-ASP-NET/dp/0596510500

Disclaimer: This book does not show you how to build Pageflakes. Dropthings is entirely different in terms of architecture, implementation and the technologies involved.

You learn how to:

Implement a highly decoupled architecture following the popular n-tier, widget-based application model
Provide drag-and-drop functionality, and use ASP.NET 3.5 to build the server-side part of the web layer
Use LINQ to build the data access layer, and Windows Workflow Foundation to build the business layer as a collection of workflows
Build client-side widgets using JavaScript for faster performance and better caching
Get maximum performance out of the ASP.NET AJAX Framework for faster, more dynamic, and scalable sites
Build a custom web service call handler to overcome shortcomings in ASP.NET AJAX 1.0 for asynchronous, transactional, cache-friendly web services
Overcome JavaScript performance problems, and help the user interface load faster and be more responsive
Solve various scalability and security problems as your site grows from hundreds to millions of users
Deploy and run a high-volume production site while solving software, hardware, hosting, and Internet infrastructure problems

If you're ready to build state-of-the art, high-volume web applications that can withstand millions of hits per day, this book has exactly what you need.

A significant part of sql server process memory has been paged out. This may result in performance degradation

omar — Wed, 19 Sep 2007 07:46:00 GMT

If you are using SQL Sever Server standard edition 64 bit on a Windows 2003 64bit, you will frequently encounter this problem where SQL Server says:

A significant part of sql server process memory has been paged out. This may result in performance degradation. Duration 0 seconds. Working set (KB) 25432, committed (KB) 11296912, memory utilization 0%

The number in working set and duration will vary. What happens here is SQL Server is forced to release memory to operating system because some other application or OS itself needs to allocate RAM.

We went through many support articles like:

918483: How to reduce paging of buffer pool memory in the 64-bit version of SQL Server 2005
905865: The sizes of the working sets of all the processes in a console session may be trimmed when you use Terminal Services to log on to or log off from a computer that is running Windows Server 2003
920739: You may experience a decrease in overall system performance when you are copying files that are larger than approximately 500 MB in Windows Server 2003 Service Pack 1

But nothing solved the problem. We still have the page out problem happening every day.

The server has 16 GB RAM where 12 GB is maximum limit allocated to SQL Server. 4 GB is left to OS and and other application. We have also turned off antivirus and any large backup job. 12 GB RAM should be plenty because there's no other app running on the dedicated SQL Server box. But the page out still happens. When this happens, SQL Server becomes very slow. Queries timeout, website throws error, transactions abort. Sometimes this problems goes on for 30 to 40 minutes and website becomes slow/unresponsive during that time.

I have found what causes SQL Server to page out. File System cache somehow gets really high and forces SQL Server to trim down.

You see the System cache resident bytes are very high. During this time SQL Server gets much less RAM than it needs. Queries timeout at very high rate like 15 per sec. Moreover, there's high SQL Lock Timeout/sec (around 15/sec not captured in screen shot).

SQL Server max memory is configured 12 GB. But here it shows it’s getting less than 8 GB.

While the file system cache is really high, there’s no process that’s taking significant RAM.

After I used SysInternal’s CacheSet to reset file system cache and set around 500 MB as max limit, memory started to free up.

SQL Server started to see more RAM free:

Then I hit the “Clear” button to clear file system cache and it came down dramatically.

Paging stopped. System cache was around 175 MB only. SQL Server lock timeout came back to zero. Everything went back to normal.

So, I believe there's either some faulty driver or the OS itself is leaking file system cache in 64bit environment.

What we have done is, we have a dedicated person who goes to production database servers every hour, runs the CacheSet program and clicks "Clear" button. This clears the file system cache and prevents it from growing too high.

There are lots of articles written about this problem. However, the most informative one I have found is from the SQL Server PSS team:

http://blogs.msdn.com/psssql/archive/2007/05/31/the-sql-server-working-set-message.aspx

UPDATE - THE FINAL SOLUTION!

The final solution is to run this program on Windows Startup:

SetSystemFileCacheSize 128 256

This sets the lower and higher limit for the System Cache. You need to run this on every windows startup because a restart will undo the cache setting to unlimited.

You can run the program without any parameter to see what is the current setting.

Download the program from this page:

http://www.uwe-sieber.de/ntcacheset_e.html

Go to the end and you will get the link to the SetSystemFileCacheSize.zip

Think you know how to write UPDATE statement? Think again.

omar — Sat, 20 Jan 2007 17:47:00 GMT

When I was a kid, my mom used to read me UPDATE queries every night before I went to sleep. I heared many stories about updating objects to database where most of them were the same old:

UPDATE [ Table ] SET Field1 = Value1, Field2 = Value2, . . . WHERE PRIMARYKEY = TheKey

So, I grew up with the same ideas on how to update objects in tables as other kids do. All the UPDATE queries involved taking all the fields and the update stored procedures used to have all the properties of the objects. If you are using some Code Generators (e.g. Code Smith) and generating data access layer codes and stored procedures for objects, you will see almost all the code generators and ORM tools generate UPDATE statments with all the fields in the SET block. Let me show you with an example how evil this idea is.

Imagine a table like this:

CREATE TABLE [ dbo ] . [ ChannelSubscribedByUser ] ( [ ID ] [ int ] IDENTITY ( 1 , 1 ) NOT NULL , [ UserId ] [ int ] NOT NULL , [ ChannelId ] [ int ] NOT NULL , [ ReadRSSItemIDs ] [ image ] NOT NULL , CONSTRAINT [ PK_ChannelSubscribedByUser ] PRIMARY KEY CLUSTERED ( [ UserId ] ASC , [ ChannelId ] ASC )

This is a table from Pageflakes database. In this table, we store all the RSS feeds user has read from a particular RSS channel. UserId is a foreign key to User table and ChannelID is a foreign key to Channel table. Pretty straight forward. We had a harmless update stored procedure generated using Code Smith using the famous .NET Tiers template.

ALTER PROCEDURE [ dbo ] . [ prcChannelSubscribedByUserUpdate ] @ID int , @ChannelId int , @ReadRSSItemIDs image , @UserId int AS UPDATE dbo. [ ChannelSubscribedByUser ] SET [ ChannelId ] = @ChannelId , [ ReadRSSItemIDs ] = @ReadRSSItemIDs , [ UserId ] = @UserId WHERE [ ID ] = @ID SELECT [ ID ] , [ UserId ] , [ ChannelId ] , [ ReadRSSItemIDs ] FROM dbo. [ ChannelSubscribedByUser ] WHERE [ ID ] = @ID

Look at the query plan and see how horrible it really is:

There are 2 Clustered Index Seeks, one Table Spool (very expensive), 2 Nested Loops, 1 Assert, 1 Clustered Index Seek. If you look at the IO Statistics, you can see how truly evil this query is:

Table 'RSSChannel'. Scan count 0, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'PageFlakesUser'. Scan count 0, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'ChannelSubscribedByUser'. Scan count 1, logical reads 15, physical reads 0, read-ahead reads 0, lob logical reads 3, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

It is making SQL Server go to the tables which are refered as foreign key during a single row update!

Generally when we update a row in a table which represents an object, we rarely change the value of the fields which are foreign keys to other tables and has index on them. Most of the time, the updates are on the fields which contain properties, not relations. For example, 99% of the cases, you will update properties of an Employee object like FirstName, LastName, Age etc. 1% case you will modify the CompanyID (because s/he was fired) which is a foreign key to Company Table. But if you go to your database and see the stored procedure which updates the Employee object, you will see this:

UPDATE Employee SET FirstName = @FirstName , LastName = @LastName , CompanyID = @CompanyID WHERE EmployeeID = @EmployeeID

Don't be ashamed. I know we all have queries like this every where.

If you remove those unwanted fields which generally have Foreign Key and Index on them, you can gain significant performance improvement. When I just change the UPDATE statement to this:

UPDATE dbo. [ ChannelSubscribedByUser ] SET [ ReadRSSItemIDs ] = @ReadRSSItemIDs , WHERE [ ID ] = @ID

See the query plan:

There's just one Clustered Index Update. No Table Spool, no Clustered Index seeks, no nested loops, no asserts. The IO statistics shows how significant the improvement really is:

Table 'ChannelSubscribedByUser'. Scan count 1, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 3, lob physical reads 0, lob read-ahead reads 0.

There's just one update and nothing else. Also the number of Logical Reads is 6 compared to 15.

So, you thought you knew how to write UPDATE statements? Think again.

Large log file can bring SQL Server down when transaction log shipping runs

omar — Sun, 17 Sep 2006 06:04:00 GMT

We were having very poor performance when we turned on transaction log shipping on our SQL Server. We are using SQL Server 2005. The transaction log file was around 30 GB because the database was in Full Recovery mode. The server became very slow, every 15 mins when we were doing the log shipping, it used to become very slow and sometimes nonresponsive. The event log was getting full of SqlTimeout exceptions generated by the web site. The web site started to show asp.net error page very frequently. We could not use SQL Server Management Studio to login to SQL Server so that we could do something about it.

Here's how the connection time was reported from an external monitoring site:

The peaks are 30 seconds which mean they timed out.

So, here's what we did:

Turned off Log shipping
Restarted SQL Server.
Switched Database to Simple recovery model. Shrunk the log file. This made the log file come down to couple of megabytes.
Ran for some days. All looked ok.
Then switched DB to Full Recovery model and configured log shipping again.

So far running fine. But we go down for an hour every Saturday when we run INDEX DEFRAG on the indexes. The log ships show around 5 or 6 log backups which are each 1 or 2 GB in size when the index defrag happens.

How to setup SQL Server 2005 Transaction Log Ship on large database that really works

omar — Fri, 15 Sep 2006 10:01:00 GMT

I tried a lot of combinations in my life in order to find out an effective method for implementing Transaction Log Shipping between servers which are in a workgroup, not under domain. I realized the things you learn from article and books are for small and medium sized databases. When you database become 10 GB or bigger, thing's become a lot harder than it looks. Additionally many things changed in SQL Server 2005. So, it's even more difficult to configure log shipping properly nowadays.

Here's the steps that I finally found that works. Let's assume there are 2 servers with SQL Server 2005. Make sure both servers have latest SP. There's Service Pack 1 released already.

1. Create a new user Account named "SyncAccount" on both computers. Use the exact same user name and password.

2. Make sure File Sharing is enabled on the local area connection between the server. Also enable file sharing in Firewall.

3. Make sure the local network connection is not regular LAN. It must be a gigabit card with near zero data corruption. Both cable and switch needs to be perfect. If possible, connect both servers using Fibre optic cable directly on the NIC in order to avoid a separate Switch.

4. Now create a folder named "TranLogs" on both servers. Let's assume the folder is on E:\Tranlogs.

5. On Primary Database server, share the folder "Tranlogs" and allow SyncAccount "Full Access" to it. Then allow SyncAccount FullAccess on TranLogs folder. So you are setting the same permission from both "Sharing" tab and from "Security" tab.

6. On Secondary database server, allow SyncAccount "Full Access" right on TranLogs folder. No need to share it.

7. Test whether SyncAccount can really connect between the servers. On Secondary Server, go to Command Prompt and do this:

9. Now you have a command prompt which is running with SyncAccount privilege. Let's confirm the account can read and write on "TranLog" shares on both servers.

10.

11. This is exactly what SQL Agent will be doing during log ship. It will copy log files from primary server's network share to it's own log file folder. So, the SyncAccount needs to be able to both read files from primary server's network share and write onto its own tranlogs folder. The above test verifies the result.

12. This is something new in SQL Server 2005: Add SyncAccount in SQLServer Agent group "SqlServer2005SqlAgentUser$....". You will find this Windows User Group after installing SQL Server 2005.

13. Now go to Control Panel->Administrative Tools->Services and find the SQL Server Agent service. Go to its properties and set SyncAccount as the account on the Logon tab. Restart the service. Do this on both servers.

14.

15. I use sa account to configure the log shipping. So, do this on both servers:

a. Enable "sa" account. By default, sa is disabled in SQL Server 2005.

b. On "sa" account turn off Password Expiration Policy. This prevents sa password from expiring automatically.

16. On Secondary server, you need to allow remote connections. By default, SQL Server 2005 disables TCP/IP connection. As a result, you cannot login to the server from another server. Launch the Surface Area Configuration tool from Start->Programs->MS SQL Server 2005 and go to "Remote Connection" section. Choose the 3rd option which allows both TCP/IP based remote connection and local named pipe based connections.

17. On Secondary Server firewall, open port 1433 so that primary server can connect to it.

18. Restart SQL Server. Yes, you need to restart SQL Server.

18. On Primary server, go to Database properties->Options and set Recovery Model to "Full". If it was already set to full before, it will be wise to first set it to Simple, then shrink the transaction log file and then make it "Full" again. This will truncate the transaction log file for sure.

19. Now take a Full Backup of the database. During backup, make sure you put the backup file on a physically separate hard drive than the drive where MDF is located. Remember, not different logical drives, different physical drives. So, you should have at least 2 hard drives on the server. During backup, SQL Server reads from MDF and writes on the backup file. So, if both MDF and the backup is done on the same hard drive, it's going to take more than double the time to backup the database. It will also keep the Disk fully occupied and server will become very slow.

20. After backup done, RAR the database. This ensures when you copy the database to the other server there's no data corruption while the file was being transferred. If you fail to unRAR the file on the secondary server, you get assurance that there's some problem on the network and you must replace network infrastructure. The RAR also should be done on a separate hard drive than the one where the RAR is located. Same reason, read is on one drive and write is on another drive. Better if you can directly RAR to the destination server using network share. It has two benefits:

a. Your server's IO is saved. There's no write, only read.

b. Both RAR and network copy is done in one step.

21.

22. By the time you are done with the backup, RAR, copy over network, restore on the other server, the Transaction Log file (LDF) on the primary database server might become very big. For us, it becomes around 2 to 3 GB. So, we have to manually take a transaction log backup and ship to the secondary server before we configure Transaction Log Shipping.

23.

24. When you are done with copying the transaction log backup to the second server, first restore the Full Backup on the secondary server:

25.

26. But before restoring, go to Options tab and choose RESTORE WITH STANDBY:

27.

28. When the full backup is restored, restore the transaction log backup.

29. REMEMBER: go to options tab and set the Recovery State to "RESTORE WITH STANDBY" before you hit the OK button.

30. This generally takes a long time. Too long in fact. Every time I do the manual full backup, rar, copy, unrar, restore, the Transaction Log (LDF) file becomes 2 to 3 GB. As a result, it takes a long time to do a transaction log backup, copy and restore and it takes more than an hour to restore it. So, within this time, the log file on the primary server again becomes large. As a result, when log shipping starts, the first log ship is huge. So, you need to plan this carefully and do it only when you have least amount of traffic.

31. I usually have to do this manual Transaction Log backup twice. First one is around 3 GB. Second one is around 500 MB.

32. Now you have a database on the secondary server ready to be configured for Log shipping.

33. Go to Primary Server, select the Database, right click "Tasks" -> "Shrik". Shrink the Log File.

34. Go to Primary server, bring on Database options, go to Transaction Log option and enable log shipping.

35.

36. Now configure the backup settings line this:

37.

38. Remember, the first path is the network path that we tested from command prompt on the secondary server. The second path is the local hard drive folder on the primary server which is shared and accessible from the network path.

39. Add a secondary server. This is the server where you have restored the database backup

40.

41. Choose "No, the secondary database is initialized" because we have already restored the database.

42. Go to second tab "Copy Files" and enter the path on the secondary server where log files will be copied to. Note: The secondary server will fetch the log files from the primary server network share to it's local folder. So, the path you specify is on the secondary server. Do not get confused from the picture below that's it's the same path as primary server. I just have same folder configuration on all servers. It can be D:\tranlogs if you have the tranlogs folder on D: drive on secondary server.

43.

44. On third tab, "Restore Transaction Log" configure it as following:

45.

46. It is very important to choose "Disconnect users in database…". If you don't do this and by any chance Management Studio is open on the database on secondary server, log shipping will keep on failing. So, force disconnect of all users when database backup is being restored.

47. Setup a Monitor Server which will automatically take care of making secondary server the primary server when your primary server will crash.

48.

49. In the end, the transaction log shipping configuration window should look like this:

50.

51. When you press OK, you will see this:

52. Do not be happy at all if you see everything shows "Success". Even if you did all the paths, and settings wrong, you will still see it as successful. Login to the secondary server, go to SQL Agents->Jobs and find the Log Ship restore job. If the job is not there, your configuration was wrong. If it's there, right click and select "View History". Wait for 15 mins to have one log ship done. Then refresh and see the list. If you see all OK, then it is really ok. If not, then there are two possibilities:

a. See if the Log Ship Copy job failed or not. If it fails, then you entered incorrect path. There can be one of the following problem:

The network location on primary server is wrong
The local folder was specified wrong
You did not set SyncAccount as the account which runs SQL Agent or you did but forgot to restart the service.

b. If restore fails, then the problems can be one of the following:

i. SyncAccount is not a valid login in SQL Server. From SQL Server Management Studio, add SyncAccount as a user.

ii. You forgot to restore the database on secondary server as Standby.

iii. You probably took some manual transaction log backup on the primary server in the meantime. As a result, the backup that log shipping took was not the right sequence.

53. If everything's ok, you will see this: