In my first “Drupal on Windows” blog post, I wrote about using Windows authentication with SQL Server. That’s a recommended best practice for SQL Server in any production environment. So with the knowledge that Drupal can run on Windows technologies in a production environment, how can we scale Drupal to achieve high availability? How do we move beyond a single web farm?

One solution is:

• Geographically dispersed web farms using Global Load Balancing
  • Each web farm resides in it’s own data center
  • Each web farm utilizes it’s own SQL Server instance
• SQL Server Merge Replication
  • Keep the database in each web farm in sync, so the served content is identical

SQL Server Merge Replication

Setting up merge replication (and global load balancing) is beyond the scope of this blog post, but there are lots of great instructions out there. I would recommend the following reading for those not familiar with merge replication:

• Replication Publishing Model Overview (general replication introduction)
• Merge Replication Overview
• How Merge Replication Works

Merge replication is one of the key technologies that enables the high availability of running global load balancing between data centers. The remainder of this blog post is dedicated to enabling Drupal to run properly with SQL Server Merge Replication.

Lessons Learned – Merge Replication with Drupal

PDO::lastInsertId Issue

After creating a publication for your Drupal database, you will start to notice that some Drupal functionality stops working properly. Specifically, most actions that insert data into the database will fail. When I first ran into this issue, it took some digging to figure out. The problem is with the PDO::lastInsertId method.

What does the PDO::lastInsertId method do? According to the documentation on PHP.net, it “Returns the ID of the last inserted row”.

To understand this problem in depth, recall how database access works in Drupal. When using SQL Server, we go through:

1. Drupal core database layer – PDO
2. Drupal sqlsrv driver
3. Microsoft Drivers for PHP for SQL Server (PDO)
4. SQL Server

At the SQL Server level, using SQL Server Profile to capture the queries being executed, I could see that Drupal was executing this statement after attempting inserts:

Having had previous experience with merge replication, I knew immediately that this was the root of the problem. You absolutely cannot trust the value returned by @@identity when replication is enabled. Why? Because replication triggers execute automatically after an insert, causing the value that is returned to be completely different than what you would otherwise want.

So how do we fix the problem? Let’s start with the SQL Server syntax and work back up the layers to Drupal once we’ve identified what we should be using. There are 3 ways to obtain the last insert ID in SQL Server:

1. @@IDENTITY
   • Returns the last identity value generated in any table in the current session, in any scope
2. IDENT_CURRENT(‘table name’)
   • Returns the last identity value generated for a specific table
3. SCOPE_IDENTITY()
   • Returns the last identity value generated in any table in the current session, in the current scope

Obviously, we need to modify our queries to use either of the last two. How do we go about doing that? Let’s start by looking at the next level up – the Microsft Driver for PHP for SQL Server.

Microsoft Driver for PHP for SQL Server

After looking at the PDO::lastInsertId documentation, you will notice that we can pass a table name to the method. This sounds like it uses IDENT_CURRENT() internally. How can we be sure? Let’s look at the source code for the driver!

Since this driver is open sourced, we can easily go and see how it actually works internally. The relevant code is located in the pdo_dbh.cpp file, as constants:

After seeing the source code, we can tell that our only two options with the driver are @@IDENTITY, or IDENT_CURRENT(). If we wanted to use SCOPE_IDENTITY(), we would be forced to change the source code and make our own build of the PHP drivers for SQL Server. Fortunately, IDENT_CURRENT() will work for our needs.

So how do we go about making Drupal use IDENT_CURRENT()? We need to get Drupal to pass a table name when calling the PDO:lastInsertId method. To accomplish that, we’ll need to make some changes to the sqlsrv driver for Drupal.

sqlsrv driver for Drupal

The main role of the sqlsrv driver for Drupal is to translate database queries to be compatible with SQL Server. This is the first layer where we can make actual changes to the queries being executed against SQL Server.

The file that we need to make modifications to is located at includes/database/sqlsrv/query.inc.

To fix the PDO::lastInsertId issue, replace all instances of:

with:

After this change, Drupal inserts will now call IDENT_CURRENT(‘table name’) instead of @@IDENTITY to get the last identity value.

TRUNCATE Issue

The next issue that you’ll encounter after enabling merge replication, is that you cannot clear the caches in Drupal. You will receive an error stating that the TRUNCATE keyword cannot be used on a table that is enabled for replication. The error message is very straight forward. Because TRUNCATE bypasses the transaction log, it’s actions therefore cannot be replicated (replication relies on the use of the transaction log).

So how do you go about fixing this issue? Very similar to the PDO::lastInsertId issue. Make a modification to the query.inc file from the sqlsrv driver for Drupal.

Again, open the includes/database/sqlsrv/query.inc file.

Replace the following statement:

with:

See also:
TRUNCATE

Cache Tables Replication Issue

If you add the Drupal cache tables (anything cache_*) as articles in your publication, you will run into problems. With my first pass at merge replication, I just added everything in the database as articles in my publication. This led to a occasional deadlocking, where there were so many conflicts and retries occurring that the database stopped responding. It was easy to see that the cache tables were the center of this problem, when looking in Replication Monitor. After some quick google searches, I could see that it is recommended for MySQL Master-Slave replication that you exclude these tables. I heeded this advice and excluded them from my publication in SQL Server. Problem solved, with nothing but smooth sailing since that change.

Conclusion

While Drupal may not have been built specifically with SQL Server Merge Replication in mind, it works great with relatively few modifications. It certainly enables high availability on Windows technologies. I hope this article can help others who find themselves faced with similar requirements of using Drupal and SQL Server, along with merge replication.

Related posts: