MS SQL 2008 server - system update recommendation
Hi SQL Admins
I would like to ask you for professional recommendation and suggestions regards new MS SQL server setup.
My plan is to upgrade entire SQL system so it would be reliable and would have Disaster recovery setup.
Let me explain what kind of setup I have.
The previous SQL admin had setup system with 1 standalone SQL server (2008r2)\w attached database and 1 warm server.
SQL DB have at least 50 instance running at the moment. The performance is not so good.
4000 employees is using this system nonstop.
What would be the best setup that would provide server and DB great performance,
What would be solution for disaster recover capability (failover)?
What kind of storage system should we use SAN, VM-DB etc?
Also, is it ok to run SQL on VM server (disadvantage and advantage)?
Moreover, I need to also upgrade server that is hosting internal page. Currently, I have one Windows 2008r2 (standalone) no DR.
What would be yours recommendation for this sever, too?
I was thinking to setup 2 windows servers with MS IIS Load Balancer?
Please assist with this question\answers.
M
I would like to ask you for professional recommendation and suggestions regards new MS SQL server setup.
My plan is to upgrade entire SQL system so it would be reliable and would have Disaster recovery setup.
Let me explain what kind of setup I have.
The previous SQL admin had setup system with 1 standalone SQL server (2008r2)\w attached database and 1 warm server.
SQL DB have at least 50 instance running at the moment. The performance is not so good.
4000 employees is using this system nonstop.
What would be the best setup that would provide server and DB great performance,
What would be solution for disaster recover capability (failover)?
What kind of storage system should we use SAN, VM-DB etc?
Also, is it ok to run SQL on VM server (disadvantage and advantage)?
Moreover, I need to also upgrade server that is hosting internal page. Currently, I have one Windows 2008r2 (standalone) no DR.
What would be yours recommendation for this sever, too?
I was thinking to setup 2 windows servers with MS IIS Load Balancer?
Please assist with this question\answers.
M
Besides the above explanations needed do you have another data center in another location? If not then you can't have a Disaster Recovery (DR) solution but an High Availability (HA) only.
What are the RPO and RTO for the DR?
And which SQL Server version and editions do you have?
What are the RPO and RTO for the DR?
And which SQL Server version and editions do you have?
ASKER
Warm server is a DR server that seats in different data center. So, if our PROD server is down, IT team would have to restore DB from backups.
Regads to instances. I ment 50 DBases on one SQL 2008r2 server.
Yes, we do have few data centers.
We are using using MS SQL 2008r2.
RTO - current DR is design very bad. It takes few hours to restore DBs. 4 hr to restore DBs from tapes or virtual storage.
This is very slow, 1hr would be peefer .
RPO - the service would be up and running with in 8 hrs. UNACCEPTABLE . WE NEED SERVICE RESTORE SIMILES AND FAST WITH VERY MINIMUM DOWNTIME.
Regads to instances. I ment 50 DBases on one SQL 2008r2 server.
Yes, we do have few data centers.
We are using using MS SQL 2008r2.
RTO - current DR is design very bad. It takes few hours to restore DBs. 4 hr to restore DBs from tapes or virtual storage.
This is very slow, 1hr would be peefer .
RPO - the service would be up and running with in 8 hrs. UNACCEPTABLE . WE NEED SERVICE RESTORE SIMILES AND FAST WITH VERY MINIMUM DOWNTIME.
ASKER
Warm server purpose - standalone server that is used as secondary server for PRODUCTION SQL Server.
So, if PROD is down. IT would restore DBs from tape or other storage location.
WARM SERVER - has MS SQL 2008r2 installed but stopped, disable.
I need better DR setup and plan to improve recovery time.
So, if PROD is down. IT would restore DBs from tape or other storage location.
WARM SERVER - has MS SQL 2008r2 installed but stopped, disable.
I need better DR setup and plan to improve recovery time.
Hi Michalek19
you did not tell about the DB sizes so to offer some hw recommendation will be unaccurate.
The restore available in 1 hour is almost utopia in the tape or similar backup but again depends on DB size.
You should rather setup database mirroring which allows to switch the server in 15 minutes and the restore speed is then not so critical (yes, even when mirroring you must backup your data).
4000 users is good to know but how many transactions are they generating in one hour/minute/second? How much data are your users generating?
Load balancing and more web servers is good to design if you know the one existing web server is overloaded. Do you have some measurements? Of course, web server(s) should not share CPU with database server.
The database placed on virtual machine is a bottle neck you should use SAN dedicated to your databases. Also SQL Server is better to run on the hardware directly, no VM.
You could also think about Windows Azure. It is virtual environment but almost without limits... The advantage is high availability and scalability. You don't need to bother with mirroring and backups. More users means just to add some CPU cores and RAM to your virtual machine. Price is a big disadvantage, of course.
you did not tell about the DB sizes so to offer some hw recommendation will be unaccurate.
The restore available in 1 hour is almost utopia in the tape or similar backup but again depends on DB size.
You should rather setup database mirroring which allows to switch the server in 15 minutes and the restore speed is then not so critical (yes, even when mirroring you must backup your data).
4000 users is good to know but how many transactions are they generating in one hour/minute/second? How much data are your users generating?
Load balancing and more web servers is good to design if you know the one existing web server is overloaded. Do you have some measurements? Of course, web server(s) should not share CPU with database server.
The database placed on virtual machine is a bottle neck you should use SAN dedicated to your databases. Also SQL Server is better to run on the hardware directly, no VM.
You could also think about Windows Azure. It is virtual environment but almost without limits... The advantage is high availability and scalability. You don't need to bother with mirroring and backups. More users means just to add some CPU cores and RAM to your virtual machine. Price is a big disadvantage, of course.
With SQL Server 2008R2 I can only see two native features for DR:
Besides the native MSSQL options you can try any storage replication feature. It will depend on your storage solution.
Besides the native MSSQL options you can try any storage replication feature. It will depend on your storage solution.
Of course, I don't expect you'll stay with 2008R2 because the main stream support period has ended already (https://blogs.msdn.microsoft.com/sqlreleaseservices/end-of-mainstream-support-for-sql-server-2008-and-sql-server-2008-r2/)
You should think about SQL Server 2016 because it offers many new cool features.
You should think about SQL Server 2016 because it offers many new cool features.
ASKER
Is there any query that I can run to generate this information ?
How many transactions are they generating in one hour/minute/second?
How much data are your users generating?
How many transactions are they generating in one hour/minute/second?
How much data are your users generating?
How much data are users generating:
Check the db file sizes in given time period and you'll see the difference. The longer period the better result (one month is OK).
Of course, you may simulate some comparable conditions, e.g. Do the backup, then shrink the database, and then record the size.
How many transactions?
The sample code is e.g. here: https://wateroxconsulting.com/archives/average-transactions-per-day/
This is not related to the question but it could be useful for you: http://www.databasejournal.com/features/mssql/article.php/3923371/Top-10-Transact-SQL-Statements-a-SQL-Server-DBA-Should-Know.htm
Check the db file sizes in given time period and you'll see the difference. The longer period the better result (one month is OK).
Of course, you may simulate some comparable conditions, e.g. Do the backup, then shrink the database, and then record the size.
How many transactions?
The sample code is e.g. here: https://wateroxconsulting.com/archives/average-transactions-per-day/
This is not related to the question but it could be useful for you: http://www.databasejournal.com/features/mssql/article.php/3923371/Top-10-Transact-SQL-Statements-a-SQL-Server-DBA-Should-Know.htm
ASKER
Pcelba wrote "Load balancing and more web servers is good to design if you know the one existing web server is overloaded. Do you have some measurements?"
How do I measure IIS performance?
What I should look for to determined what kind of server do I need for IIS?
How do I measure IIS performance?
What I should look for to determined what kind of server do I need for IIS?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Can i use performer counters to generate data
Data on your SQL Server? Most probably not.
But you may generate SQL data in tools used for QA testing obviously. Of course, to generate or create the QA tool setup for e.g. initial data load is a nightmare or a lot of work at least...
But you may generate SQL data in tools used for QA testing obviously. Of course, to generate or create the QA tool setup for e.g. initial data load is a nightmare or a lot of work at least...
warm server? You mean some kind of DR/High availability setup? Please tell us what the warm server is doing and we may figure out what do you mean.
the word instance is sometime confusing. you mean there are 50 database servers running virtuallt from the same physical box, or 50 databases in a SQL server.... ?