Pre – Planning for a SQL 2012 Upgrade

Items to Consider before SQL 2012 Installation

Before installing a server the company needs to consider the security issue of their system. Security is essential for any business and any product. By carrying out simple best security practices, an organization can avoid various security vulnerabilities. In this regard among the number of things the organization should consider prior to the installation include: improving physical security, using firewalls, isolating services, configuring a secure file system, disabling NetBIOS and blocking server message and finally, installing SQL on a domain controller.

Logical and physical isolation build the SQL server security foundation. To improve the SQL Server physical security, the organization should consider putting the server in a room which is only accessible to authorized individuals. The database hosting computer should also be place in a secured physical location, preferably a locked room containing supervised fire detection and flood detection or suppression systems. Databases should then be installed in a secure corporate intranet zone and not directly to the internet. The organization should also organize for an off-site location backup system.

Firewalls are essential to assist in securing the SQL Server installation. To enhance the effectiveness of the firewall, the organization should place a firewall between the internet and server and enable it. The network should then be divided into different security zone by the use of firewall. In this case all traffic should be blocked and admit selectively only what is needed. Multiple firewalls should be generated to develop subnets in a multi-tier setting. Interior firewalls should then be configured to allow authentication.

Read also SQL Security and High Availability

Services isolation lowers the risk that a compromised service would be utilized to compromise others. To isolate services the organization should consider running distinct SQL Server services under distinct Windows account.  Utilizing the accurate file system augments security. In this case the NTFS file system will need to be configured. This file system is preferred since it is more recoverable and stable as compared to FAT file system. It also allows security choices that include file system encryption, and directory and file access control list. In the perimeter network, servers need to have all usual protocols disabled. This includes server message block and NetBIOS. However, DNS and web servers do not need SMB or NetBIOS. On the two servers both protocols need to be disabled to lower the user enumeration threat. This should then be followed by installation of the SQL Server (Valonfeka, 2015).

Read also Microsoft SQL Server 2012 All Editions Review

SQL 2012 Editions Review

The SQL Server 2012 version comes with different versions. They include web and developer edition, express edition, enterprise edition, business edition and standard edition among others: Standard Edition is restricted to 16 scores and a RAM of 64GB. It gives the central relational engine of database and primary capabilities of business intelligence. The edition does not incorporate support for the extensive availability features or the extra powerful features of Business Intelligence. This edition does incorporate support for Always-On Failover two-node clusters. It is licensed for per server of per core.

Read also CMIT 370 7982 Windows Network Proposal Project Instructions

Business Intelligence edition is restricted to a RAM of 64GB and database engine of 16 cores. It can utilize the maximum value of cores which are supported by the operating system for reporting services and analysis services. This edition has all standard edition features, though it lacks advanced available features support that includes online operation. This edition is licensed for every server and it supports AlwaysOn Failover two-node clusters.

Enterprise edition supports the biggest number of RAM and cores in the host OS and gives the complete feature set of the SQL Server. This includes support for all advanced BI and availability features. The edition supports a maximum of 16-node AlwaysOn Failover Clusters and the ColumStore index, transparent data encryption, advanced auditing, Master Data Services, Power View, PowerPivot, online operations, and AlwaysOn Available Groups. The edition is licenses for each core.                

LocalDB and Expression Edition Editions: this edition comes in three different versions which include express containing advanced services, express with tools, and express which contains database only. These editions are restricted to support for a RAM of 1GB, and one CPU. In addition, databases are restricted to 10GB per database and a new choice referred to as LocalDB will as well be available. This LocalDB uses the sqlservr.exe engine and is created for developers. It does not needs configuration and it executes as a user process.   

Developer and Web Edition give similar features as the enterprise edition set. Nevertheless, it is licensed per every developer and can never be utilized for production purposes. This edition is just licensed to hosting firms with Service Provider License Agreement.  The best edition to be used in this case is the enterprise edition. This is because the edition does not have a lot of limitation. It supports the highest number of RAM and cores in the host OS and gives the complete set of SQL Server feature. It also supports all BI and advanced available features. In this regard, this is the only edition without a lot of restriction and thus, it gives the company a chance to expand its operation in the future (Otey, 2012).  

Minimum Hardware Requirement

The server focuses on serving a maximum of 500 users. In addition the company focuses on working with two servers and in this regard, the company will be using dual server. Other minimum hardware requirements include one Intel Xeon dual core processor at 2.13 GHz, a memory of 4 GB and one hard disk of 500 GB operating at 7.2k (Msdn. Microsoft, 2015). To ensure availability, the SQL 2012 server hosted on the Hyper-V Windows 2012 environment enjoys the advantage of Live Migration that allows virtual machines migration without downtime. Windows 2012 server also permits simultaneous migration of several SQL Server VMs as desired, assisting in maintaining SQL Server availability while lowering planned downtime. In so doing, the server uses a network bandwidth of up to 10GB.  The presence of Cluster-Aware Updating can permits updates to be automatically used to host the OS or other components of the system in a clustered SQL Server setting while upholding availability. This can essentially assist in increasing the availability of SQL server in the process of updating in both non-virtual and virtual environment. The Failover Clustering Dynamic Quorum for windows server 2012 permits SQL Server AlwaysOn cluster to dynamically change the quorum votes number that are needed to keep executing. This can make a set up simple by 80 percent and assist in augmenting SQL Server cluster availability in scenarios failover in both non-virtualized and in virtualized environment (Heuring & Komo, 2013).

To ensure the scalability, the windows server 2012 contains high capacity virtual memory and processors that allow the organizations to organize mission critical workloads in virtual setting by use of SQL Server 2012. The VM can utilize a maximum memory of 1TB and 64 virtual processors. This server can attain greater deployment density in virtualized environment permitting a maximum of 8, 000 Virtual SQL Servers in every cluster. It also allows bigger cluster scalability, handling up to 64 nodes of SQL Server clusters.           

Synopsis of any Possible Installation and Configuration Issues

There are a number of issues that one can encounter when configuring SQL 2012 Server. Some of these issues include error when linking to a remote database server, failure in database creation when account of a user cannot be resolved, failure in database creation with error in system catalog, failure in database creation with Invalid column ‘ProductVersion. Invalid column ‘ProductName’. Others include failure in database creation with ‘Data is Null error, error when retrieving the web service, Unauthorized HTTP 401.2, unavailability of HTTP 503-Service, internal server error of HTTP 500.21, and internal server error of HTTP 500.19. To eliminate the remote connection error, one should ensure that he or she is able to enable the TCP/IP protocols and Named pipes of the database engine during the connection. To resolve failure in database creation, one should configure the service to utilize the service account with domain access. To resolve the catalog error, one should set the parameter of allow updates to 0 and then try to develop the database again. To resolve column error, one should consider creating the database of Master Data Service on a different instance of database Engine.  To resolve 503 error guarantee that the application pool in IIS is executing. To resolve 500.21 error, one should access the command prompt and access the .Net directory to execute aspnet_regiis-I command. To handle 500.19 error one should ascertain that there is proper computer configuration for the web application of the master data manager and for null data error one should ensure that all databases in the same instance of database engine are online and not restored (Lee, 2011).

Scroll to Top