Do we need IT Service High Availability and Disaster Recovery ?

Hello today I am writing a small article on the huge subject of high availability (HA) and Disaster Recovery (DR) and why you should consider them to provide highly available IT services and minimise downtime for your users. 

I’ll cover brief definitions and examples of High Availability (HA) and Disaster Recovery (DR) planning, and explain their differences.  I feel this is important as many people often refer to HA and DR interchangeably and sometimes in the wrong context.

I talk to many clients and help to architect highly available systems, mostly based on Microsoft technologies such as Windows, SharePoint and SQL Server and many common questions and discussions come about regarding HA and DR.  I thought it would be good to get this down into a high level article to help others and for me to reference it when talking about these things with others. 

What is High Availability (HA) ?

In the world of IT infrastructure, a service that is highly available is considered to be resilient from component failure at the site level, where a site would be a data centre.  Resilience here means that the service would have little or no downtime if any one component in the infrastructure hardware or software stack failed.   A component could be defined from anything from a power supply in a server, a motherboard, a hard disk or a network switch at the hardware level. This then bubbles up to the software level in the guise of the operating system and then up to the user application or/and any other software that may be present such as drivers or backup software.

The availability of any service can be made resilient in a number of ways, both at  the hardware & software level.

Here are some examples :-

Component Level Resiliency

image

The above table gives a rough idea on how we can architect some level of high availability.  The application row is very high level as application resiliency depends on how the application is written and if it is cluster aware for example, as some applications can not be added to a cluster effectively.  

An example for SharePoint would be to use two or more servers in what would be termed as a “farm” and use network load balancing (NLB) to distribute network traffic to the most responsive server for example.  For Microsoft SQL Server we could create a cluster as SQL Server is cluster aware. Well the Database engine, Analysis Services and SSIS (to a degree) are.  For Reporting Services we would use the farm scenario like we have in our SharePoint example, with NLB.

So now we have a highly available service in our data centre which is running on highly available hardware with multiple servers being fed from multiple power sources and internet links.  In effect we are protected against any single component failure and we can automatically failover to another physical host, virtual server, power supply, or internet feed for example.  We may want to control this so failover could be manual for some or all components.  This is where monitoring comes in but that’s a subject for another day !

A Highly Available Virtual Host Cluster

In order to provide our virtual machines (VMs) some high availability and protection from physical hardware and potentially power and network link outages we could create a cluster.  The principle of a virtual host cluster is the same in Hyper-V as it is for VMware in that the hosts can support multiple VM’s which can automatically move (fail over) to another host if required.

Here we see a highly available three node Hyper-V cluster which can host a number of virtual machines.

image

The blue machines represent general VMs and the red one represents a mission critical virtual machine that must be kept alive at all times.

The cluster is designed to be highly available in that there are multiple hosts with multiple connections to the various networks required such as heartbeat, Live Migration, iSCSI SAN for storage and the LAN.  Any network switch, network card or port can fail and the system will continue to run.  A host could also fail and any VMs which are marked for high availability would be automatically moved to another host server without any downtime (host capacity allowing).  Assume the iSCSI SAN storage is provided by a highly available storage system with RAID disk configurations to help protect against disk failure.

So we are fully protected against failure of any physical server, network and storage components.

So why do I need to use clustering or NLB when my environment and VM is already highly available ?

The answer to this common question is that there are several good reasons to protect your service further and beyond the physical level mentioned just above.  The short answer will come if you think of a blue screen, OS freeze or maintenance.

These reasons are listed here with a bit more detail:-

Application Level High Availability through NLB or Clustering

image

So are we done ? Our application or service is now highly available and nothing can possibly take down our application…. or can it ?

Well if you have a highly secure solid and resilient data center where power, cooling, access etc. is all good then almost all is well and the chances of service outages are reduced significantly.  However as we live in the real world there CAN be things that can go wrong at the data center level.  This leads us into disaster recovery.

Disaster Recovery

Disaster recovery is required to protect services from a disaster at the data center level.  The process and planning of bringing services back online in the case of a disaster is referred to as Business Continuity.  As in, if your business depends on certain services, will these services be available in the event of a disaster in order for the business to continue to function?   Other factors to consider in business continuity are things like how will users be notified ? If the main offices are no longer accessible where will be people work from and so on.   The most likely cases which would require invocation of a disaster recovery plan are things like natural disasters such as floods, fires or even a malicious attack or a UFO, meteorite or plane crash into the data center.  Some of these things are more likely to happen than others ! Smile 

