Customized Installing of SQL Server 2022 on a laptop - Part 2

 In Part 1 we looked at hardware and software requirements and determined that the Surface Pro laptop could satisfy the requirements for the SQL Server 2022 installation. There are also links to earlier versions of SQL Server at the end of this article.

If you are looking to working on your SQL Server in an isolated mode to gain some experience, you may not need to worry about a number of items in the Planning node of the SQL Server Installation center such as,

• Azure extension for SQL server (New)
• Download Data Migration Assistant(DMA)
• How to get started with SQL Server 2022 Failover Clustering
• Upgrade Documentation
• Download SQL Server Migration Assistant (SSMA)

However, make sure you go through the other items. You could always bring up the SQL Server Installation Center later if you need to.

The next item on the SQL Server Installation center is Installation. A click on the installation node in the SQL Server Installation enter brings up this page (window).

On this page, these items are important, 

• New SQL Server standalone installation or add features to an existing installation.
• Install SQL Server Reporting Services
• Install SQL Server Management Tools
• Install SQL Server Data Tools

If this is a fresh install, you will be using the New SQL Server standalone installation, the first item's option.

Click "New SQL Server standalone installation"

The installation begins and you will be getting messages regarding installation progress, you just wait them out.

After a while you get his page(window).

Since we are using a free edition, just choose the first option Specify a free edition. However, have a look at the two other options

Also, note on the navigation items on the left of this page. The installation goes through each of these items. It may skip through some items that are not relevant.

Click the button Next> at the bottom. As the case may be, sometime you want to go back to the previous screen and a back button is present.

The following page is displayed regarding license terms.

If you accept by checking the "I accept..." control at the bottom you can install. Some of your data may be collected by the software. It may be prudent to keep a copy of the license terms should you be interested.

Click on the Next> button. As you may have observed, it skips the Global Rules step in the installation and goes to Microsoft Update. 

Accept the recommendation of Microsoft and place a checkmark. Here, you have an option to go to the shown links to check out FAQ and Privacy related statements.

Click Next>. It processes your request rapidly and skips Product Updates, to Install Setup Files .

After Install Setup Files the Install Rules page gets displayed. You may see Windows Powershell highlighted. You need not worry as Windows Powershell is already installed on Windows 11 Pro computers and laptops.

Click Next>. 

This is the Azure Extension for SQL Server and skip this as we are just looking at the SQL Server on our laptop exclusively.  This is wasn't there in the SQL Server 2012 edition. The Azure Cloud services had just appeared in beta agt that time.

Let us disregard this (do not check any of the boxes) and click Next>.

This takes you to the Feature Selection page as shown.

This is an important step and you should take time to read and understand the items. It describes broadly under Instance Features, Shared Features and Redistribution Features. Microsoft Reporting Services used to be a  part of the installation in earlier editions of SQL Server. In this edition you can install it using the internet link shown on this screen.

Place a check mark on Database Engine Services. Windows Powershell, a powerful programming is already installed and it used to be a requirement in earlier editions. It comes with Windows 11. We will not be interested in Replication and you skip this one. Machine Learning Services and Language Extensions will be very useful should we use R, Python, Java and other programming languages. With the advent of AI this may become very useful. If you choose this, it will enter the installation queue and will be installed. It would take up 1419 MB of disk space as shown.

Choose the option "Full-Text..."

If you want to connect to non-SQL databases choose the option "Polybase query Service for External Data". I believe, Polybase Query had just appeared in Beta in 2012.

As you choose, items the disk space requirements gets updated as you see in your right side boxes.

For now, we skip Analysis Services. We also skip the whole of Shared Features as well as Redistribution Features. We can always go to the SQL Server Installation Center and modify items later.

At the very bottom, observe that the installation root directory is shown as, C:\Program Files\Microsoft SQL Server\

Click Next>.

It takes you to the Instance Configuration page as shown with a standard Default instance, named MSSQLSERVER. This naming has little changed over the editions. The SQL Server directory is also shown.

If you choose the Named instance by clicking on it. You need to give a name for your SQL Server.  

Let us choose a named instance. Herein, Regency2024 is chosen. The directory gets modified to reflect your choice.

As there are no SQL Servers installed presently on this machine, this being the first,  the Installed Instances does not list any server. 

Click Next>.

It goes to Polybase Configuration as shown. 

It pre-chooses a set of ports to which non-SQL Server databases may be connecting to. We will come to it, when we have to. This page was not there in SQL Server 2012 and Polybase had just made its appearance and was in its initial stages.

Click Next> takes you to the important, Server Configuration page as shown.

In SQL Server 2022, NT Service accounts are virtual accounts used to run SQL Server services. These accounts are automatically managed by Windows and are designed to provide a high level of security and ease of management.

All these do not require user intervention as they are managed by the Windows system. If they are set Manual it suggests they are set manual for resource optimization. They can be Started/Paused/stopped using Windows services. Microsoft recommends a separate account, but herein the default will be used.

