Share via

SharePoint 2013: SQL Server 2014 CTP2 for SharePoint

  • Article

The evolution of SQL Server was announced at TechEd North America. For more details please check the following link:http://blogs.technet.com/b/dataplatforminsider/archive/2013/06/03/sql-server-2014-unlocking-real-time-insights.aspx

http://gokanx.files.wordpress.com/2014/01/a21.png?w=600&h=336

When you install for the first time SQL Server 2014 you can think that there aren’t many changes and the same stuff has been released but you can only see the real differences when you’ll have a closer look to the product. According to the official blog the real differences are as following:

SQL Server 2014 helps organizations by delivering:

  • Mission Critical Performance across all database workloads with In-Memory for online transaction processing (OLTP), data warehousing and business intelligence built-in as well as greater scale and availability
  • **Platform for Hybrid Cloud **enabling organizations to more easily build, deploy and manage database solutions that span on-premises and cloud
  • **Faster Insights from Any Data **with a complete BI solution using familiar tools like Excel

** **

Please check the hardware and software requirements for SQL Server 2014

  • **.**NET 3.5 SP1 is a requirement for SQL Server 2014 when you select Database Engine, Reporting Services, Master Data Services, Data Quality Services, Replication, or SQL Server Management Studio, and it is no longer installed by SQL Server Setup.
  • SQL Server 2014 does not install or enable Windows PowerShell 2.0; however Windows PowerShell 2.0 is an installation prerequisite for Database Engine components and SQL Server Management Studio.
  • Supported operating systems for SQL Server 2014 have built-in network software. Named and default instances of a stand-alone installation support the following network protocols: Shared memory, Named Pipes, TCP/IP and VIA.
  • SQL Server 2014 requires a minimum of 6 GB of available hard-disk space.
  • Express Editions: 1 GB. All other editions: At least 4 GB and should be increased as database size increases to ensure optimal performance.
  • Processor Speed 2.0 GHz or faster
  • Processor Type: x64 Processor: AMD Opteron, AMD Athlon 64, Intel Xeon with Intel EM64T support, Intel Pentium IV with EM64T support. x86 Processor: Pentium III-compatible processor or faster

For more hardware and software requirement for SQL 2014 please check the following link:http://msdn.microsoft.com/en-us/library/ms143506(v=sql.120).aspx#hwswr

 

Thinks to know about SQL Server 2014

  • The Microsoft SQL Server 2014 CTP2 release is NOT supported by Microsoft Customer Services and Support (CSS).
  • The Microsoft SQL Server 2014 CTP 2 release is available for testing purposes only. The Microsoft SQL Server 2014 CTP 2 release should NOT be installed and used in production environments, including side-by-side with a production instance of SQL Server.
  • Upgrading from Microsoft SQL Server 2014 CTP1 to Microsoft SQL Server 2014 CTP2 is NOT supported.

 

http://technet.microsoft.com/en-us/evalcenter/dn205291?download=dlm3&url=http://care.dlservice.microsoft.com/dl/download/1/F/E/1FE74710-7503-4434-B9BB-80FF064DCA63/SQLServer2014CTP2-x64-ENU.iso

For sharePoint 2013 you can install and user SQL Server 2014 CTP2. It’s not because you can that you should do it. When launching the SETUP.EXE you can see the same welcome screen as the previous versions of SQL Server. If you want to install SQL Server2014 CTP2 on a development environment hit New SQL Server stand-alone installation or add features to an existing installation under Installation.

http://gokanx.files.wordpress.com/2014/01/a22.png?w=600&h=517 

You can validate your SQL Server 2014 CTP2 by entering the 25-carachter key from the Microsoft Certificate of Authenticity but I want to install the free edition of SQL Server please select “Evaluation”. But for the moment the best choice is to select Evaluation as this option has the largest set of SQL Server features for free.

Be aware that this evaluation is limited for 180-day. If you want to upgrade from one edition to another you still can execute the Edition Upgrade Wizard.

http://gokanx.files.wordpress.com/2014/01/a23.png?w=600&h=517

Accept the license requirements and PLEASE turn the customer experience improvement program. This will help the SQL team to improve the quality, reliability and performance of SQL Server 2014 and not only the CTP2.

http://gokanx.files.wordpress.com/2014/01/a24.png?w=600&h=517

On this screen SQL Server 2014 CTP2 setup is testing a few rules to be sure that SQL 2014 CTP 2 can be installed for SharePoint 2013. Everything run smoothly but on my development environment I got 2 warnings:

http://gokanx.files.wordpress.com/2014/01/a25.png?w=600&h=517

  • Microsoft .NET Application Security: You’ll receive this error when the server/computer can’t access the Internet for checking the latest patches/updates.
  • Windows Firewall: You’ll receive this error when the Windows Firewall is enabled; so you have to open few ports like:
    • One inbound TCP rule with ports:1433,2383,2382
    • One inbound UPD rule with port: 1434

1433: SQL Server is a Winsock application that communicates over TCP/IP by using the sockets network library. SQL Server listens for incoming connections on a particular port. The default port for SQL Server is 1433. The port doesn’t need to be 1433, but 1433 is the official Internet Assigned Number Authority (IANA) socket number for SQL Server.

2383: TCP port 2383 should be open when installing a default instance or creating an Analysis Services failover cluster.

2382: TCP port 2382 should be open when installing a named instance. Named instances use dynamic port assignments. As the discovery service for Analysis Services, SQL Server Browser service listens on TCP port 2382 and redirects the connection request to the port currently used by Analysis Services.

1434: the client computer would need to open a random UDP port and the server UDP port 1434 will be used to send the instance name, and if the instance is clustered, the version of the SQL instance, the TCP port number that the instance is listening on, and the named pipe that the instance is using. However, if the goal is to minimize the number of ports open on the firewall, a static port number should be chosen for the default instance and any named instance. The client computers would need to be configured to connect to a particular Server Name or Server Name instance and specific port number.

You can choose between SQL Server Feature Installation and SQL Server PowerPivot for SharePoint. Four our Development environment please select as shown the first one and hitNEXT.

http://gokanx.files.wordpress.com/2014/01/a26.png?w=600&h=519 

At the next screen of our SQL 2014 CTP2 installation as on the previous versions you’ll have to select the minimum features needed for SharePoint. Select the features that you think is capital for your environment and hit next.

http://gokanx.files.wordpress.com/2014/01/a27.png?w=600&h=517

Provide a simple or complex name for your instance depending the requirement. Be aware that if you choose a Named Instance SQL Server Browser should be running under the Windows Services. Otherwise, if you choose the default instance the SQL Server Browser can be stopped.

http://gokanx.files.wordpress.com/2014/01/a28.png?w=600&h=517

If you are using Managed Accounts for the SQL Services you can change them here. You can select the startup time for each SQL Server Service and also be sure to select “Automatic” for SQL Server Browser if you selected a Named Instance.

http://gokanx.files.wordpress.com/2014/01/a29.png?w=600&h=517

A SharePoint Server Best Practice for SQL Server is to change the Database Collation form the default value to Latin1_General_CI_AS_KS_WS. Therefore please execute the following:

  • Select « Latin_General » from the list.
  • Don’t select the « Case-sensitive ».
  • Select  « Accent-sensitive »
  • Select « Kana-sensitive »
  • Select « Width-sensitive »
  • Hit ok.

http://gokanx.files.wordpress.com/2014/01/a30.png?w=600&h=517

 

Windows authentication for SQL is considered as a Best Practice, but you won’t lose any functionality if you use mixed mode.  Here’s an article that discusses security in SharePoint:http://technet.microsoft.com/en-us/magazine/ff625837.aspx

This is what the article says on SQL authentication.

“Though you could choose either mixed mode or Windows Authentication Mode, you should configure your SQL Server to use Windows authentication whenever possible. Windows mode is more secure than mixed mode because it uses the Kerberos security protocol during the authentication process. Moreover, Windows authentication uses domain user accounts, so any password policies you have established within your Active Directory remain in effect”

It’s up to you to choose with mode you want to use on your development machine but I always select the Mixed Mode and add the Current User and the Farm Admin as SQL Server Administrator.

http://gokanx.files.wordpress.com/2014/01/a31.png?w=600&h=517

You actually don’t have another choice to select the **Install Reporting Services **and hit next so proceed as asked.

http://gokanx.files.wordpress.com/2014/01/a32.png?w=600&h=517

And finally the can begin… When the installation is finished run the SharePoint Products Configuration Wizard and create a new Farm.

The wizard will ask you to enter the:

  • Database Server
  • Database Name
  • Username
  • Password

http://gokanx.files.wordpress.com/2014/01/a33.png?w=600&h=517

But as many SharePoint Administrators you can receive the following error: “Cannot connect to the configuration database or Unable to connect to database. Check database connection information and make sure the database server is running.”

Causes

There are many reasons why:

  • SQL database and services are down.
  • The SQL database may not be running correctly
  • You applied a Hotfix or Service Pack and did not reboot.
  • The Firewall is blocking the communication
  • The SharePoint Installation Account does not have the required permissions to the SQL Server database.
  • Network connectivity is not optimal between the SharePoint Server and SQL Server.
  • …

Troubleshooting steps

1.      Check logs:

Review the PSCDiagnostics log at, C:\program files\common files\Microsoft shared\web server extensions\15 or \14 for the SharePoint logs

This is the kind error that you can receive with maybe more information:System.Data.SqlClient.SqlException was thrown. Additional exception information: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (Provider: SQL Network Interfaces, error: 26 – Error Locating Server/Instance Specified)

2.      Verify that the SQL database is running and services.

In the list of services, locate the MSSQLSERVER service and be sure that it’s running.
Be even sure that on the Microsoft SQL Server, the following services are running:

  • SQL Browser** (if your aren’t using the default instance name)**
  • All other SQL services

3.      Firewall

Firewall can block access and communication with your Microsoft SQL Server so you have 2 possibilities.

  1. Disable Firewall, easiest way on development machine but not secure and recommended for a Production environment.
  2. So you can create 2 rules on the Firewall:
    1. One inbound TCP rule with ports:1433,2383,2382
    2. One inbound UPD rule with port: 1434

4.      Is your SQL configured correctly?

Is actually your SQL server correctly setup? Are you sure about the steps that you executed? If not please check here. All these links are official TechNet articles:

  1. Installation how-to Topics
  2. Install SQL Server 2012 on Server Core
  3. Validate a SQL Server Installation
  4. Check Parameters for the System Configuration Checker
  5. Product Updates in SQL Server 2012 Installation
  6. Configure the Windows Firewall to Allow SQL Server Access

5.      User Permissions

Next, you have to verify that your account has the required permissions on the SQL Server database.

  1. Click Start, point to Programs, point to Microsoft SQL Server, and click Enterprise Manager
  2. In the left pane, double-click Microsoft SQL Servers, and then double-click your SQL server group.
  3. Double-click your server.
  4. Double-click Security.
  5. In the left pane, click Logins.
  6. In the right pane, double-click the user for your Farm Admin Global Administrator.
  7. In the SQL Server Login Properties dialog box, click Server Roles.
  8. And select the following:  Security Administrators and the** Database Creators** check boxes and then click Database Access.

6.      Can they talk to each other?

  1. Verify that SharePoint is using the correct IP address for the SQL server. To do this, run the ping command on the Windows SharePoint Services server.
  2. Verify that the SharePoint server is obtaining the correct IP address for the SQL server from DNS. To do this, run the nslookup command from the SharePoint Server.
  3. Make sure that there are no incorrect entries for the SQL server. To do this, examine the Hosts file on the SharePoint server. This file is in the following location:
    %systemroot%\system32\drivers\etc\Hosts
  4. On the SharePoint server, look for SQL client aliases. To do this, follow these steps: Click
    1. Start, click Run, and then type cliconfg in the open box.
    2. Click the Alias tab. By default, there are no SQL client aliases. If you have any aliases for the SQL server, verify that they are correct, or remove them.
    3. Open the SQL Server Configuration Manager (Start SQL Server 2008  Configuration Tools  SQL Server Configuration Manager
      1. Navigate to the SQL Server Network Configuration  Protocols for MSSQLSERVER node in the tree view
      2. Enable TCP/IP and Named Pipes (you’ll be warned that these changes will not apply until the service is shut down)

7.      SID

Please be sure that if you made a copy of a Virtual Machine that you used sysprep before to avoid getting the same SID! You can use PSTOOLS to change this if it’s not the case.

  • First, click to Start->Run, type sysprep and press OK.
  • This will open sysprep folder which is located in c:\Windows\System32. Open sysprep application.
  • This will open System Preparation Tool 3.14 window. As a System Cleanup Action select Enter System Out-of-Box Experience (OOBE). Important: select generalize if you want to change SID, it’s not selected by default. As Shutdown Options select Reboot.
  • After rebooting you’ll have to enter some data, for example, Country or region, Time and currency and Keyboard input.

8.      Reset Database-connection-timeout and is your DB up-to-date

  1. Follow the http://technet.microsoft.com/en-us/library/cc263314.aspx and change the timeout to 45 with the next command: stsadm -o setproperty -pn database-connection-timeout -pv 45
  2. Click Start, click Run, type cmd in the Open box, and then click OK.
    1. **1.       **Change to the following directory:  system drive\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\Bin
    2. Run the following command: 
      psconfig -cmd upgrade -inplace b2b
    3. In SQL Server Configuration Manager, in the console pane, expand SQL Server Network Configuration, expand Protocols for <instance name>, and then double-click TCP/IP.
    4. If the TCP Dynamic Ports dialog box contains 0, indicating the Database Engine is listening on dynamic ports, delete the 0.
    5. If the TCP Port box isn’t 1433, type the port number 1433 and then click OK.
    6. In the console pane, click SQL Server Services.
    7. In the details pane, right-click SQL Server** (<instance name>)** and then click Restart, to stop and restart SQL Server.

9.      To assign a TCP/IP port number to the SQL Server Database Engine

  1. In SQL Server Configuration Manager, in the console pane, expand SQL Server Network Configuration, expand Protocols for <instance name>, and then double-click** TCP/IP**.
  2. If the TCP Dynamic Ports dialog box contains 0, indicating the Database Engine is listening on dynamic ports, delete the 0.
  3. If the TCP Port box isn’t 1433, type the port number 1433 and then click OK.
  4. In the console pane, click SQL Server Services.
  5. In the details pane, right-click SQL Server** (<instance name>)** and then click Restart, to stop and restart SQL Server.

10.  SharePoint administrative accounts: Local Administrator

The installation account is used to set up each server in your farm by running the SharePoint Configuration Wizard, the initial Farm Creation Wizard, and Windows PowerShell. For the examples in the setup user administrator account is used for farm administration, and you can use Central Administration to manage it. Some configuration options, for example, configuration of the SharePoint 2013 Search query server, require local administration permissions. The setup user administrator account requires the following permissions:

  • It must have domain user account permissions.
  • It must be a member of the local administrators group on each server in the SharePoint farm, excluding the server running SQL Server and the Simple Mail Transfer Protocol (SMTP) server.

When done you’ll have to insert a Passphrase.

The Passphrase in SharePoint 2013 Preview is described as: (From the ‘Deployment guide for Microsoft SharePoint 2013 Preview’). Although a passphrase resembles a password, it is usually longer to improve security. It is used to encrypt credentials of accounts that are registered in SharePoint 2013 Preview. For example, the SharePoint 2013 Preview system account that you provide when you run the SharePoint Products Configuration Wizard. Ensure that you remember the passphrase, because you must use it every time that you add a server to the farm.

Ensure that the passphrase meets the following criteria:

  • Contains at least eight characters
  • Contains at least three of the following four character groups:
    • English uppercase characters (from A through Z)
    • English lowercase characters (from a through z)
    • Numerals (from 0 through 9)
    • Nonalphabetic characters (such as !, $, #, %)

http://gokanx.files.wordpress.com/2014/01/a34.png?w=600&h=517

Provide or leave the standard port number and hit next. The port number should be preferable other then 80 and not equal to 443 if you aren’t planning SSL.

http://gokanx.files.wordpress.com/2014/01/a35.png?w=600&h=517

And after a few clicks your SharePoint 2013 is running under SQL Server 2014 CTP2! Nothing changed from the previous installations and I’m sure that if you are able to install and configure SQL Server 2008, SQL Server 2008R2 or SQL Server 2012 this shoudn’t be a problem!!

 

http://gokanx.files.wordpress.com/2014/01/a36.png?w=600&h=517

See Also