In the event of a disaster, services would resume from the DR site ideally without too much or any disruption to the users of the services.  There may well be manual system administration tasks to complete in the case of a DR scenario such as IP address configuration, SAN storage and cluster configuration to bring up services.  As technology evolves these manual steps get simpler, the effort required AND recovery time is also reduced.   An example of this is Microsoft’s Hyper-V Replication which is designed for a DR scenario. Hyper-V Replication allows the administrator to configure a separate IP address for the DR site when normally this may have been done by using additional scripts or manual steps.

Other reasons for having DR would be to gain confidence from your clients and also to prove to auditing bodies that your organisation can continue in the event of a disaster.   The benefits of having a GOOD DR process and architecture is that DR can be tested properly without or with minimal disruption (out of hours please !) to consumers of a service.

 

Conclusion

Having a good HA and DR process in place for your mission critical systems provides your business with the confidence to perform value adding activities required for the organisation to be successful.  The outcome of this may be better customer satisfaction, more profit or even saving lives in some cases.   HA and especially DR require proper planning and investment and some example questions to ask when considering a HA and DR strategy are “Why do we need it ?”, “What does it cost us if there is an outage ?”, “Whose responsibility is it if x service is down for x amount of time ?”, “How long can we function without the service?”.

Most importantly I think it is essential to think about “What would happen if we did nothing ?”. For example will you lose more money if the service was down for 1 day than you would save in the implementation of a good HA and DR strategy ?   Would you lose your trading license or lose customers ?

Well it is a very broad topic and I hope I have highlighted some key or interesting points for you to take away.  Please do comment on the post if you found this useful or if there are other important points which could help others.  And thanks for reading. I know it’s a long article this time…

STOP ! Read this SQL Server Pre Deployment Checklist Before….

SQLChecklistThere are many things to check BEFORE implementing an Enterprise or even a medium scale SQL Server based infrastructure.  Below are some key points to consider before deploying SQL Server in a production environment to help prevent a whole lot of pain once a system has been deployed and  is in production.  This post relates to general pre-installation checks rather than the logical and physical architectural decision making process for the implementation Microsoft SQL Server 2005 through to SQL Server 2012 SP1+.

image  Hopefully this article will help some people to avoid learning the hard way !

There are many reasons for following a checklist and ensuring the environment is correct. Examples are recording a snapshot of the current configuration and benchmarking performance. The most important reason to use a checklist and get things right the first time is that it is much simpler to get things right pre-build rather than once the system has gone into production and WHEN problems arise.  Note the missing IF statement here.

 

Pre-Installation Checks

Server Level

 
Firmware Ensure BIOS, RAID controller and HBA firmware is up to date. 
Remove any unnecessary software

If the server is not a fresh build, remove any unnecessary software such as old and unused SQL Server versions or components, any agents that may no longer be required or need updating,

   

Operating System and Server Environment

Ensure environmental aspects are considered such as AD, DNS, server configuration and health etc.
Windows Server Edition

Ensure you have the correct Edition for current and future requirements. E.g. note memory limitations in Windows Standard Edition (32GB). If you have a license to use Enterprise then that maybe a better option to start with.  Only relevant to Windows 2008 and 2008 R2.  Note in Windows 2012 there are NO differences between Standard and Datacenter, only virtualization rights  There is no Windows Server 2012 Enterprise Edition.

Windows patch level

Ensure the server is up to date on patching and that an update strategy is defined and implemented.

Hardware Drivers

Ensure all drivers are up to date especially storage, network and HBA drivers.

Windows OS Regional Settings

Ensure the Region settings of the OS are correct for the country of use. I have seen many installations in the the UK for example, set with the default region as US.  Once SQL is installed with defaults, the collation will be incorrect for the region it is being used in, resulting in incorrect date/time configuration etc..

Consider using DNS A records for the server name

Consider the use of a DNS A record for application access to the SQL Server.  This way if a database, set of databases or a whole instance needs to be moved to a new server it can be done with ease by changing the DNS record in AD without making changes to applications and reports for example.  A good example of this is Reporting Services or SharePoint.   Yes you can use SQL Aliasing too which may require a bit more configuration. e.g. for SharePoint you would have to create an alias on each SharePoint Server. With a DNS alias you change the Alias in one place (DNS!).

Check Group Policies

Check and audit which group policies are being applied to the server to ensure there are no unexpected or unwanted GPOs being applied and vice versa.

Check Event logs for pre existing errors or warnings Ensure event logs are clean. Take special note of the system logs which can highlight potential system and hardware errors or time bombs.
Network Configuration and bandwidth

Is your networking optimally configured ? This includes iSCSI or Fibre Channel links to any SAN that might be in the mix.  Test and benchmark throughput.  Use tools such as CrystalDiskMark or SQLIO. Are you getting expected throughput ? i.e are you hitting 100MB/s max sequential write throughput if you have 2 x 4GB HBA cards ?  Ensure drivers and multi-pathing configuration and software is installed and correct. Note some SANs may not allow Active/Active configuration or may have this as an additional cost option.   If all ok move onto disk storage configuration.

If SQL Service account Permissions. Grant SQL Server permissions to “Perform Volume Maintenance Tasks” to allow for instant file initialisation.  More info here http://msdn.microsoft.com/en-us/library/ms175935(v=sql.105).aspx

Also grant the SQL Server the “Lock Pages In Memory” right – only if required.  More on these options will be covered in later blogs.  There are many blogs around lock pages in memory and whether it should be used or not.
See https://www.simple-talk.com/sql/database-administration/great-sql-server-debates-lock-pages-in-memory/
by Jonathan Kehayias
and
http://blogs.technet.com/b/askperf/archive/2008/03/25/lock-pages-in-memory-do-you-really-need-it.aspx
from the Windows Server Performance Team.  A lot depends on the environment, it is not generally recommended in virtual environments when there is a risk of a host requiring memory to start up VMs in an outage scenario for example.

SQL Service Account Permissions The SQL Server service account does not require any special permissions.   Any permissions required are granted during installation or when changing the account using SQL config manager.  This is the main reason why you should use this tool for changing accounts and NOT the Windows Admin Services console.
   

Storage

Optimal storage configuration is essential for SQL Server to perform well, especially on larger databases.

Disk Configuration

Ensure your disks are formatted correctly using 64K blocks.  The default of 4k will provide poor performance as SQL stores data pages in 8k chunks which fit perfectly into 64k cluster size (or file allocation unit size),  Each 8k block is split if using 4k blocks.  This can and does cause a lot of fragmentation in the database. This simple configuration can help improve IO performance by up to 30%.  If formatting is incorrect, all data must be moved and disks reformatted once in production.  Disk alignment is no longer a requirement unless you are still using Windows 2003.  If you are using Windows 2003, an upgrade is WELL overdue as mainstream support has ended. Also, Windows Server 2003 will not be capable of taking advantage of modern hardware (i.e. major processor advancements since 2003).  Further info here.

RAID Configuration

Are your RAID arrays optimised for SQL Server and expected usage ? There are many articles explaining the benefits of why RAID groups are important. More info can be found here ….Raid Levels and SQL Server.
Of course modern SANs coupled with tiered storage make RAID pretty much redundant now by using storage pools.  This is essentially using a large pool of disks to create LUNS (Logical Unit Numbers) as logical disks. Correct setup and configuration can provide better performance and use less disk space by removing storage silos compared to traditional RAID.  See here for a great blog by Paul Galjan. Disk and File Layout for SQL Server.

Disk Sizing

Are your disks sized appropriately  to accommodate at least medium term growth expectations ? Do you have capacity to add more storage if required easily ?

SAN Configuration and load

Ensure SAN is not already saturated or at capacity, ensure it can deliver reasonable IOPS for your application to perform as required.  Refer to Microsoft Certified Master and MVP Brent Ozar who provides tonnes of excellent SQL Server information in his blogs.

SAN Software and vendor recommendations Ensure you look at the vendors recommendations for SQL Server as they may differ from standard best practices for a particular SAN. 
SAN – HBA Drivers and software Ensure the correct and latest drivers for any HBA cards are installed and configured correctly.  E.g. Ensure Multi-pathing is set up and working correctly.  Some vendors do not allow Active / Active (for max bandwidth) configuration by default, check with vendor.
   

SQL Server Specific

 
SQL Server Version and Edition

Do you have the correctly licensed SQL Server Edition ?  Per core, VM, Standard, BI, Enterprise Edition ?  Ensure latest service pack is applied and license key is correct for use case.

SQL Server Service Accounts

Ensure the service accounts are correctly planned and created in Active Directory prior to the installation. e.g. separate accounts for Reporting Services and Analysis Services for example.  Ensure you have the account details.

SQL Cluster Considerations

If using a Windows Cluster ensure that the storage and resource groups are assigned correctly.  Ensure the quorum method and mode is designed correctly, e.g. disk or file share, node majority options. See the following for Configure and Manage the Quorum in Windows 2012 Clustering
And
Understanding Quorum Configurations in a Failover Cluster
Consider Cluster Aware updating for patching clusters, especially clusters with many nodes. This feature is only available in Windows Server 2012.

Most importantly ensure you thoroughly test and document the failover process.  You do not want to be looking for documentation or ringing support (Panic…Microsoft or Storage Vendor !!?) in the event of a cluster failure during a production outage.  The reason a SQL cluster even exists is to minimise downtime of critical systems.  You would not be popular if you could not show evidence of testing the failover and failback.

Thank you for reading, I will try and follow this up with a post on what to check AFTER the SQL server installation is complete.

Also check out my post on how to complete a scripted install of SQL Server on Windows Server 2012 Core, which will of course also work with the full GUI shell of Windows Server 2012 .   

MIcrosoft_SQL_Badges_SQLGeekMIcrosoft_SQL_Badges_ServerFan2012

Check out the newly released SQL Server community badges above, they are just for fun !

I hope this article is useful to at least a handful of people and spares someone from possible future pain.

Did you find this useful ? Do you have further good checklist tips ? Post a reply to share…

Installing SQL Server 2012 on Windows Server 2012 Core

This post accompanies a video tutorial which is available here http://youtu.be/Tytfq6WXvas


SQL-Server-2012-logo-405x260-390x242

I would like to document and share how to install SQL Server 2012 on a Core installation of Windows 2012. Windows Server Core as I am you may be aware of is a version of Windows Server without the graphical tools and weight of a full Windows Server installation. Some of the reasons for using Server Core are listed here:- 

  • Smaller attack surface as there is less installed and running by default .
  • Less patching and reboots as many services and features such as Internet Explorer, Windows Explorer, start screen are not installed.
  • Smaller disk footprint as the OS takes approximately 4GB as opposed to around 8GB for the full install.

New in Windows Server 2012 is the ability to install the full GUI if required and then remove it again.  This would usually only be required to install certain drivers or applications that must have a GUI.

The installation is the demo video is done using a Windows Server 2012 Core VM which is then configured through sconfig and joined to a domain.  The server already has some installation files and scripts on the C Drive as part of the SYSPREP image it was copied from.

The scripts used in the video are available in this article. SQL Server and Analysis Services are configured to run using Domain Accounts.


The Process

Prepare any code and scripts up front so they are ready for the installation.  Ensure the steps below are followed for a smooth installation.

1. Enable Remote Management, Remote Desktop, check and configure network IP setting

2.  Join the server to the domain and create the required service accounts in Active Directory.

3.  Ensure the Sources\sxs folder from the Windows 2012 installation media is available or copied to the server. Mine is copied to C:\SourcesSxS

4.  Allow SQL Server through firewall script :-

netsh advfirewall firewall add rule name="Open SQL Server Port 1433" dir=in action=allow protocol=TCP localport=1433

5.  Allow Analysis Services through the firewall :-

netsh advfirewall firewall add rule name="SQL Server Analysis Services inbound on TCP 2383" dir=in action=allow protocol=TCP localport=2383 profile=domain

Here are the SQL scripts used in the demo, and the code for each is found below.

image

6. Create or download and customise the ConfigurationFile.ini for SQL Server.

This can be done running through the SQL Server setup full GUI on another server and stopping before the installation part and grabbing the ini file from the default location of :- “C:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\Log”

See this link for full details from Technet:-

http://technet.microsoft.com/en-us/library/dd239405(v=sql.110).aspx

You can then customise this file as you wish and select the components you require, installation type,  service accounts, directory locations.  Note Reporting Services (SSRS) is not supported on Server Core.  However SSIS is.

Here is the ConfigurationFile.ini from my setup for you to compare yours against or just copy this one !

Add SSMS, ADV_SSMS to the Features line to add the management tools.

Code Snippet
  1. ;SQL Server 2012 Configuration File – Mitesh Chauhan 2012
  2. [OPTIONS]
  3.  
  4. ; Specifies a Setup work flow, like INSTALL, UNINSTALL, or UPGRADE. This is a required parameter.
  5.  
  6. ACTION="Install"
  7.  
  8. ; Detailed help for command line argument ENU has not been defined yet.
  9.  
  10. ENU="True"
  11.  
  12. ; Parameter that controls the user interface behavior. Valid values are Normal for the full UI,AutoAdvance for a simplied UI, and EnableUIOnServerCore for bypassing Server Core setup GUI block.
  13.  
  14. ;UIMODE="Normal"
  15.  
  16. ; Setup will not display any user interface.
  17.  
  18. QUIET="False"
  19.  
  20. ; Setup will display progress only, without any user interaction.
  21.  
  22. QUIETSIMPLE="True"
  23.  
  24. ; Specify whether SQL Server Setup should discover and include product updates. The valid values are True and False or 1 and 0. By default SQL Server Setup will include updates that are found.
  25.  
  26. UpdateEnabled="False"
  27.  
  28. ; Specifies features to install, uninstall, or upgrade. The list of top-level features include SQL, AS, RS, IS, MDS, and Tools. The SQL feature will install the Database Engine, Replication, Full-Text, and Data Quality Services (DQS) server. The Tools feature will install Management Tools, Books online components, SQL Server Data Tools, and other shared components.
  29.  
  30. FEATURES=SQLENGINE
  31.  
  32. ; Specify the location where SQL Server Setup will obtain product updates. The valid values are "MU" to search Microsoft Update, a valid folder path, a relative path such as .\MyUpdates or a UNC share. By default SQL Server Setup will search Microsoft Update or a Windows Update service through the Window Server Update Services.
  33.  
  34. UpdateSource="MU"
  35.  
  36. ; Displays the command line parameters usage
  37.  
  38. HELP="False"
  39.  
  40. ; Specifies that the detailed Setup log should be piped to the console.
  41.  
  42. INDICATEPROGRESS="False"
  43.  
  44. ; Specifies that Setup should install into WOW64. This command line argument is not supported on an IA64 or a 32-bit system.
  45.  
  46. X86="False"
  47.  
  48. ; Specify the root installation directory for shared components.  This directory remains unchanged after shared components are already installed.
  49.  
  50. INSTALLSHAREDDIR="C:\Program Files\Microsoft SQL Server"
  51.  
  52. ; Specify the root installation directory for the WOW64 shared components.  This directory remains unchanged after WOW64 shared components are already installed.
  53.  
  54. INSTALLSHAREDWOWDIR="C:\Program Files (x86)\Microsoft SQL Server"
  55.  
  56. ; Specify a default or named instance. MSSQLSERVER is the default instance for non-Express editions and SQLExpress for Express editions. This parameter is required when installing the SQL Server Database Engine (SQL), Analysis Services (AS), or Reporting Services (RS).
  57.  
  58. INSTANCENAME="MSSQLSERVER"
  59.  
  60. ; Specify the Instance ID for the SQL Server features you have specified. SQL Server directory structure, registry structure, and service names will incorporate the instance ID of the SQL Server instance.
  61.  
  62. INSTANCEID="MSSQLSERVER"
  63.  
  64. ; Specify that SQL Server feature usage data can be collected and sent to Microsoft. Specify 1 or True to enable and 0 or False to disable this feature.
  65.  
  66. SQMREPORTING="False"
  67.  
  68. ; RSInputSettings_RSInstallMode_Description
  69.  
  70. RSINSTALLMODE="FilesOnlyMode"
  71.  
  72. ; Specify if errors can be reported to Microsoft to improve future SQL Server releases. Specify 1 or True to enable and 0 or False to disable this feature.
  73.  
  74. ERRORREPORTING="False"
  75.  
  76. ; Specify the installation directory.
  77.  
  78. INSTANCEDIR="C:\Program Files\Microsoft SQL Server"
  79.  
  80. ; Agent account name
  81.  
  82. AGTSVCACCOUNT="Demo\SQLSVC"
  83.  
  84. ; Auto-start service after installation.  
  85.  
  86. AGTSVCSTARTUPTYPE="Manual"
  87.  
  88. ; Startup type for Integration Services.
  89.  
  90. ISSVCSTARTUPTYPE="Automatic"
  91.  
  92. ; Account for Integration Services: Domain\User or system account.
  93.  
  94. ISSVCACCOUNT="NT Service\MsDtsServer110"
  95.  
  96. ; The name of the account that the Analysis Services service runs under.
  97.  
  98. ASSVCACCOUNT="Demo\SSASSVC"
  99.  
  100. ; Controls the service startup type setting after the service has been created.
  101.  
  102. ASSVCSTARTUPTYPE="Automatic"
  103.  
  104. ; The collation to be used by Analysis Services.
  105.  
  106. ASCOLLATION="Latin1_General_CI_AS"
  107.  
  108. ; The location for the Analysis Services data files.
  109.  
  110. ASDATADIR="C:\Program Files\Microsoft SQL Server\MSAS11.MSSQLSERVER\OLAP\Data"
  111.  
  112. ; The location for the Analysis Services log files.
  113.  
  114. ASLOGDIR="C:\Program Files\Microsoft SQL Server\MSAS11.MSSQLSERVER\OLAP\Log"
  115.  
  116. ; The location for the Analysis Services backup files.
  117.  
  118. ASBACKUPDIR="C:\Program Files\Microsoft SQL Server\MSAS11.MSSQLSERVER\OLAP\Backup"
  119.  
  120. ; The location for the Analysis Services temporary files.
  121.  
  122. ASTEMPDIR="C:\Program Files\Microsoft SQL Server\MSAS11.MSSQLSERVER\OLAP\Temp"
  123.  
  124. ; The location for the Analysis Services configuration files.
  125.  
  126. ASCONFIGDIR="C:\Program Files\Microsoft SQL Server\MSAS11.MSSQLSERVER\OLAP\Config"
  127.  
  128. ; Specifies whether or not the MSOLAP provider is allowed to run in process.
  129.  
  130. ASPROVIDERMSOLAP="1"
  131.  
  132. ; Specifies the list of administrator accounts that need to be provisioned.
  133.  
  134. ASSYSADMINACCOUNTS="DEMO\administrator" "DEMO\ssassvc"
  135.  
  136. ; Specifies the server mode of the Analysis Services instance. Valid values are MULTIDIMENSIONAL and TABULAR. The default value is MULTIDIMENSIONAL.
  137.  
  138. ASSERVERMODE="MULTIDIMENSIONAL"
  139.  
  140. ; CM brick TCP communication port
  141.  
  142. COMMFABRICPORT="0"
  143.  
  144. ; How matrix will use private networks
  145.  
  146. COMMFABRICNETWORKLEVEL="0"
  147.  
  148. ; How inter brick communication will be protected
  149.  
  150. COMMFABRICENCRYPTION="0"
  151.  
  152. ; TCP port used by the CM brick
  153.  
  154. MATRIXCMBRICKCOMMPORT="0"
  155.  
  156. ; Startup type for the SQL Server service.
  157.  
  158. SQLSVCSTARTUPTYPE="Automatic"
  159.  
  160. ; Level to enable FILESTREAM feature at (0, 1, 2 or 3).
  161.  
  162. FILESTREAMLEVEL="0"
  163.  
  164. ; Set to "1" to enable RANU for SQL Server Express.
  165.  
  166. ENABLERANU="False"
  167.  
  168. ; Specifies a Windows collation or an SQL collation to use for the Database Engine.
  169.  
  170. SQLCOLLATION="Latin1_General_CI_AS"
  171.  
  172. ; Account for SQL Server service: Domain\User or system account.
  173.  
  174. SQLSVCACCOUNT="Demo\SQLSVC"
  175.  
  176. ; Windows account(s) to provision as SQL Server system administrators.
  177.  
  178. SQLSYSADMINACCOUNTS="DEMO\administrator" "DEMO\sqlsvc"
  179.  
  180. ; Provision current user as a Database Engine system administrator for SQL Server 2012 Express.
  181.  
  182. ADDCURRENTUSERASSQLADMIN="False"
  183.  
  184. ; Specify 0 to disable or 1 to enable the TCP/IP protocol.
  185.  
  186. TCPENABLED="1"
  187.  
  188. ; Specify 0 to disable or 1 to enable the Named Pipes protocol.
  189.  
  190. NPENABLED="0"
  191.  
  192. ; Startup type for Browser Service.
  193.  
  194. BROWSERSVCSTARTUPTYPE="Manual"
  195.  
  196. ; Specifies which account the report server NT service should execute under.  When omitted or when the value is empty string, the default built-in account for the current operating system.
  197. ; The username part of RSSVCACCOUNT is a maximum of 20 characters long and
  198. ; The domain part of RSSVCACCOUNT is a maximum of 254 characters long.
  199.  
  200. RSSVCACCOUNT="NT Service\ReportServer"
  201.  
  202. ; Specifies how the startup mode of the report server NT service.  When
  203. ; Manual – Service startup is manual mode (default).
  204. ; Automatic – Service startup is automatic mode.
  205. ; Disabled – Service is disabled
  206.  
  207. RSSVCSTARTUPTYPE="Automatic"

