We have an application developed in asp.net. We sell this application as a white label to insurance companies.
We launched this application about a month ago, and we already have about 12,000 accounts. There are several web servers (IIS) for the different white labels, and a big database server that supports all white labels.
We need to have everything on one database for reporting purposes.
We now got a new white label that wants to bring about 500,000 users, so I freaked out. My question is this, we handle prety well the web servers by adding a new server when it is necessary, but what about the SQL Server?
The database has about 140 tables, one of the tables grows at a rate of 1,000+ records per day with only 12K users, and Im scared cause I dont know how will I handle this in the future. Someone told me there's a way to split the tables among several SQL Servers, but Changing the SQL scripts on about 400 pages freaks me out.
What are the most best solutions or scenarios to handle this? I have a lab/test environment where I can start testing the solutions.