July 5, 2011 at 10:32 pm
I have a request for planning out the implementation of the movement of a stand-alone sql instance to SQL Servere cluster.
The Stand-Alone SQL Server currently has 7 databases, with total database size of ~500GB. The Transaction log growth per day is around 25 GB.
1. Could you please suggest what all should be considered while making this change.
2. Should Active/Active or Active/Passive Installation be implemented. What goes behind this decision making.
3. How to minimize the down time while implementing this.
July 5, 2011 at 10:34 pm
More details:
Current version of SQL is SQL Server 2005, Enterprise Edition.
July 6, 2011 at 2:06 am
arvind.1611 (7/5/2011)
The Stand-Alone SQL Server currently has 7 databases, with total database size of ~500GB. The Transaction log growth per day is around 25 GB.
thats the max size the log is hitting i presume?
arvind.1611 (7/5/2011)
2. Should Active/Active or Active/Passive Installation be implemented. What goes behind this decision making.
if it is to be a single instance cluster then it can only be Active\Passive. So, question is how many instances do you plan to deploy?
arvind.1611 (7/5/2011)
3. How to minimize the down time while implementing this.
Build the new cluster and migrate each database and its associated objects (jobs, logins, etc) in turn reconfiguring application connection strings as you go
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 6, 2011 at 3:46 am
Could you please suggest what all should be considered while making this change
Install the SQL Server in Cluster Mode and then crosscheck both servers to move services ,then verify services are running fine or not,Tune the instance,Take all necessary backups
User Databases Full Backup
SQL Server Agent Jobs Scripts if any
Linked Server Scripts if any
User scripts (sql_help_revlogin) (http://support.microsoft.com/kb/246133)
Server Level Triggers backup if any
Should Active/Active or Active/Passive Installation be implemented. What goes behind this decision making.
go to Active/Passive,what do you understand by Active/Active in SQL Server ?
Active/Active means more than 1 sql server instance on more than 1 server in cluster mode
How to minimize the down time while implementing this.
1-Take Full Backup and then restore on the server with norecovery (Downtime not started here)
2-Take Application offline 1 by 1 and then take Differential backup of the offline application database then restore on the server with recovery
3-Route the offline Application to the new Server and then online
Do same for the rest of the applications
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
www.aureus-salah.com
July 7, 2011 at 3:54 am
One option I would consider is "the big bang" migration by logshipping all databases prior to migration.
this will allow the set up of jobs and logins etc.
At migration time, bring all dbs online, make old dbs offline and perform the other jobs.
July 7, 2011 at 10:45 pm
Thanks All for your comments.. I've planned to go with the Active/Passive cluster setup, as there is only one instance running at the moment on the current standalone box.
Gathered some information online on the recommended RAID Configuration for SQL files. i.e. Log On RAID 1, Tempdb on Raid 1, SQL Bits on RAID 5.
To minimize the downtime, I am planning to use transactional replication, to keep the cluster and the standalone boxes in sync, so that the time to switch between the servers doesn't takes too long.. Offcourse doing it an application at a time can further reduce the downtime.
July 8, 2011 at 5:53 am
arvind.1611 (7/7/2011)
I am planning to use transactional replication, to keep the cluster and the standalone boxes in sync,
That's probably the worse way to do this. What if any of the database table objects have no PK defined, you will not be able to replicate them or any objects that depend upon them.
To replicate a complete database, I would use either log shipping or database mirroring.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 8, 2011 at 10:01 am
arvind.1611 (7/8/2011)
@Perry: Then suggest a better way out..
I already have, see above and below
Perry Whittle
To replicate a complete database, I would use either log shipping or database mirroring.
arvind.1611 (7/8/2011)
I guess replication keeps the latency to minimum.
Really? why is that then. How can you have latency issues, from what i can read the stand alone server and cluster will be on the same network, is this not the case? How far apart are the geographic locations?
Log shipping is probably the easiest to setup, maintain and eventually remove!
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 8, 2011 at 10:02 am
thanks mate..
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply