I need some advice/suggestions on a situation.
Currently we have one instance of SQL Server R2 running on Windows Server 2008 R2 on 12.0 GB of RAM and 24 (2.93 GHz) processors.
Most of the databases have Identity column as the primary key. Lot of the internal applications are tied to this Identity column. We are currently at 10 million rows on the identity column in the table having maximum number of rows.
We are expecting a lot of website traffic in near future. I would say to be on the safer side and as a test case approximately around 20000 users an hour would be present on the website. They would be inserting records into 15 tables concurrently.
How can I increase the scalability of this so that users are served without any interruption ?