I am having severe trouble with a SQL Server - in particular, with transactional replication.
We used to have a Windows 2003 physical machine, with SQL 2000. We call this “WebDB” The server was located in a datacenter, and we connected to it over WAN.
Back in the office, we have a Windows 2008, with SQL 2008. We call this “OfficeDB” This has transactional replication publication with around 20 catalogs. It was replicating to this physical server with no problems.
Recently, we deployed a new server, on a VM (Esxi) hosted environment. This environment is also connected to over WAN.
This server, has SQL 2008 R2 installed. It has 12gb memory.
We took a backup of the original WebDB and restored it on the new vWebDB. I configured it as a subscriber to our publication from OfficeDB.
After initializing, everything seemed to be working ok.
We switched our web app to use the new vWebDB server.
CPU seems to be constantly hovering around 100% Nothing on the web app has changed - the queries (although somewhat inefficient- and i will look at this in due course) have remained exactly the same.
Replication latency is up in the region of 10 mins - 2 hours (varies)
If i disable replication, the vWebDB server seems to calm down, and CPU usage drops to around 50 - 60 %
With replication enabled however, eventually, the DB starts timing out, and users get errors etc...
Things of note:
I did a straight backup / restore of the original database, onto the new, virtual hardware. The thing with this is the MDF / LDF files are split over 2 virtual disks. Is this good practice in a virtual environment?
The distributor was (and still is) on the OfficeDB server. The distribution Agent is the distrubutor (push subscription)
The thing that puzzles me is this setup was working fine on the old machine. I am open to suggestions on fixing this. I will edit this post with answers to questions if relevant.