Place check mark for "Grant Perform...." You may look at details going to the linked page shown.

You may also review this link: https://learn.microsoft.com/en-us/sql/relational-databases/databases/database-instant-file-initialization?view=sql-server-ver16

Click Next>.

This takes you to the Database Engine Configuration page shown in the next screen,

There are very important items on this page. At the very top there are tabbed pages in this Database Configuration node:

• Server Configuration (the page presently displayed)
• Data Directories
• TempDB
• MaxDOP
• Memory
• FILESTREAM

All bulleted items are configurable although defaults are accepted here.

In the Server Configuration tabbed page one has to choose between Windows authentication mode (default) and a Mixed Mode (SQL Server authentication and Windows authentication) needs to be chosen. Windows authentication is considered to be more secure because it uses Windows security model with all its features like password policies, NTLM protocols, etc. The management is simple. 

Mixed-mode authentication is more flexible. Please go here, for more details, or read my earlier posts.

Let us choose Windows authentication. We would like to Add Current User (the current user is also the one installing the SQL Server).

Click Add Current User. It adds OLIBU\hoden. These are the credentials of the ComputerName\Computer User (windows). You could add, or remove more users. Remember that they should be in the Windows Users list.  If you need to add people to this, you could do it later as well. Since it is windows User, no need to enter password, etc.

Click Next>

It skips everything else and goes to Ready to Install node of the navigation on the left ass shown. If you need to configure the items on this page, you could go to each of the tabbed page and configure. You may read here for more.

Only a part of this screen is shown. The details shows are available in the SQL Server Configuration file, Configuration.ini. When you install, all of the above will be installed.

Click Install.

It starts installing and shows a progress bar. After the installation is complete, this page is displayed.

The installation was successful. A installation log is generated for checking up the details.

Close the Installation center window.

Installing SQL Server 2017 is a breeze

Installing SQL Server 2017 on Windows 10

Installing SQL Server 2016 SP1

Installing SQL Server 2016

Watch this video for SQL Server 2016 Custom for Reporting Services:

https://www.youtube.com/watch?v=qp6IF_rAi80

 

Report Builder 3 has UI problems that needs addressing

User Interface is very important to a graphical tool such as Report Builder. I have been using since the earliest version.

This version of Report Builder 14.0.609.142 seems to have UI problems. It is not related to resolution and I think it is in need of rework.

Here how the UI to create a connection to a SQL Server using Report Builder 3. Here you can see the whole UI (all buttons), the intermediate elements are messed up.

Expression is often used in Report Builder to fashion some relevant expression. In Report Builder 3 version 14.0.609.142, there is no OK button to submit your expression to be processed. Also the window is inflexible, bad design. Even the Help button is only partially visible.

This is not related to resolution as I have tried to see how it looks at different resolutions and even in Landscape/Portrait views. If the OK and Cancel buttons did exist they would have been aligned with the half-showing Help button.

Few more images at different display settings:

Here is how it should have appeared (copy from an earlier version article on MSDN).

New Subscription or New History Snapshot produces error

SQL Server 2016 SP1 on Windows 10 Professional on a Dell Laptop

Report Manager (Web Portal URL) has reports embedded, folders etc. Report server; Report Manager and Report Builder 3 are all working.

Trying to create a New Subscription or New History Snapshot produces error. Report user is administrator of SQL Server 2016 SP1 as well as installer. The current user is also the Reporting Services Administrator. The report is neither a linked report not does it have parameters. Data Source credentials are that the report author who is also the current user as well as the server installer on the Windows 10 Pro laptop.

Here are the messages. Some MSDN content on MSDN site related to this subject is outdated and does not relate to some of the procedures mentioned in MSDN articles.

---

Jayaram Krishnaswamy

SQL Server 2016 SP1 repair does not fix Reporting Services Problem

Problem with SQL Server Reporting Services 2016 SP1 is still unsolved. Two things that prevent me from going further is that I cannot use the SQL Server Reporting Services 2016 SP1 because:

Microsoft Edge is not displaying the Report Server.
Web Portal URL is not working and this happens to be the front-end for the Reporting Services.

I assumed that probably there is something wrong with the SQL Server 2016 Sp1 and I went through a repair process:

It appears there is no problem with the Reporting Services 2016.

SQL Server Management Studio for SQL Server 2016 is a separate Install

Recently downloaded SQL Server 2016 SP1 and to my surprise I did not find SQL Server Management Studio. This is by design that Microsoft wants user to download it separately, it is free however. In addition to SSMS (v17.0) lots of other components related to SQL Server vNext CTP 2.0 are also installed.

Another surprise, SQL Server 2016 SP1 now installs the SQL Server Configuration Manager.

How's that for confusion!


Install SSMS (Release 17) from this link:
https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms

 SQL Server 2016 SP1 installation installs the following:

SQL Server Management Studio installs the following shown by arrows:

These are the two tools in SQL Server Tools 17:

