a bit of background :
I am attempting to configure transactional replication for a database. The intention is for the Live database to replicate to a database on another server which is to be used for Business Objects reporting thus reducing the load on the "Live" system. In line with best practices (i think) I am using the reporting database server to host the distribution database and am trying to set up a pull subscription. I would like to initialise the reporting database for transactional replication using a backup rather than a snapshot as it is my understanding that this method will be much quicker.
I started to create the publication (adding articles) yesterday at 15:00. The articles were still being created this morning at 09:00 and approx 91,000 of 140,000 had been created. Since the remaining articles were being created extremely slowly I decided to cancel the publication creation and modify a memory setting on the server (it's x64 and max memory hadn't been configured....don't ask). I cancelled the publication and set the max server memory to 14GB of an available 16GB thus leaving 2GB for the OS. The server then felt a lot less sluggish.
I decided I would now delete the publication and recreate it. First I attempted to do this via the GUI. The result - SQL Server locked a stack of objects and users started to experience connectivity issues to the live system....great!! I killed the process, apologised to the ServiceDesk for the increase in their call volume and waited until a quieter time in the day to remove the publication, this time using sp_droppublication. This has currently been running for an hour and a half.
My questions are two-fold.
1. Have I gone about removing the publication the wrong way, or does it usually take this long to drop a publication with this many articles (it is my understanding it has to drop lots of triggers and views in the live system)
2. Is transactional replication the best solution to maintain a copy of the live database for reporting purposes and does anyone out there have practical experience of using it in this way?
I would really appreciate any advice, condolence or support you can offer me.
Thanks in advance