Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Single user mode during upgrade to SQL 2008? Expand / Collapse
Author
Message
Posted Saturday, September 08, 2012 12:19 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 11:56 PM
Points: 1,015, Visits: 2,840

IN place method for the database upgrade SQL 2000 to 2008,

Can I go for below one of the steps?

first database brings to single user in SQL 2000 database, then start upgrade installation on sql server 2008. once completed installation then apply SP3, finally database brings to multi_user mode
Post #1356340
Posted Sunday, September 09, 2012 8:22 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 9:25 AM
Points: 7,070, Visits: 12,523
I am not sure what that would do to the installer. If the installer tried to open multiple connections it would fail, and it may in fact change the mode itself during the upgrade process. Not sure about 2000 but 2005 and the newer will deny connections during critical portions of an upgrade stating they are in 'administrative mode' or something like that.

Is your goal simply to guarantee users cannot connect to the database instance during the upgrade? If so then if you only have network clients to worry about then consider disabling all protocols except Shared Memory to essentially take the database instance off the network before doing the upgrades.


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1356483
Posted Sunday, September 09, 2012 10:43 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 3:31 PM
Points: 4,244, Visits: 3,321
I would uninstall and do a clean install. I have seen too many problems with upgrades, especially if a between-version is skipped.
Post #1356496
Posted Sunday, September 09, 2012 10:59 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:25 AM
Points: 35,959, Visits: 30,253
ananda.murugesan (9/8/2012)

IN place method for the database upgrade SQL 2000 to 2008,

Can I go for below one of the steps?

first database brings to single user in SQL 2000 database, then start upgrade installation on sql server 2008. once completed installation then apply SP3, finally database brings to multi_user mode


No... Don't use the single user connection mode for this because if you lose connection for even a second, a web service will grab the connection and you'll be out of luck.

The proper way to do this is to make sure that all of the web services pointing to the server have been cleanly shut down.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1356555
Posted Monday, September 10, 2012 1:13 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 11:56 PM
Points: 1,015, Visits: 2,840
Thanks for your replay...

Mr. Jeff comments is good method stoping IIS services instead of using single user mode in database side.

I have done migration activity last weekend and application aslo working fine with SQL SERVER 2008, but i am did wrong method by used single user mode at databse side during IN PLACE upgrade action.

I should take care next time if any server migration action.
Post #1356579
Posted Tuesday, September 11, 2012 12:22 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 11:51 AM
Points: 5,958, Visits: 12,839
you could turn on your firewall and block incoming traffic to the port sql server is listening on

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs"
Post #1357629
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse