Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 3 down vote favorite

I am currently developing a project with a huge amount of database transactions.

My estimate is around 70,000 inserts/updates/deletes/selects every 2 minutes, since I am not there yet, I can only estimate, but in the near future, it'll get there.

Since I am not a DBA, I'd like to hear your opinion.

I am running on a Windows Server 2008 R2 and I thought I can use SQL Server 2008 R2, the questions is, how many processors and what type exactly of SQL Server I need to use? there are plenty like Enterprise, Standard, Web Edition, etc.

Any suggestions folks?

share|improve this question
3  
You need to provide more information. 35k transactions a minute could mean a lot of things, since you dont specify how wide your tables are, how complicated the relationships are, if you are reading/writing to the same tables, if there will be archiving of old data or partitioning, if you will have replication, etc – JNK♦ Jan 20 '12 at 16:54
70k transactions every 2 minutes is ~583 transactions per second. That isn't much load at all. – mrdenny♦ Jan 20 '12 at 20:18
Do you guys think SQL Server 2008 R2 Web Edition 2 Processors would be enough to handle it? I really have a budget problem I guess... – Ido Jan 21 '12 at 9:18

4 Answers

up vote 3 down vote

It is difficult to estimate server load based on "number of transactions" because cost of a transaction can vary greatly depending on the design of your DB and the nature of the query. Enterprise and Datacenter editions are your versions to consider for situations with high volume and high scalability. Microsoft has comparison charts showing what the processor/memory limitations are for each, extra features, and virtualization restrictions. (Search: SQL Server version comparison)

What type of application is this supporting and for what business type? Will it be performing strictly transactional OLTP tasks or are you looking to support reporting and analysis with (a separate instance of) the same product?

share|improve this answer
up vote 2 down vote

70,000 per 2 minutes isn't much: you can do that per second easily in SQL Server.

Saying that, the question is otherwise too wide. The choice of edition doesn't really apply: your design and hardware setup matter more. Standard or Web should be enough.

If you budget extends to a single RAID 5 volume, for example, then forget scaling up. Or you let nHibernate design your schema.

share|improve this answer
Windows 2008 Server R2 Quad Core 2.5Ghz with 16GB Ram including SQL Server 2008 R2 Web Edition would be enough in your opinion? All the transactions should be on 3 tables, database should contain around 25 tables, but out of this table, 1 will face around 70% out of all queries. What do you think? – Ido Jan 20 '12 at 19:06
up vote 1 down vote

I presume from your question, you are asking if it is feasible to move a database off of its current platform, to one on Windows Server 2008 R2 running SQL Server 2008 R2. In addtion to those items raised by @jnk and @hrsie you will want to give some detailed hardware information as well, especially where your disk sub-system is concerned (attached storage or SAN, etc.)

In SQL Server you want to have as much physical separation as possbile with separate drives for the OS, SQL software, Data (mdf), log (ldf), tempdb, OS pagefile.

share|improve this answer
Hi, my server is Quad Core 2.5Ghz with 16GB Ram DDR3 and 2 TB HDD running Windows Server 2008 R2, I just wondered what type of SQL Server I need in order to make it work fast enough. – Ido Jan 20 '12 at 19:38
Two large internal drives would make me cautious. I would download SQLIO microsoft.com/download/en/… and run it to try determine how your internal drives would handle things. Two drives means OS, SQL executables, tempdb, pagefile & maybe mdf on C:\, log on D:\. Doesn't look good. – jl01 Jan 20 '12 at 22:23
up vote 0 down vote

Agreed. Many thinks to consider , not just the database transactions. The infrastructure will play a role as you may consider:

  • RAID 1 configuration (separate volume) for tempdb
  • RAID 1 disks for the transaction logs
  • RAID 5 or 1+0 (although this requires more disk) for the data files.

As for memory the more memory , "the better". I personnaly use 16Gb as a standard for the new servers running SQL Server 2008 R2. Processors usually 2 processors (8 cores), as we mostly use SQL Server Standard with per processor licence. If I understand your requirements you would probably need at least a standard edition. The type of licence (and cost) will depend on on may users will connect to the server.

Another thing to consider as you seem to want to both read and write is maybe to user separate servers to implement log shipping (1 database to write, "mirrored" to another database to read) or replication.

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.