7.  Run the .NET Framework 3.5 Feature Installation from PowerShell.

The following script enables non signed code to run, then installs the .NET Framework Feature and tells Windows to find the source files in the C:\Sources\sxs folder.

Set-ExecutionPolicy RemoteSigned -Force

Install-WindowsFeature –Name NET-Framework-Features –Source C:\Sources\sxs

8.  Insert Media (if not already inserted or copied locally). Run the SQL installation command or batch file.

The text used in mine is here (carriage returns added for readability):

d:\setup.exe /SQLSVCPASSWORD=P@ssword123 /AGTSVCPASSWORD=P@ssword123 

/ASSVCPASSWORD=P@ssword123  /configurationfile=c:\Scripts\configurationFile.ini /IAcceptSQLServerLicenseTerms

9.  Wait for the install to complete and check the log files. 

The installation log files will be found in in “C:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\Log”. Reboot (recommended) and test connectivity when setup has completed.

10.  Run the Mofcomp command or script to allow remote configuration of SQL Server Manager. The script is simply mofcomp sqlmgmproviderxpsp2up.mof from the “C:\Program Files (x86)\Microsoft SQL Server\110\Shared” directory


Troubleshooting

Cannot access the SQL Server from a remote server.

First check networking with ping etc. Check IP Addressing, DNS, subnets and LAN/VLAN config.

Once the above is checked the problem is most likely to do with firewall settings on the Windows Core Server. You can run the Coreconfig FirewallSettings.PS1 from the Powershell command line. Download from codeplex (link below) and copy to the virtual disk. You can do this by copying across the network or shutting down the VM and double clicking the VHDX file which will open up the drive to allow you to copy files to and from the VHDX.  Remember to Eject it when finished.

Download Core Configurator from here

http://coreconfig.codeplex.com/

The Firewall settings should look something like this.

clip_image001

This should enable remote connections correctly.

Alternatively turn off the firewall which can be done with the same tool, or Group Policy for example.

Cannot connect to Analysis Services

Again this is most likely to be a firewall issue. Run the following command to allow access to the Analysis Services default port.

netsh advfirewall firewall add rule name="SQL Server Analysis Services inbound on TCP 2383" dir=in action=allow protocol=TCP localport=2383 profile=domain

WMI Errors

When connecting remotely via Computer management you may get WMI errors.

See the following link

http://msdn.microsoft.com/en-us/library/ms188690(v=sql.110).aspx

Cannot connect to SQL Server Configuration Manager Remotely

Error :- Cannot connect to WMI provider.You do not have permission or the server is unreachable. Note that you can only manage SQL Server 2005 servers with SQL Server Configuration Manager.
Access is denied.

The fix for this to run the following command from the correct directory shown below.

Mofcomp sqlmgmproviderxpsp2up.mof

clip_image003

 

Cannot connect to SQL Server as dynamic ports are used for a named instance.

Add exceptions for the SQLSERVR.EXE file in the C:\Program Files\Microsoft SQL Server\MSSQL11.<instance_name>\MSSQL\Binn directory. Also ensure the SQL Browser service ports are unblocked. to do this enter:-

netsh advfirewall firewall add rule name="SQL Server Browser Services inbound on TCP 2382" dir=in action=allow protocol=TCP localport=2382 profile=domain

Fort further SQL Server firewall related configuration steps please see the following links:-

Configure the Windows Firewall to Allow SQL Server Access

http://technet.microsoft.com/en-us/library/cc646023.aspx#BKMK_ssas

Configure the Windows Firewall to Allow Analysis Services Access

http://technet.microsoft.com/en-us/library/ms174937.aspx

Thanks for reading !

 

Please let me know if you found this article useful and if you use or plan to use Windows Core with SQL Server and if there are any interesting findings you would like to share.

Installing System Center Virtual Machine Manager (SCVMM) 2012 SP1 on Windows 2012 the easy way !

In this article we install SCVMM with SP1 (Beta) on a clean Windows 2012 server. Note that with SP1 the Self Service Portal has been removed.

This is a standalone non-clustered SCVMM install.

scvmm


Requirements for SCVMM 2012 SP1 on Windows Server 2012

There are several requirements for SCVMM, some of which are listed here:-

  • SQL Standard / Enterprise (Express is no longer supported)
  • The SQL instance must be configured as case insensitive.
  • A domain account is also required for the SCVMM installation and must be an administrator on the SCVMM server.
  • Windows Assessment and Deployment Kit for Windows 8 – Download before installing SCVMM  to avoid downloading 3.5GB during installation possibly during installation on a server that has no internet connection.
  • The SCVMM machine name can’t include –SCVMM- for example My-SCVMM-Server but can be called SCVMM.
  • If using Dynamic memory the start-up RAM must be at least 2048 MB. This demo uses 4096 MB of RAM.
  • A container in Active Directory to store the Encryption keys, required for SCVMM cluster. See link at end for more info. Use ADSI Edit to create this container.
  • It is also recommended that the SQL Command Line Tools and Native Client Tools are also installed on the SCVMM server. See links at the end of this article. We have used the SQL 2012 versions here.

Download and script the Windows Assessment and Deployment Kit

To install the ADKSETUP on the SCVMM computer it is recommended that the adksetup.exe program is run and the files are downloaded before-hand from a computer with internet access. Then run the required command lines to install the ADKSETUP features required by SCVMM.

  • Check the SCVMM server has 2GB or more RAM allocated to it.
  • Download the latest ADK Setup.
  • Check version is 8.0 as 8.1 may cause issues with the SCVMM installer.

http://www.microsoft.com/en-us/download/details.aspx?id=30652

Make available for an offline PC so it does not download 3 GB every time. Select the second option.

image

This downloads the required files, and can take a while depending on internet connection as it is a 3GB download.

image

SCVMM Management Server only requires the Deployment Tools and Windows PE components.

Copy the above downloaded Windows Kit and the SCVMM software to the SCVMM server or somewhere they can be accessed.

To install just these features from the command line use the following command or click ADKSETUP to use GUI.

Adksetup /installpath “C:\Program Files (x86)\Windows ADK” /features OptionId.DeploymentTools OptionId.WindowsPreinstallationEnvironment /ceip off

Note: – Quotes around the install path need to be double quotes if there are spaces in your chosen location and the directory does not have to exist.

The command will open up the Assessment and Deployment Kit installation screen if there are no errors.

image

Click through the screens and check selections which should already be populated correctly.

image

If you run the command with a /q before the /ceip switch the installation proceeds silently without the need for any user interaction. This install should take only a minute or two.

image

Once complete the Assessment Deployment Kit will be present in Control Panel, Programs, Programs and Features.

clip_image001

Finally, reboot the server before SCVMM installation.


SCVMM With SP1 (Beta) Installation

Download is approximately 750MB.

Run the setup.exe and click Install.

clip_image002

Select the options required. Expanded in the screen shot for info.

clip_image003

Enter organisational or product key details.

clip_image004

Accept License Agreement, select customer improvement program and Microsoft Update options

clip_image005

clip_image006

Check installation location:-

clip_image007

Database Configuration

Specify the SQL Server to be used for the SCVMM databases.

clip_image009

Configure Service Account and key management

clip_image011

Leave as default or change ports as necessary, defaults shown here.clip_image013

Library Configuration

clip_image015

Check Summary and hit Install. Done.

clip_image017

Press close to launch the console

Click close to open the SCVMM connection screen then click connect.

clip_image019

SCVMM Console Screen
clip_image020

Troubleshooting

Errors reported on a clean Windows build without pre-requisites installed for reference.

Here are the errors if no pre-requisites are in place. Follow the instructions above to allow installation of SCVMM 2012 SP1 Beta

clip_image021

Cannot find SQL Server

Ensure the SQL Server is available and running, check firewalls and DNS. Select instance in SCVMM installer to show available instances.


References

ADKSetup (Windows 8 Assessment and Deployment Kit)

http://msdn.microsoft.com/en-us/library/hh825494.aspx#CMDLINE

SCVMM Deployment

http://technet.microsoft.com/en-US/library/gg610617.aspx

SQL Server 2012 Feature Pack tools – SQL Native Client and Command Line tools

http://go.microsoft.com/fwlink/p/?LinkId=253555

SQL Server 2008 R2 Feature Pack Tools

http://go.microsoft.com/fwlink/p/?LinkId=210563

Distributed Key Management – Recommended and required for failover SCVMM cluster.

http://technet.microsoft.com/library/gg697604.aspx