November 5, 2014 at 9:35 am
Hi there,
My team is studying methods for faster patching ways in SQL Server (2008 R2, Enterprise Edition). To be more specific, we need that one or two databases come available before any other in that instance, so, we can reach a partial availability while upgrade occurs.
We had this idea before upgrading the server (Scenario: Two-node cluster with disk quorum);
1 - Move all instances in node A for node B;
2 - Apply service pack in node A;
3 - Put the user databases offline before jump to step 4;
4 - Move all instances from node B to node A.
5 - Wait for Script Upgrade mode in node A. The reason to put all users databases offline is to allow SQL Server finish upgrade script mode more faster ;
6 - Put databases online gradually (in order of business importance) and run sp_vupgrade_replication ;
As far I know:
- A SQL Server upgrade does an upgrade in every database (system catalogs, etc) using the order of database_id (sys.databases), so put them offline allows me to bring a database online more faster without consider your order...
- When a database is offline, an errorlog message says: Could not open database %s. Replication settings and system objects could not be upgraded. If the database is used for replication, run sp_vupgrade_replication in the master database when the database is available run sp_vupgrade_replication
Can anybody help me...Is there any problem in this strategy (colateral effects, corruption risk for instance,etc)? We've already tested it btw and it worked fine, but we are not sure about the cons...
Just for remember: Our needs is take some bases online faster without wait the entire upgrade time.
Thanks in advance.
November 5, 2014 at 1:06 pm
how many databases in these instances? script upgrade usually runs in a couple of minutes. I have patched instances with about 300 databases with acceptable recovery times.
Maybe your log files have excessive VLFs and this is affecting restart times. how long does the recovery stage take on normal restarts?
---------------------------------------------------------------------
November 6, 2014 at 4:19 am
50 for each instance.
The recovery time in normal restarts is 1 minute at maximum.
Our average duration for Script Upgrade mode until I remember was around 2 hours.
November 6, 2014 at 4:32 am
Hmmm, I have never had script upgrade take anywhere near that. Sounds like that is your real problem and anything else than a fix for that is just a workaround.
I'm afraid I cannot say why your script upgrade is taking so long.
---------------------------------------------------------------------
November 6, 2014 at 5:16 am
We're trying to understand everything the Upgrade Script makes, including what DBCC's it uses.
Thank you anyway.
November 19, 2014 at 11:31 am
I agree.. that is very strange. Once you fail over SQL Server to the newly upgraded Node the upgrade stuff runs in about 2-3 minutes and you are good to go.
December 8, 2014 at 6:44 am
Hi,
We have recently upgraded our production servers with SP3 and discovered the possible reason of long upgrade.
Our instances are using CDC enabled. CDC uses some objects used by Replication and the principal "player" of all upgrade process is the procedure called sp_vupgrade_replication.
So, a piece of code of this procedure calls another procedure,sys.sp_cdc_vupgrade_databases.
This second makes a rebuild of ALL tables in the cdc schema and our tables are very large ones.
The steps:
1) When the failback occurs, the upgrade script mode starts.
2) Some work is done by upgrade, and the principal one is [sp_vupgrade_replication].
3) The ERRORLOG show for us:
Log Date ProcessInfo Text
------------ ------------- -----------------
2014-12-07 00:01:58.910spid9sExecuting sp_vupgrade_replication
2014-12-07 03:33:38.030spid9ssp_vupgrade_replication executed sucessfully
1) Modify date from "sys.objects where name = 'change_tables'" shows the schema alteration (and it's compatible with SUM period).
2) Check and "order" the modfy date of indexes.
3) Compare the date of events.
Sounds like a hidden behavior of Script Upgrade mode (drop and create indexes) that occurs in SQL Server 2008R2.
There is a KB here about a correction made in MSSQL2012 about this matter:
http://support.microsoft.com/kb/3013551
[]'s
December 8, 2014 at 7:09 am
thanks for posting back
---------------------------------------------------------------------
December 8, 2014 at 9:17 am
I'm glad to came back into this thread, so, you're welcome and I'd like to thank you all for your impressions.
It's a cool subject because CDC content isn't very wide and some problems (in this case, is most a behavior than problem in my initial point of view) should be reported for the community.
In the future, we want to simulate the problem in DEV using 2008 R2 and 2012 with SP1 to understand better the problem and measure the gains.
Cause the info above is a result based from findings and it's not MS procedural, I don't know if I should finish this thread. 😉
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply