Monday, December 16, 2013

SQL Server 2012 AlwaysOn Presentation Given at Vermont PASS SQL Server User Group Last Week

This past Wednesday evening, 11th Dec, I braved the snowy roads down from Montreal to Winooski (Burlington area) to join a very friendly crowd at MyWebGrocer and presented all I know about AlwaysOn for the Vermont Professional Association for SQL Server, run by Roman Rehak.
I shall be placing an AlwaysOn script shortly, once I have cleaned up the code - however for those who were there, Roman Rehak was provided with all the files to redistribute also.
If the presentation link is blocked for you, please try this one on LinkedIn's server (see right after summary).
Thanks again to Roman and especially My Web Grocer for sharing its amazing work space with us.

Wednesday, December 11, 2013

SQL Server Installation Folder Setup Log and Command Line Install Information

The other morning I was commenting during our regular meetings amongst fellow DBAs on where to read up on installation issues, or for simple text validation of what components were added during an installation process. This is the folder I want to point out: \\rootDrive:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\Log

Every install action, whether an Add of a node, settings validation, repair, remove, uninstall appears in this folder, and as soon as you have installation issues, go right to the specific Detail.txt under the Log folder corresponding to the date and time an installation was executed. You will see that Summary.txt has very little information, which is self-evident by its name.

For those of you who have a solid state drive at home (desktop / laptop, etc), to become familiar with how SQL Server is installed, I recommend a download of the SQL 2012 Developer edition ISO and to familiarize oneself with the command line installation - and if we could compare total install times (it's ll in the logs, no need for the stop watch), versus a GUI install that would be cool :) to share.  
For those who are curious regarding command line installs, here are some installation examples. Note that now you can mount your ISO natively in Win 2012/Windows 8 and run this comment directly from the mounted drive letter. I have avoided the use of /QS below because I like to see the GUI install to validate the parameters for a second time, just to ensure the instance starts off on the right foot.
CMD line install and one for an non-clustered with Analysis Services:
setup.exe /ACTION="Install" /AGTSVCPASSWORD="19CharacterPassword" /ASSVCPASSWORD="19CharacterPassword" /SAPWD="19CharacterPassword" /SQLSVCPASSWORD="19CharacterPassword" /INDICATEPROGRESS="true" /ENU="True" /UpdateEnabled="TRUE" /UpdateSource="Drive:\FOLDERCONTAININGLatestUpdate/FEATURES=SQLENGINE,REPLICATION,FULLTEXT,DQ,AS,RS /HELP="False" /INDICATEPROGRESS="TRUE" /X86="False" /INSTALLSHAREDDIR="C:\Program Files\Microsoft SQL Server" /INSTALLSHAREDWOWDIR="C:\Program Files (x86)\Microsoft SQL Server" /INSTANCENAME="InstanceName" /INSTANCEID="InstanceName" /ERRORREPORTING="True" /INSTANCEDIR="C:\Program Files\Microsoft SQL Server" /AGTSVCACCOUNT="InstanceSpecificServiceAccountName" /ASSVCACCOUNT="InstanceSpecificServiceAccountName" /ASSVCSTARTUPTYPE="Automatic" /ASCOLLATION="Latin1_General_CI_AS" /ASDATADIR="DriveName:1\olapdb_InstanceName" /ASLOGDIR="DriveName:\olaplog_InstanceName" /ASBACKUPDIR="DriveName:\olapbakup_InstanceName" /ASTEMPDIR="DriveName:\\olaptmp_VInstanceName" /ASCONFIGDIR="DriveName:\OLAP\Config" /ASPROVIDERMSOLAP="1" /ASSYSADMINACCOUNTS="ListOfUsers" "ADDINSTANCESPECIFICCCOUNT" /ASSERVERMODE="MULTIDIMENSIONAL" /FILESTREAMLEVEL="0" /SQLCOLLATION="Latin1_General_CI_AS" /SQLSVCACCOUNT="InstanceSpecificServiceAccountName" /SQLSYSADMINACCOUNTS="InstanceSpecificServiceAccountName" "ADDINSTANCESPECIFICCCOUNT" /SECURITYMODE="SQL" /INSTALLSQLDATADIR="DriveName:\sqlsysdb_InstanceName" /SQLBACKUPDIR="DriveName:\sqlbakup_InstanceName" /SQLUSERDBDIR="DriveName:\mpdbs001\sqlappdb_InstanceName" /SQLUSERDBLOGDIR="DriveName:\sqlapplog_InstanceName" /SQLTEMPDBDIR="DriveName:\sqltmpdb_InstanceName" /RSSVCACCOUNT="NT Service\ReportServer$InstanceName" /RSSVCSTARTUPTYPE="Automatic" /FTSVCACCOUNT="NT Service\MSSQLFDLauncher$InstanceName"

And a command line instance Repair:
setup.exe /QS /ACTION="repair" /ENU="True" /INSTANCENAME="NAME" /ASSVCACCOUNT="19CharacterPassword" /ASSVCPASSWORD="19CharacterPassword" /SAPWD="19CharacterPassword" /SQLSVCPASSWORD="19CharacterPassword"

For the most part, the cluster installation is exactly the same as the standalone SQL Server installation with the exception of a few screens in the GUI, and I would not recommend a Failover Cluster installation from the CMD prompt since you miss all the steps of whether parameters are valid for installation - unless you run CMD without the /QS parameter, which means an attended installation launched from the command link. I find this is a faster way of feeding the GUI installation procedure, and validating as you go along that the parameters actually work within the installation procedure before clicking Next (or equivalent) on each step.

Adding a node, however, is straightforward unattended and a real time-saver, NB
 when you add a node, you must provide again the passwords for service accounts.

setup.exe /ACTION="AddNode" /AGTSVCPASSWORD="StrongPassword" /SQLSVCPASSWORD="StrongPassword" /INDICATEPROGRESS="true" /ENU="True" /UpdateEnabled="False" /UpdateSource="Drive:\FOLDERCONTAININGLatestUpdate" /HELP="False" /INDICATEPROGRESS="TRUE" /X86="False" /INSTANCENAME="InstanceName" /FAILOVERCLUSTERGROUP="ClusterRoleName" /FAILOVERCLUSTERIPADDRESSES="IPv4;;Public;" /FAILOVERCLUSTERNETWORKNAME="SQLVirtualClusterName" /CONFIRMIPDEPENDENCYCHANGE=1 /AGTSVCACCOUNT="domain\InstanceSpecificServiceAccount" /SQLSVCACCOUNT="domain\InstanceSpecificServiceAccount"

---this one is when you have to add AS also on the second node

Using a Configuration file to add a second node to a cluster:
setup.exe /qs /ACTION="AddNode" /CONFIGURATIONFILE=”DRIVEONOTHERNODE:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\ConfigurationFileINSTANCENAME.ini” /AGTSVCPASSWORD=”15CharacterPassword" /ASSVCPASSWORD=”15CharacterPassword" /SQLSVCPASSWORD=”15CharacterPassword" /INDICATEPROGRESS="TRUE"

Changing Database Server Collation:, err, if you set it wrong by accident (works exclusively for standalone from my experience):
Setup /QS /ACTION=REBUILDDATABASE /INSTANCENAME="InstanceName" /INDICATEPROGRESS="TRUE" /SQLSYSADMINACCOUNTS="ML\oth_mlsqldbms" "listOfAccounts" "domain\userGroup" /SAPWD="StrongPassword" /SQLCOLLATION=SQL_Latin1_General_CP1_CI_AS

References (for all the other options):