Splitting out one table to a separate server

Question

Hi all,

Background, I am running MS SQL Server 2005.

What do you guys think about this.

I have an analytics process whereby a stored procedure is fired off on every page of my website and the results logged into my analytics table. Given that I have thousands of users on my site daily, this a very frequent albeit "light" overhead to the system.

Therefore, I am thinking of reducing the load on my server and spinning the analytics table to a separate server. Practically speaking, how would I do this though? I am aware of partitioning functionality in SQL Server, is that where I should be starting from?

Would it not be easier to create a database just to house the analytics server and than use SSIS to extract information from my new analytics database with whatever data I need from my back up server.

While I'm on this, this analytics server setup is just going to be passively receiving incoming requests and periodic downloads to a OLAP reporting server for analysis. On this note, what do you guys reckon about hosting this on Amazon's EC2/S3 combination vs. a dedicated server. This server will not be experiencing traffic spikes which negates one of the strong points of hosting this on the cloud. However, it could potentially be cheaper for us in the short run plus easier to maintain.

I hope this is not too vague. Cheers!

UPDATE Why not use Google Analytics? I can't track on a per user level with Google Analytics or on pre-segmented groups.

Answer 1

Why not just use google analytics?

Answer 2

You could:

1. Create a new table, even better a DW (star schema) on a new server.
2. Keep the “transaction table” on the current server.
3. Periodically (hour, day) ETL from the "transaction table" to the DW on the new server.
4. Truncate the "transaction table" after the ETL process.
5. Point reporting and analytics to the new server (DW).

You can use the SSIS for the ETL, run the package from the new server.

Answer 3

Why not refactor your web application so that it connects directly to the separate server to do the logging? As long as you are using connection pooling there shouldn't be a concern about creating a new database connection. Also I would recommend doing the logging asynchronously so your entire site won't crash if there is a performance issue in your logging database.