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 8, 2012 12:19 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, September 12, 2014 2:40 PM
Points: 1,058, Visits: 3,008

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 9, 2012 8:22 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:19 PM
Points: 7,127, Visits: 12,655
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
Post #1356483
Posted Sunday, September 9, 2012 10:43 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 11:54 AM
Points: 4,427, Visits: 3,423
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 9, 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 @ 12:03 AM
Points: 37,065, Visits: 31,628
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."

(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: Friday, September 12, 2014 2:40 PM
Points: 1,058, Visits: 3,008
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: Today @ 4:51 AM
Points: 6,350, Visits: 13,674
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