SQL Server Installation and Upgrade

1.      Which system can install SQL Server 2012?
a.      SQL Server 2012 Enterprise - Operating Systems: Windows Server 2008 SP2+
b.      SQL Server 2012 Standard - Operating Systems: Windows Server 2008 SP2+ & Windows 7 SP1 + Vista SP2
2.      Installation Process
a.      Setup.exe is written in native code, not depending on other components
b.      Setup.exe will check if there are other installations on the server, if yes and the cached version is equal or greater, the installation will use the cached setup.exe to support slipstream.
c.      ScenarioEngine.exe is used to check Global Rule and new patches. It is written in C#.
d.      If there are errors, check the installation logs (search for “error” and “exception” in the summary and detail log files) and then Windows log.
3.      Installation on a Standalone Machine
a.      Choose the right collation
                                          i.     It’s possible to change after installation
1.      Backup all databases
2.      Backup logins and jobs
a.      SSMS|Security|Logins|Script Login as|CREATE To
b.      SQL Server Agent|Jobs|Script Job as|CREATE To
3.      Detach all user databases
4.      Install with a new installation
                                         ii.     But as the system database, everything in the master and msdb is gone, they need to be re-created.
b.      Do not install on domain controller, if you have to, use a domain account as no local or network service account
c.      Slipstream for 2008+ Product Update replaces SlipStream in 2012 – Microsoft Update or Windows Server Update Services scan the update (remotely and locally)
d.      Installation Methods: GUI, cmd with parameters, cmd with configuration .ini file (setup.exe /ConfigurationFile = ConfigurationFile.ini)
e.      Installation Errors
                                           i.     The OS is English, SQL Server is Chinese – change the OS region and language to Chinese
                                         ii.     SQL Server setup hang - SQL Server 2008/R2 setup hangs on a x64 system with more than 32 logical processors (http://support2.microsoft.com/kb/2276255 and http://support2.microsoft.com/kb/2251397, http://support2.microsoft.com/kb/2276255)  - msconfig|Boot|Advanced Options|set Number of Processors to 1, then install, set it back after installation.
                                        iii.     If user profile causes the error, delete the folder c:\users\theServiceAccount
                                        iv.     WMI error? – test it: Run wbemtest. If not WMI error, need to uninstall SQL Server completely
                                         v.     MSI or MSP is missing when install a patch – Solution: see How to restore the missing Windows Installer cache files and resolve problems that occur during a SQL Server update (see http://support2.microsoft.com/kb/969052)
                                        vi.     Summary: starting with the installation summary and detail reports.
                                       vii.     Windows Small Business Server 2008 Premium Edition includes 32-bit and 64-bit versions of Microsoft® SQL Server® 2008 Standard Edition for Small Business, which enables you to run line-of-business applications in the Windows SBS 2008 network. SQL Server 2008 Standard Edition for Small Business Server can be installed only in the Windows SBS 2008 network. It is not the same as the regular SQL Server 2008 Standard Edition. If there are errors, you can further diagnose them with the following method:
The requirements to install SQL 2008 Standard Edition for Small Business are documented on this post:
Under certain circumstances, you may encounter the block even though all the requirements seem to be met, here is a checklist of things to verify:
Make sure you are logged in as a domain administrator on the server where you are attempting the install. The SQL setup must be run while logged in to the domain with valid
domain credentials. The user needs to have domain administrator and local administrator rights.
Confirm the domain environment health, the verification process needs to be able to talk to all your domain controllers.
If using SBS, do not use the SBS console to count the number of computers and users, you will have to rely on what is showing in the native Active Directory Users and Computers mmc.
You can also enable verbose logging to get detailed output of which test is failing so you can take corrective action.
To enable verbose logging perform the following steps:
After you have experienced the block the first time, close the SQL setup.
Open notepad as an administrator and save the following contents:

<?xml version="1.0" encoding="utf-8" ?> 
<trace autoflush="true" /> 
<source name="SBSADCheck" switchType="System.Diagnostics.SourceSwitch" 
<add name="myListener" type="System.Diagnostics.TextWriterTraceListener" initializeData="log.txt" /> 
Save the file in %ProgramFiles%\Microsoft SQL Server\100\Setup Bootstrap\Release\x64\Setup100.exe.config (if you are installing SQL Server 2008 R2 for Small Business the path will be: %programfiles%\microsoft sql server\100\setup bootstrap\sqlserver2008r2\x64\Setup100.exe.config)
Launch the SQL setup and run through to the block.
Collect the Verbose log from %ProgramFiles%\Microsoft SQL Server\100\Setup Bootstrap\Release\x64\log.txt or %programfiles%\microsoft sql server\100\setup bootstrap\sqlserver2008r2\x64\log.txt
4.      Installation and Upgrade in Cluster
a.      SQL Server 2005 can run installation simultaneously on all nodesbut SQL 2008+, starts with the main node, then one by one.
b.      Some services must start
                                           i.     Remote registry
                                         ii.     Task scheduler
                                        iii.     Cryptographic
                                        iv.     Server
                                         v.     Cluster
c.       All disks in the cluster must be online, no matter whether they are used by SQL Server installation – use “Cluster res” to check the status.
d.      Network – two nodes can ping NETBIOS, FQDN
e.      Shared files – two nodes can visit the shared files
f.       Cluster Network Name needs to have privilege to create SQL Server Network Name
g.      Common errors
                                           i.     Not all disks are online
                                         ii.     SQL Network Name (xxx): Unable to create computer account: Access is denied.
                                        iii.     Different versions of SQL Server 2005 on the two nodes (SQL Server 2008+ does not have the problem as we can upgrade the node with the lower version directly as it requires does not simultaneous installation)
                                        iv.     Microsoft Cluster Service (MSCS) cluster verification errors (see http://support2.microsoft.com/kb/953748)
                                         v.     Summary
1.      Collect installation logs from all nodes
2.      Determine the nodes with errors
3.      Check the detailed errors from the installation log on the nodes
4.      Identity the cause of the errors
5.      Google if necessary
5.      Upgrade
a.      Different from install patches, for patch installation, we can uninstall them (for SQL 2008+ only), upgrade cannot be uninstalled after installation
b.      Back up first before upgrade!
c.       Then Evaluations
                                           i.     Any big differences between the old and new versions make application no long working? – use Upgrade Advisor and then input a trace or a batch file to evaluate if there are any compatibility issues after upgrade.
                                         ii.     Stable on the old version, but decrease in the new version? – test! Re-design the query/table/database if necessary
                                        iii.     Any risks for online upgrade, if problems occur, is it possible to rollback? – No, you need to back up all of the databases before upgrade
                                        iv.     What is the upgrade sequence for DB mirroring, replication, log shipping, and cluster?
1.      DB Mirroring

2.      Replication
a.      Secondary server first, then primary, otherwise, the backup from the primary cannot be restored to secondary.
3.      Log Shipping
a.      Distributor's version is higher than the Publisher's
b.      Publisher and subscribers can have different versions
c.       Make sure all of the publications are replicated to distributors (sp_reolcmds should be empty)
d.      Close the connections with sp_replflush
e.      Upgrade
f.       For merge replications, the version of the subscribers should be lower than that of the publisher
4.      Cluster
a.      All disks must be online
b.      Upgrade the passive node first
c.       Switch to the upgraded node
d.      Upgrade other nodes
e.      Finally switch back to the active node
                                         v.     What to do after upgrade?
1.      Make sure the compatibility level is a new version now
2.      Rebuild indexes on the databases
3.      Upgrade statistics