These are tools for the next version. You may want to get rid of them if you do not need (perhaps?).

Finding installed features after SQL Server 2016 is installed

The best way to find what features are installed when you installed SQL Server 2016 is to run a discovery report. The discover report table has the following columns:

Product
Instance
Instance ID
Feature
Language
Edition Type
Version
Clustered or not
Configured or not

How do you run a discovery report?

You can use the command line tool to run the report.
Run the following from command line prompt:

C:\Program Files\Microsoft SQL Server\130\Setup Bootstrap\SQLServer2016>setup.exe /action=RunDiscovery

This generates the discovery report and places it in the log folder.

C:\Program Files\Microsoft SQL Server\130\Setup Bootstrap\Log

This generates the report (an htm file) in the following directory:
C:\Program Files\Microsoft SQL Server\130\Setup Bootstrap\Log\20170304_105325>

Where 20170304_105325 is the last session (or the session for running the discovery)

 You may now go to the above directory and open the htm file in your usual browser (the whole screen is not shown).

Using a Bacpac file to construct the Wide World Importers Database

In my previous post I showed you how easy it is to bring the Wide World Importers (WWI) database into a named instance of SQL Server 2016 SP1 on your computer(desktop / laptop).

In this post I describe how you may construct the database on the named instance using a BacPac file.
If you have not used this kind of file please review the following link here.

In what follows I describe using the .bacpac file to construct the WWI database
Go here:
https://github.com/Microsoft/sql-server-samples/releases/tag/wide-world-importers-v1.0

Get this bacpac file:
Azure SQL Database Standard tier

    WideWorldImporters-Standard.bacpac - standard edition OLTP sample database in bacpac format. For Azure SQL Databases in the Standard tier.

Download the 58.4 MB file from the above site.


SQL Server 2016 SP1 like the other versions has a wizard to bring the database using a .bacpac file such the one you downloaded.

You invoke the wizard as shown in the next image and follow through.

bakpacWWI_01.png

Click Import Data-tier Applicaiton... to open the window shown in the next image.  Read the steps in this introductory page.

bakpacWWI_02.png

 Click Next. You click the Browse... button to locate the  indicated file. This is the location to which I saved the WideWorldImporters-Standard.bacpac file. 
It does not matter it is for Azure SQL Database.

bakpacWWI_03.png

After getting the file location from the location as shown in the next image proceed to next step.

bakpacWWI_04.png


After getting the file location in the wizard's interface (the image one above the previous) click Next.

bakpacWWI_05.png

 Just verify that the location for files are what you want. Herein defaults are accepted. You could browse and park elsewhere.

The database name was changed to WWI_Bacpac.
 
Click Next. The Summary of processing is presented as shown.

bakpacWWI_06.png


Click Next. The next four images shows the progress captured in images. Indexes are dropped and created, etc.

 bakpacWWI_07.png

 bakpacWWI_08.png

 bakpacWWI_09.png

bakpacWWI_10.png

Object explorer refreshed - last processing step.

The WWI database is imported successfully.

Here is the final verification of the import into the named instance.

 

bakpacWWI_10.png

Install SQL Server 2016 SP1 and get productivity enhancements

SQL Server 2016 Service Pack 1 is released  and now generally available(GA).

According to the above release blog:

Many of the features (listed in the table here) available in Enterprise Edition are now available in several other editions as well after installing the SP1 (Standard, web, express and LocalDB).

SQLSrvrSp1_00

Note that Change Data Capture requires SQL Server Agent not available in Express and In Memory OLTP not possible in LocalDB due to security reasons

DBCC CLONEDATABASE added supports cloning of CLR, Filestream/Filetable, Hekaton and Query Store objects are available after installing SP1

DBCC CLONEDATABASE  (source_database_name, target_database_name)    –– Default CLONE WITH SCHEMA, STATISTICS and QUERYSTORE metadata.
DBCC CLONEDATABASE  (source_database_name, target_database_name) WITH NO_STATISTICS                                                                                               –– SCHEMA AND QUERY STORE ONLY CLONE
DBCC CLONEDATABASE  (source_database_name, target_database_name) WITH NO_QUERYSTORE                                                                                              –– SCHEMA AND STATISTICS ONLY CLONE
DBCC CLONEDATABASE  (source_database_name, target_database_name) WITH NO_STATISTICS,NO_QUERYSTORE                                                          –– SCHEMA ONLY CLONE
New Create/Alter supports modifying and deploying database objects such as
Stored Procedures, 
Triggers, 
User–Defined Functions, and 
Views

This is just a short list from more than a dozen others. Make sure you visit the link referenced at the top.

The SQL Server 2016 Enterprise specific sample database, WorldWorldImporters can now be used in Standard and Express editions.

Download SQL Server 2016 SP1 here .

If you are just starting out on Reporting Services follow the instructions here:

https://hodentekmsss.blogspot.com/2017/05/installing-sql-server-2016-sp1-for.html