Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

My production database is SQL Server 2008 R2 Express. It has limited features and I am testing it in a production environment. To better handle reporting queries in a highly concurrent environment, I want to use any open-source RDBMS with tables that will be acting as temporary tables to frequently fill recent records and delete them after use.

The only problem is that I need to keep two connections for each RDBMS, but I feel, Firebird or PostgreSQL can give me better performance of reports in a highly concurrent environment.

What are your suggestions? Is this approach used in any commercial scenario?

share|improve this question
    
Are you asking about the general approach or for specific database recommendations? –  Oded Jan 14 '12 at 10:44
1  
Isn't this question better suited for the DBA Stack Exchange site? According to their FAQ it would seem on topic there. –  PersonalNexus Jan 14 '12 at 10:46
    
@PersonalNexus: I guess this question involves programmers also, so not entirely suited for DBA.StackExchange. –  RPK Jan 14 '12 at 10:54
    
@Oded For any specific scenarios. –  RPK Jan 14 '12 at 10:55
1  
I do not know about SQL Server, but MySQL supports different storage engines and you can choose a storage engine for your database geared towards more writes or more reads. MySQL also supports the powerful search server tool called Sphinx, which makes searching many times faster in mysql. –  Imran Omar Bukhsh Jan 14 '12 at 11:00

2 Answers 2

up vote 3 down vote accepted

Beware of the programmer's solution. We are too willing to take on the challenges brought on by the current restrictions. Why have two different databases when you can buy the recommended version of SQL Server for a production environment or just switch completely to an open source rdbms if costs are an issue?

Eventually, you may have to off-load reporting to another server or datamart, but you're not there yet just because you've pushed the boundaries of the Express version.

share|improve this answer
    
I want to move to Firebird or PostgreSQL, but don't know how easily support is available for PostgreSQL and Firebird on StackOverflow and other sites. Upgrading SQL Server to Enterprise Edition involves much official debate. –  RPK Jan 15 '12 at 8:27

The term 'reporting' is a bit loose. Some people use it to reference the result of basic queries. If this is the case, then as Jeff O says 2 different databases is not good if you need to update both databases with each transaction.

If you mean serious reporting, your options are to:

1 - Use the non-express version (as suggested by Jeff O) or

2 - Use a Data Mart type of solution (with its own database) with Star Schema and ETL jobs to periodically refresh the 2nd database. This is not as trivial as some may think, also it is not easy to maintain specially with new applications where schema changes are possible. However, this is a common way to perform reporting. It provides very fast response for queries because of the the Star Schema and batch aggregations. If you have large data, you may consider a dedicated server for this solution.

I don't know how your schema looks like or what kind of reports you are after, or how much data is there, but strive for having 1 database whenever feasible its much less troublesome.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.