I'm looking for some advice on how to handle reporting for our environment. We currently have 16 servers with 20 instances of SQL Server 2005. We have over 6,600 databases and growing across these instances (1 database per customer). The majority of our databases run in size from 200mb to 7gb with about 60 databases running in size from 11GB to 110 gb at the largest.
We are using a SAN for storage and we are running into issues with the running of the reports affecting IO.
One idea we had was to pull the 60 larger databases and then use transactional replication to replicate these databases and run reporting on the replicas.
That would then leave all the smaller databases to run without the stress of the larger databses. In the future we don't believe there will be any more of the the larger databases based on our company goals.