January 12, 2010 at 8:59 am
I have been asked to build a data warehouse server (my first one) for a SQL programmer. The programmer has been working from a decent desktop PC and is now ready to move his work from the desktop to a server.
Could someone tell me if there are any generic settings I need to configure (or set) when installing SQL 2005 Standard and SQL Reporting services on this server. For example: I know in a transactional database (for performance) I need to have my LDF and MDF files on different drives. Is this true for a data warehouse too? Or can I put both on the same drive since users will be looking at data and not updating data? Will I need second or third drive location for items related to the SQL Reporting services?
At the moment the larges database I could find on the PC running SQL is 100MB.
July 11, 2011 at 4:09 pm
I don't believe you would need separate files for a DB that size. You may wnat to consider SQL 2008 vs. 2005 if you need to use SSRS. The two products are quite different and 2008 is much better. If using 2005, you may want to consider SQL Server and MS Access for reporting. I did this for a much larger DB with success.
We did move to SSRS when we moved to SQL 2008.
The LDF for Access is for "Locked" which is mute in SQL Server depending on how you set it up and for reporting purposes I would not recommend using the options.
Separate data files are very useful for larger volume in my opinion. Using for smaller volume is more DBA effort than truly needed.
Hope this helps.
July 13, 2011 at 5:04 am
Before you take Howard's advice, consider this:
Will the datawarehouse always be 100 MB?
It is my experience that datawarehouses tend to grow exponentially, especially if there is more than one course feeding them. You need to consider forecast your data growth based on the growth of the sources, then make your data file considerations.
Also, look at your drive set up. Are you using SAN or NAS? Which type of RAID (if any)? What will your recovery solution be (which drives your backup solution, which also has an impact on how your files are set up)?
What about security? Who will be accessing the warehouse and how will it be accessed (third party app, cube, reports only, direct warehouse access via SSMS)?
How much traffic do you expect to be hitting the warehouse? Is your network card able to handle it? Are you in a clustered environment that needs a virtual server name or is this a single server set up?
Is there a firewall in the way that might need a hole burned through it?
I could go on and on about server configurations. There are too many things to go over and I have no idea what your corporate environment is like. If you really want good setup advice, you should pay a consultant to come in and teach you what works for your company and your particular needs. That way, the information is specialized and works rather than it being hit-n-miss generalizations that you get off the forums.
July 13, 2011 at 5:41 am
I agree with all of Brandie's comments. The key to me is the move from a "desktop" to a "PC" vs. to a "server". The reasons for such a move may be numerous and Brandie's comments are quite valid if probable growth is the primary issue.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply