Friday, April 29, 2016

SQL Saturday in Baltimore #506 - Pre-Con

While Pre-Con is still going and our SQL Saturday event is scheduled for tomorrow I already starting to post pictures from it.

On SQL Saturday in Baltimore 2016 we have two following Pre-Con sessions:

"SQL Server Internals: The Practical Angle" by Dmitri Korotkevitch
"Building Better SSIS Packages" by Tim Mitchell

Here are the pictures:

























Tuesday, April 26, 2016

SQL Server Windows Core installation does not support Reporting Service

Previous topic: Seven obstacles of SQL Server Core Installation

Read next:
- How to Install and prepare Windows Core for SQL Server installation
- Five Steps of Remote SQL Server installation using PowerShell


Yep.
You could guess that it is pretty obvious that under Windows Core you can't have SQL Server Management Studio or Profiler, but we wouldn't imagine that one of four main SQL Server components won't be available in Core.

I've tried to install SSRS on my Virtual Core machine and here is what I've got:

Will copy-paste the error message here:
The following error occurred:
You have selected a feature that is not supported on Windows Server Core.
The supported features on Windows Server Core are:
Database Engine Services,
SQL Server Replication,
Full-Text and Semantic Extractions for Search,
Analysis Services,
Client Tools Connectivity,
Integration Services,
and SQL Client Connectivity SDK
.


That Error message let you figure out what you CAN'T install on Windows Core.
To make that decision you have to look through the whole list of possible options in MSDN: https://msdn.microsoft.com/en-us/library/ms144259.aspx#Feature


Obviously, SSRS is a component, but it is not in the "safe core" list.
Lets look at everything else with little cheating: By my experience I can do an installation of whole "SQL" feature module, that means, PolyBase ss part of "SQL" parent feature also has to be installable.
Hold on, lets look at my prior post: Installing Polybase for SQL Server 2016
Basically, for installing "PolyBase" feature you have to install Java from Oracle.
So, I've tried to install PolyBase and I've got that error:

That was predictable.
Then I've tried to install Java driver and got following:
In other words: my device is not in the list of those 3 billions devices, which run Java.
That means, technically, installation of PolyBase is allowed, but not possible on Windows Core Server.

So, where is PolyBase when you install "SQL" feature? - It is just ignored.
I've checked it by running following command:
SELECT SERVERPROPERTY ('IsPolybaseInstalled') AS IsPolybaseInstalled;
That command will return "0" (Zero), which means, feature is not enabled.

In order to determine what is supported and what is not supported by Core installation I had to run each feature separately and here is the result table. Enjoy.


Parent feature parameterFeature parameterDescriptionWindows Core Support
SQL
Installs the SQL Server Database Engine, Replication, Fulltext, and Data Quality Server.YES
SQLSQLEngineInstalls just the SQL Server Database Engine.YES
SQLReplicationInstalls the Replication component along with SQL Server Database Engine.YES
SQLFullTextInstalls the FullText component along with SQL Server Database Engine.YES
SQLDQCopies the files required for completing the Data Quality Server installation. After completing SQL Server installation, you must run the DQSInstaller.exe file to complete the Data Quality Server installation. For more information, see Run DQSInstaller.exe to Complete Data Quality Server Installation. This also installs SQL Server Database Engine.YES
SQLPolyBaseInstalls PolyBase components.NO
AS
Installs all Analysis Services components.YES
RS
Installs all Reporting Services components.NO
DQC
Installs Data Quality Client.NO
IS
Installs all Integration Services components.YES
MDS
Installs Master Data Services.NO
Tools
Installs client tools and SQL Server Books Online components.NO
ToolsBCInstalls backward compatibility components.NO
ToolsBOLInstalls SQL Server Books Online components to view and manage help content.NO
ToolsConnInstalls connectivity components.YES
ToolsSSMSInstalls SQL Server Management Tools – Basic. This includes the following: - SQL Server Management Studio support for the SQL Server Database Engine, SQL Server Express, sqlcmd utility, and the SQL Server PowerShell providerNO
ToolsADV_SSMSInstalls SQL Server Management Tools – Complete. This includes the following components in addition to the components in the Basic version: - SQL Server Management Studio support for Reporting Services, Analysis Services, and Integration Services - SQL Server Profiler - Database Engine Tuning AdvisorNO
ToolsDREPLAY_CTLRInstalls Distributed Replay controllerYES
ToolsDREPLAY_CLTInstalls Distributed Replay clientYES
ToolsSNAC_SDKInstalls SDK for Microsoft SQL Server Native ClientYES
ToolsSDKInstalls the software development kit.NO
ToolsLocalDBInstalls LocalDB, an execution mode of SQL Server Express targeted to program developers. LocalDB is an option when installing any SKU of SQL Server 2016 ExpressYES

Monday, April 25, 2016

Seven obstacles of SQL Server Core Installation

Following topics:
- How to Install and prepare Windows Core for SQL Server installation

- SQL Server Windows Core installation does not support Reporting Service

- Five Steps of Remote SQL Server installation using PowerShell


I strongly believe when a system has less moving parts it behaves more stable and responds faster.
The Windows Core is perfect example when there are much less moving parts than in Windows with GUI.
Why is that?
Generally Windows Server installed with EVERYTHING, with all possible features you might not need, especially when you plan to have only SQL Server in that box.

There are three reasons why Windows Server with Core is better than Server with a GUI:
1. Security: Core has less features. That means less surface for intruders to attack.
2. Management: No unnecessary updates and planned reboots.
3. Resource consumption: Less memory usage and less CPU overhead


I've seen a situation when 8 DBAs connected to a SQL Server via Remote Desktop. Run SQL Server Management Studio with several tabs and query results opened and disconnected from the server without closing their sessions.
Discussion about the lack of memory on that server was very informative and interesting.

You do not have that problem with Core because ALL SQL Server administration can be done remotely from DBA's PC or dedicated Gateway Server. Less people can go to your server more secure and stable it is.

In this post I will describe my experience with installing SQL Server in Windows Core environment.

Here are all Seven obstacles I've hit during that installation:

1. Installation must be performed ONLY in command line mode. There is just NO GUI. You Have to start SQL Server installation with following command:
setup.exe /ACTION=Install

2. Because there is no GUI, installation MUST be absolutely "QUIET", which requires following parameter:
/Q

3.During the installation you are usually asked about License Agreement. Right? To suppress that and "quietly" answer "I Accept SQL Server License Terms", you have to add following parameter:
/IAcceptSQLServerLicenseTerms

4. SQL Server on Core does not support ALL SQL Server features. For Instance it does not support Reporting Services and Management Studio. To solve the issue you have to specify features you want to install like this:
/FEATURES=SQL,AS,IS
More details about features are in MDSN: https://msdn.microsoft.com/en-us/library/ms144259.aspx#Feature

5. You have to decide how you will access your SQL Server. If your Server is in a domain, than you can provide domain admin account or domain group. If you do not have a domain you better use SQL authentication. You also have to specify Local user accounts with passwords, which will be used for running services:
/SECURITYMODE=SQL /SAPWD=<Your strong SA Password> /SQLSVCACCOUNT="MyDomain\MyAccount" /SQLSVCPASSWORD=<Password> /SQLSYSADMINACCOUNTS="MyDomain\MyAccount" /AGTSVCACCOUNT="MyDomain\MyAccount" /AGTSVCPASSWORD=<Password> /ASSYSADMINACCOUNTS="MyDomain\MyAccount"

6. You have to specify name of your SQL Server instance:
/INSTANCENAME=MSSQLSERVER

7. You have to specify the SQL Server product key. If you do not do that, SQL Server will be installed in Evaluation mode. You do it like this:
/PID=<Valid SQL Server product key>

That is it. Seems very easy.

Lets Start do SQL Server Core installation in three easy steps:

Step 1. Connect to a Windows Core Desktop and login as an administrator:


After you login, screen should look like this:

Step 2. Copy-paste following script and hit enter:

d:\setup.exe /ACTION=Install /Q /IAcceptSQLServerLicenseTerms /INSTANCENAME=MSSQLSERVER /SAPWD=MyStrongSAPassword2016 /SECURITYMODE=SQL /FEATURES=SQLENGINE /SQLSVCACCOUNT="WinCore\CoreAdmin" /SQLSVCPASSWORD="LocalAdmin2016" /SQLSYSADMINACCOUNTS="WinCore\CoreAdmin" /AGTSVCACCOUNT="WinCore\CoreAdmin" /AGTSVCPASSWORD="LocalAdmin2016" /ASSYSADMINACCOUNTS="WinCore\CoreAdmin"
There are few assumptions:
A. I've used Server name "WinCore"
B. I've used Server Admin Account "CoreAdmin" with password "LocalAdmin2016"
C. My SQL Server installation DVD is on "D:\" Drive.
D. I've tried "Evaluation" installation mode.

It should look like this:

After several minutes of the installation I got following:
In case your SQL Server installation produced errors, the error file and details will be stored in following folder: "C:\Program Files\Microsoft SQL Server\<Version Number>\Setup Bootstrap\Log\" on the Server.

Step 3. Open Incoming Access Port for Your newly installed SQL Server and Restart the box.

At first, run following command in PowerShell to open the port
New-NetFirewallRule -DisplayName "Open Inbound Port 1433" -Direction Inbound –LocalPort 1433 -Protocol TCP -Action Allow
Then, restart the box.

After the restart, I opened SSMS on my local machine and connected to the newly built SQL Server:

In this post I did not cover the Windows Core installation itself, but I might do it in one of my following posts.