Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ED_KING
Starting Member
7 Posts |
 Posted - 2013-10-28 : 15:32:13
|
| Looking for some guidance, help or even a consultant to help me solve a growing problem we are starting to run into with OLTP and BI data. SQL 2008 R2 (will be going to 2012 but not for another 6+ months)Problem:Our infrastructure is a 2 node cluster with a single-tenant design where each client has its own databases. We have about 3,000 databases. The problem we are running into is these databases are mostly OLTP and designed as such so you can imagine when we try to get BI reports or run any reports it’s slow and a lot of blocking occurs. It wasn’t such a big deal but now we are getting bigger clients and we are building bigger reports that they can run.I know we need to start migrating data out of these databases into a more OLAP type database but how? We did setup a replication model only replicating the log tables that we needed for data but it didn’t scale well.So my question to you guys is what path should I start looking at? Anyone have experience with this?Thanks. |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2013-10-28 : 16:00:11
|
| Are your OLAP requirements such that you need to analyze data across all/multiple clients? Maintaining 3000 DBs must be a royal pain. I'm sure this is water under bridge but I think it was a mistake to model it so that each new client gets a new set of DBs.Any chance you could consider modifying your model so that your tables are partitioned or at least clustered by ClientID so that you have basically one set of databases for all clients rather than a set of DBs for each client? Be One with the OptimizerTG |
 |
|
|
ED_KING
Starting Member
7 Posts |
Posted - 2013-10-29 : 00:28:36
|
Yeah its too late for that but we had much success with the single tenant design from security to backups to being able to quickly create staging environments and sand boxes for clients to simply not having to have 32 core servers with 1TB+ of ram. I'm able to span them out to up to 8 nodes on much cheaper boxes. I understand the advantage of both setups but since we designed our system from the ground up on this type of design it has been very easy to manage. If we were to run one DB it would be over 15TB.As for the OLAP requirements its just for the client to run queries and filters against their own data. We have other databases for system wide reports. quote:
Originally posted by TG
Are your OLAP requirements such that you need to analyze data across all/multiple clients? Maintaining 3000 DBs must be a royal pain. I'm sure this is water under bridge but I think it was a mistake to model it so that each new client gets a new set of DBs.Any chance you could consider modifying your model so that your tables are partitioned or at least clustered by ClientID so that you have basically one set of databases for all clients rather than a set of DBs for each client? Be One with the OptimizerTG
|
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2013-10-29 : 10:21:54
|
| Not doubting you as it sounds like you have your <stuff> together but I'm curious, how do you manage upgrades across so many clients? For instance if you modify an SP or introduce a new one how do you get that done on all client DBs? And do you keep all client DB sets in sync or do you allow differences and somehow track who has what?I suppose it could be good news that your issue now is for client's reporting so you don't need to "combine" data across clients. Do you think you have the option to keep whatever solution you come up with within the existing client db sets as opposed to migrating to different servers? If the slowness is due to contention then that may be a possibility. but if the servers are simply over taxed then maybe not.Be One with the OptimizerTG |
|
|
|
|
|
|
OLTP + OLAP Issues