April 18, 2012 at 12:47 pm
So I am looking for some opinions on how to determine a reporting solution. Here is our current set up and for arguments sake lets just assume I can't change the design.
We have 16 windows servers with a total of 20 database instances. Using Sql Server 2008 Standard Edition. We have approximately 300 databases per instance that are identical with one shared server that has all common tables. Each database is a different customer. Each customer database can range anywhere form a few hundred megabytes to a few hundred gigabytes.
Our databases are high OLTP, And we would like to pull the reporting out of the current customers databases and separate our OLTP and OLAP as much as possible. If we were to build new sql servers for every current server obviously that would be extremely expensive.
Has anyone tried using a combination of Sql Server and an Open Source database for this? Does anyone have any other ideas?
April 18, 2012 at 12:52 pm
I don't like the open source idea... BTW, open source <> free.
I would design a solution with 1 or 2 reporting servers that get the data replicated to them or some sort of ETL operation. Then optimize those servers for read only access.
Jared
CE - Microsoft
April 18, 2012 at 12:59 pm
SQLKnowItAll (4/18/2012)
I don't like the open source idea... BTW, open source <> free.I would design a solution with 1 or 2 reporting servers that get the data replicated to them or some sort of ETL operation. Then optimize those servers for read only access.
+1 on open source != Free. you also have to factor in having to transform your data and code to what ever other DB platform you want to use (you cant just restore a backup to get you started if you are going to have the same structure). you also have the headaches of a mixed environment when things go wrong. there may be allot of support for MSSQL and the other DB product but probably not much support on making the two talk to each other.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
April 18, 2012 at 2:30 pm
Jared,
So your thought is for all 10000 databases (approx), I should consolidate into 2 Report servers? Can Sql SErver handle that? We expect to have almost double the amount of databases by next year. Currently there is about 2 terabytes of data. Now not all of it is reportable information. But...
April 18, 2012 at 2:35 pm
pamozer (4/18/2012)
Jared,So your thought is for all 10000 databases (approx), I should consolidate into 2 Report servers? Can Sql SErver handle that? We expect to have almost double the amount of databases by next year. Currently there is about 2 terabytes of data. Now not all of it is reportable information. But...
Sorry... My point was you don't need 1 server for each server you currently have. You can have 32,767 databases per instance. Maybe you can do it one 1, maybe it will take 5, or 8... You have to plan that based on what your company's needs and budget is. It "may" make sense for you to combine it all into 1 or more data warehouses. You have to decide that. However, it will cost you more in the long run (IMO) to convert data and try to introduce a new skillset for a free solution that has no support and bears no responsibility to the developer.
Jared
CE - Microsoft
April 18, 2012 at 5:06 pm
The main point is that the optimal configuration for servers running transactional databases is not optimal for server running reporting databases (and vice versa). In addition, data structures that work very well for transactional processing (lots of very thin tables with lots of joins and no duplication of data) do not work very well for reporting.
If you have 16 servers running transactional databases, then it would make sense to have 8, 12 or 16 servers for reporting databases.
April 18, 2012 at 6:50 pm
Agreed. But when you have limited funds you have to try to find working alternatives.
April 18, 2012 at 10:07 pm
I think you may need data warehouse not single reporting and you could only ETL these objects you needed in report not everything. After that, you could optimize your DW such as adding more indexes.
And since you have lots of common DBs, you could consolidate them together.
April 19, 2012 at 9:51 am
For Reporting purposes having multiple instances on one server does that hurt or help in anyway? So If I were to take my current 20 instances from 16 servers and put them on to 2 servers. What does that do for me? If I separate instances I will have less stress on tempdb correct? Is there anything else? Obviously there will be more IO than when they were separated but since I no longer have the writes it shouldn't be as bad.
May 17, 2012 at 12:10 pm
So I have gone away from the idea of using open source but I am now trying to determine the best way to get the data from the oltp databases to the warehouse(s). Is transactional replication a good way to go? When you have 10000 databases and you need to replicate 20 tables from each database is that good use of replication? How difficult is it to manage?
I guess my concern is managing replication for 10000 databases. Is that a reasonable concern?
May 17, 2012 at 2:07 pm
If you only need a subset of tables and need that data transformed into Dimension and Fact tables, I'd recommend using SSIS to perform the ETL tasks.
MWise
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply