April 24, 2009 at 2:08 pm
I am an acting DBA, though my background is better described as being a SQL Programmer.
We are about to start a project to rewrite our in-house custom inventory app (VB.net and SS). We currently have 100 users, but we could easily grow to 600+ in a few years. One idea I have to help the app scale is to have two SQL Servers, the master server and a read-only copy (one way replication). The read-only server would be used by all reporting functions and could be used by the VB.net app.
My main question right now is: Is it a good idea to have the VB.net developers use 2 SQL Connections, one for read-only and one for updates? Initially they would probably be going to the same box, but, we could switch to the two server scheme outlined above if performance becomes an issue. I suppose could even move to multiple read-only copies. (I do anticipate a lot more read activity than update.)
Is this a reasonable solution? Or should I use clustering instead? I do not have any experience with clustering. We are not necessarily going to need an Enterprise Edition... we have not yet selected our Edition. Comments?
Thanks,
Chris
April 24, 2009 at 5:32 pm
Now, you are referring to an entire solution for a proper database management system.
Let me see if I can analyze your question and therefore be able to answer component at the time.
To have a database for your reporting purposes:
Well, I do recommend a reporting services server as a started. This service should use a read only data.
Read only database:
You should take advantage of the sql server replication engine and of course you need to make sure that all tables of yours have proper indexing schemes for that matter.
Development environment:
The dev should not have access to both servers. Just the one that is normally being used for production and of course have a test environment that would have both sql and the vb or c# code.. in a segmented and virtual LAN to avoid hitting the main productions.
Hope this helps.
Cheers,
John Esraelo
April 24, 2009 at 9:54 pm
John,
Thank you for the answer, however, I don't know if you understood my suggestion. I am only describing the production environment. I agree I would also have dev and qa/testing environments, but, for a moment let's only consider production.
Does it seem like a good & reasonable idea to have my VB.net client point to the read only server when it knows it is doing a read only action?
Thanks,
Chris
April 25, 2009 at 4:36 am
Chris,
I think I understand what you're after; you're basically envisioning one "version" of SQL for read/write, one (that may be tuned differently, etc) for reporting. A fairly common scenario, and certainly doable.
The next question to think about is how to get the data from your production version of the database to the reporting version; part of that will depend on how much difference your situation can tolerate between the production data and the reporting data. If someone runs a report are they expecting the exact same data that they just entered in the main application, or can that data be 30 minutes old and your users will tolerate it?
Clustering is not going to work for you in this scenario; the clustered version of the database won't be accessible for you to use for reporting. I would recommend that you read up on the differences between log-shipping and replication, and see which one of those technologies would work best in your environment.
David
April 25, 2009 at 6:12 am
Hi Chris
Sounds like a classic data-warehouse scenario. You should consider to simplify the data structure on your read-only database for better reporting performance. Most times there are many information in the OLTP (your writable) database which are not needed for any reporting but the structure is too normalized for high performance reporting.
Consider a SSIS (or any other ETL) job to transport the data to your DW database.
Greets
Flo
April 25, 2009 at 9:29 am
I recommend you read up on clustering.. Clustering has nothing to do with the reporting and the reporting service, as merely it's for disaster recovery, business resumption and importantly availablity of data to the business.
What you really need for your reporting is some sort of replication method.
good luck!
Cheers,
John Esraelo
April 25, 2009 at 10:05 pm
I do appreciate everyone's comments, but, I'm not talking about a reporting server - at least not in the classical sense. For what it is worth, we do already have a data warehouse, Kimball style, with Business Objects being the primary consumer.
I am worried that our VB.net application will overwhelm the SQL server with requests. I figured a solution would be to have the VB.net application do its reads from a second server. The data in the read only copy would need to be fairly fresh, probably within 10 - 15 seconds. I was thinking one way replication.
Maybe someone could convince me that we won't have any sizing problems. Currently we have about 300 users, total Data size approx. 300 GB, largest tables have approx. 30 million rows. I need to design a solution that can handle approximately triple that. I can buy new hardware for this.
Thanks,
Chris
April 26, 2009 at 4:53 am
Chris Hancock (4/25/2009)
I do appreciate everyone's comments, but, I'm not talking about a reporting server - at least not in the classical sense. For what it is worth, we do already have a data warehouse, Kimball style, with Business Objects being the primary consumer.I am worried that our VB.net application will overwhelm the SQL server with requests. I figured a solution would be to have the VB.net application do its reads from a second server. The data in the read only copy would need to be fairly fresh, probably within 10 - 15 seconds. I was thinking one way replication.
Maybe someone could convince me that we won't have any sizing problems. Currently we have about 300 users, total Data size approx. 300 GB, largest tables have approx. 30 million rows. I need to design a solution that can handle approximately triple that. I can buy new hardware for this.
Thanks,
Chris
Chris,
the questions of your server(s) being overwhelmed with requests or having sizing problems, will depend on your server and network configuration just as much as how your application behaves. I would definitely recommend getting your network / server support groups involved as early and often as possible in your planning process.
One other suggestion to consider would be to have a different set of indexes on your second server than what is on the transactional server, tuned specifically for the types of reads that will be happening. Sounds like a very interesting project.
David
April 26, 2009 at 5:29 am
Chris Hancock (4/25/2009)
Maybe someone could convince me that we won't have any sizing problems. Currently we have about 300 users, total Data size approx. 300 GB, largest tables have approx. 30 million rows. I need to design a solution that can handle approximately triple that. I can buy new hardware for this.
No offence intended to you or anyone there, but I strongly suggest you look at getting a competent SQL consultant in to evaluate the design and scalability and to advise you. Especially since you don't (from what I read) have an experienced DBA there.
Forum advice is all well and good, but it's no substitute for someone working directly with you.
As for your second server, replication (transactional replication) can be used to create a second server. The latency depends on the network and the amount of changes that happen. Do note that replication isn't free. It does add load to the publisher, if the load is heavy it does need a separate distribution server. The more changes that happen, the greater the load on the source system is.
600 concurrent users on a 1TB or so DB is possible, but it needs to be architected and designed for that kind of load. It won't happen by accident.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 26, 2009 at 10:17 am
Gail,
No offense, I appreciate that position. I was a consultant for 10 years (certainly not as a DBA), so I do see the value they represent. I believe I can get the money for a week or two, but probably not a long time. The project hasn't officially kicked-off yet, and I am not the PM on this one... I'm just trying to flesh out our options.
David,
Thank you for the advice. The network and server guys are involved.
Thanks guys, any additional comments are still welcome. 🙂
Chris
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply