SQL Server 2025 ready to go

 I have not yet done looking at SQL Server 2022, SQL Server 2025 is ready to go. Microsoft is indeed relentless! 

Microsoft announced SQL Server 2025 at the Microsoft Ignite event in Chicago. Now , AI spans all over the Microsoft Eco system and that includes SQL Server. This time around Microsoft introduced SQL Server 2025 and SQL database on Fabric.

SQL Server is integrated with AI with:

• Vector search
• Vector Indexing using DiskANN
• T-SqL functions to support generation embedding and text chunking

Another useful feature added is the support for calling external REST APIs into SQL Server, a feature that existed in AZURE SQL Database. This AI feature makes it easy to store your AI models on premises and use them right away.

SQL Server 2025 will have optimized locking and will support native JSON datatype and improvements to Always on Availability groups. Also,GIT support for Microsoft Management Studio was announced. With this SSMS has now what is called a dark mode! 

Furthermore, a new product was announced, the SQL Database on Fabric. Microsoft Fabric is its unified analytics platform that integrates various datatools under one title. It is supposed to simplify data analysis and insights for both professionals and business users. 

Hey, Google does not have one such thing under a single title, but it has various functionalities dispersed in its cloud.

Let's move on!

More here:

https://www.microsoft.com/en-us/sql-server/blog/2024/11/19/announcing-microsoft-sql-server-2025-apply-for-the-preview-for-the-enterprise-ai-ready-database/

Do you know what you installed while custom installing SQL Server 2022?

 When you install any software in Windows OS, the key information gets into the Registry and some of which you can look up in the control panel.

For example, I Custom installed SQL Server 2022 on 9/14/2024. I see the following entries in the Control Panel/Programs/Programs and Features.

Browser for SQL Server 2022----------9/15/2024

Microsoft OLEDB Driver for SQL Server-9/15/2024

Microsoft SQL Server Setup(English)--9/15/2024

Microsoft SQL Server 2022(x64Bit)--9/14/2024

However, the installation does more than this.

Browser for SQL Server 2022: This service helps clients find SQL Server instances on the network. It’s essential for managing multiple SQL Server instances on a single machine1.

Microsoft OLEDB Driver for SQL Server: This driver allows applications to connect to SQL Server databases using OLEDB, a standard API for accessing database management systems2. There is an associated OLEDB Manager GUI.

Microsoft SQL Server Setup (English): This entry represents the setup files and installation resources for SQL Server 2022. It includes the necessary tools and configurations for installing and managing SQL Server.

Microsoft SQL Server 2022 (x64Bit): This is the main SQL Server engine, which includes the core database services and features. It’s the primary component that handles database operations.

SQL Server 2022 like its predecessors a Windows service. It is a good practice to take a look at what it is.  Therefore, all SQL Server Services can be found in the Windows Services listing as shown here (just pop-up the Services from Control Panel, or just type Services in the Windows Search on the desktop). It is too important a tool and the used should get a handle on this.

There are couple of services that came up with our custom installation we discussed earlier.(link here).The SQL Server related services are all shown inside the red rectangle. If you are unfamiliar with this screen, it is important to understand how things work on Windows.

Let us just look at the SQL Server instant we installed earlier. 

This service is described on the left with your cursor on the service in the left. This service as seen in its properties provides storage, processing and controlled access of data for rapid transaction processing. If you encounter an error that the service is not running etc. you should immediately get to this screen and start the service from this screen.

Note that from this screen, you can not only start but stop, restart (if stopped),or temporarily pause the service. In the Services window shown above place your cursor and make a left click and from the drop-down list, pick Properties.

The Log On tab shows the present long on credentials of the Regency Server. You can make changes in this screen, if you want to.

 

This tab Recovery shows the steps you can take in case the service fails.

The Dependencies shows the interconnectedness of services, system groups etc. The four components shown at the bottom of the next screen all depend on SQL Server.

In earlier versions of SQL Server that I worked with the SQL Server Configuration Manager was installed when the SQL Server was installed and there used to be a shortcut in even earlier versions inthe start menu. However, this shortcut was missing in SQL Server 2016. The link explains why it was moved out of the start menu.

https://hodentekmsss.blogspot.com/2016/10/sql-server-configuration-manager.html

In 2022, there is no such shortcut or link. 

SQL Server Configuration Manager is a very important tool that you may have to access to configure network protocols, manage network connectivity of clients connecting to the server. In SQL Server 2022(16.x) later you can also manage Azure extension for SQL Server.

This link https://learn.microsoft.com/en-us/sql/relational-databases/sql-server-configuration-manager?view=sql-server-ver16, shows where it is located for various versions of SQL Server.

Just type in SQLServerManager13.msc in your Run command (that can be invoked from Search box in Windows 11) as shown,

On clicking OK, the Sql Server Configuration Manager pops-up as shown.

In future posts we look at logging on to the server and other activties.

 

Install the latest MySQL and improve security

The latest MySql Version now presently as of Oct 15, 2024 is 8.4.3 , which was released as an LTS (Long Term Support) version: 

These are the earlier issues 

8.4.2: Released on April 30, 2024, this is the latest LTS version. 

8.4.0: Released on April 30, 2024, this LTS release includes a default setting that rejects attempts to use a nonstandard key as a foreign key. 

 In general, MySQL 8.0 introduced several performance-enhancing features, such as:

Improved query optimizer   

Faster JSON handling   

Enhanced security features 

Better scalability and high availability options   

It is always a good practice to work on the latest versions for reasons of improved and optimized performance and security

What is SQLOISIM? What is it used for in SQL Server?

SQLIOSIM is a tool for simulating SQL Server IO. SQLIOSIM performs reliability and integrity tests on the disk systems that SQL Server utilizes for data and log files. 

It simulates,

Read

Write

Checkpoint

Backup

Sort and 

Read-ahead activities of MS SQL Server.

Main objective of SQLIOSIM is to test the reliability of the underlying I/O subsystem even before installing SQL Server. This proactive tool helps preparing well for the installation. SQL Server installation is not needed for using it as it does not interact with the SQL Server. If an installation exists, using it may overwrite the files. It is not a tool for data security. It is an upgrade over the previous SQ7IOStress utility.

It is located in the Program Files/Microsoft SQL Server/MSSql16.<Named Instance>MSSQL/BINN folder of your installation.

In the installation described here, there are two SQLIOSIM related files in the BINN folder.

SQLIOSIM Application (SqLIOSIM.exe)----GUI

SQLIOSIM DOS file (SQLIOSIM.com)--DOS 

For using SQLIOSIM.com you need to configure the SQLIOSIM configuration file.

What if you cannot locate the file?

If the file is not found as in this installation, it can be found in GitHub. However, it is not found on the GitHub and one may need to go to Microsoft or raise the issue. As we see later, there may be a way out using the GUI.

https://github.com/microsoft/mssql-support/blame/master/sqliosim/Readme.md

The SQLIOSIM gui can be launched using the SQLIOSIM.exe application in the BINN folder.

Double click the SQLIOSIM application in the BINN folder to launch the GUI.

Click SIMULATOR t open the Simulator. Simulator->Configure will open the Files and Configuration GUI as shown.

From here on it can be used to configure the .mdx and .ldx file locations, size  and max size and Increment parameters on the computer and test patterns for integrity testing can be carried out. Do not choose the existing .mdx and .ldx file locations as they will be overwritten during testing.

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

 

Preparatory steps for a Custom Install of SQL Server 2022

 SQL Server is a comprehensive software that works with a variety of Microsoft Software such as Reporting Services, EXCEL, 

It is assumed that you are coming here from the following link:

https://hodentekmsss.blogspot.com/2024/05/installation-of-latest-version-of-sql.html

On executing SQL2022-SSEI-Dev.exe (4191 KB)file. This file gets displayed as shown.

Choose Custom edition.

Accept the target location, you may also choose. Clicking Install downloads the installation package.

  Installation begins.

SQL Server Installation Center opens as shown.

 Invest some time in going through this screen before proceeding further.

These are the detailed screens you encounter:

Planning

Installation

Maintenance

Tools

Resources 

Advanced   

Options

The installation center guides you through the process, step by step till you arrive at finishing the installation.

Here is a detailed description of installing SQL Server 2016 Developer Edition. 

I used this installation in writing my books on SQL Server products.

Installing SQL Server 2022 on a laptop - Part 1

This is where you really start looking installing SQL Server 2022 on a laptop, or a computer. This is in the planning stage of the installation. If you are looking at installation of SQL Servers there are a number of posts for other versions here.

It is possible to install SQL Server 2022 on a laptop as long as it satisfies the hardware and software requirements.

These are the hardware requirements from Microsoft site:

Component	Requirement
Storage	SQL Server requires a minimum of 6 GB of available hard drive space. Disk space requirements vary with the SQL Server components you install. For more information, see Hard Disk Space Requirements later in this article. For information on supported storage types for data files, see Storage Types for Data Files.
Monitor	SQL Server requires Super-VGA (800x600) or higher resolution monitor.
Internet	Internet functionality requires Internet access (fees can apply).
Memory 1	Minimum: Express Editions: 512 MB All other editions: 1 GB Recommended: 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	Minimum: x64 Processor: 1.4 GHz Recommended: 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

1 The minimum memory required for installing the Data Quality Server component in Data Quality Services (DQS) is 2 GB of RAM, which is different from the SQL Server minimum

For example I have a Surface Pro older version with the following specs:

Processor 12th Gen Intel(R) Core(TM) i7-1265U   2.70 GHz

Installed RAM 16.0 GB (15.8 GB usable)

System type 64-bit operating system, x64-based processor

Pen and touch Pen and touch support with 10 touch points

Additionally, the Surface Pro has a display capable of a resolution of 2496 x 1664

This makes it easy to decide that SQL Server 2022 can indeed be run on this laptop. However, we need to allocate 6GB for the database which presently, is more than needed.

The laptop also satisfies the software requirements.

Software Requirements:

• Operating System: Windows 10 version 1607 or greater, or Windows Server 2016 or greater.
• .NET Framework: Minimum operating systems include the minimum .NET framework required.
• Network Software: Supported operating systems for SQL Server have built-in network software.

Does Google's Gemini work with SQL Server without any other downloads?

There are two ways it works. One is commercial, third party and the other is Google's approach.

1. Commercial product: It is called Countersoft Gemini that can be used with SQL Server on your local SQL Server installation. It offers must typical tasks such as performance analysis, change tracking and automating tasks. For using Countersoft, you should install it on your local server. It is not a web based service.

It is not free. There are two prices, $10 monthly per user in the cloud and $850 for 10 users.

Use this link here: https://www.countersoft.com/

2. Google's Gemini: This is the built-in AI assistant for DBMS on the Google Cloud Platform(GCP). It does performance, schema analysis, querying, etc. It is built-in and does not require anything special. However, for SQL Server, you need to have an instance installed in the GCP.

More here: 

https://gemini.google.com/app?utm_source=google&utm_medium=cpc&utm_campaign=2024enUS_gemfeb&gad_source=1&gclid=CjwKCAjw4f6zBhBVEiwATEHFVi_2XLUBnZCVss9YqGvsAFYIth7bzE2rn_4aC0aNY7Q3C4U3UcWa9RoCZe8QAvD_BwE

Unlocking Insights, Automating Decisions, and Transforming Businesses with SQL Server AI

 The world is datacentric and everything moves based on data. SQL Server is a database management system and takes care of every aspect of data, from second to second management by online transaction processing (OLTP) to mining stored, archived data that includes data that streams (OLAP).

Data and Data Science:

Raw data can be enormous and the only way to give meaning and take out value out is the role of analytics. The advanced analytics handlers are the data science professionals. They go through the mountain of data and distil out the most useful, and the most relevant information. The success of a company, or organization is dependent on these professionals. All of these example following use cases and many more not mentioned here may be cited: credit risk assessments; managing customer/employee churn; hospitalization metrices; targeted sales & campaigns, etc.

Data Science using SQL Server:

SQL Server is a very comprehensive, database management system well suited for everything related to data present anywhere from OLTP data to OLAP data. It can be present both on site and in the cloud and integrated with the AZURE cloud which in turn is integrated with a myriad of other data related applications not only from Microsoft but from many other applications. Doing data science with SQL Server results in high value and high returns.

Data scientists can connect to a myriad of databases that can be used to train data and test the machine learning tool at their hands.

Where does AI come in or fit in?

The data scientists access the data from the client's database and combine with data from other sources to develop models using R software for joining the data and filtering based on criteria. They may begin shaping the data by creating extra informational features like new columns, or useful data partitioned or transformed, etc. This data shaping lays the foundation for predictive analytics. Going a step further they can put into operation a plan for the model that applications can use for producing useful outputs in the form of a predictive model.

This just one scenario where data is taken out of database and worked upon to get at the useful information by creating models. How easy is it? Moving large amounts of data in and out of database comes at a cost. Thinking about cost and other contingent aspects like the location of data, the security of data (that was not mentioned so far), the latency involved if geographically separated sites are involved, and not having features of DBMS like indexing, column stores, high availability, etc. one can conclude that it may be prudent to do as much of the filtering and shaping done on the database using all the tools the DBMS can provide instead of working on raw data from a database.

As a result of considerations discussed previously it will be indeed beneficial if data science and AI on the existing database can be carried out prior to moving out data. This allows you to leverage all the inbuilt features of the DBMS previously discussed. If the data involves geographical data these can be handled inside SQL Server's inbuilt data types. If on the other hand data has to be accessed from other data sources outside of Microsoft, the linked source feature of the DBMS can be used. 

The following picture copied from a Microsoft site shows the two ways discussed so far.

Another important consideration is after doing predictive analytics the operationalization of what has been achieved with data science, namely the predictive model. The deployment of this to a production environment can be accomplished by using programming using SQL Server R services in the form of a stored procedure. The predictive model will be stored as varbinary (max) in a database table.

Look forward to more discussions related to SQL Server and AI.

What is the best place to save the SQL Server 2022 Installation media files?

 This is a very important question. when I began using SQL Server for writing as well as for teaching, I used to take this for granted and used to store at the default location. Sometimes, I used to get into difficulty accessing the installation media as it was needed after a long interval and I would have made changes to my directory structure, or moved them to other places. However, it is very important where you save your SQL Server Installation media for a couple of reasons.

What's the need to save?

1. It provides a means for you to reinstall or repair your installation. You cannot do this task, if you do not have your installation media. 

2. For Off-line installations, it is essential to have this media.

3. If you are having multiple installations, it ensures consistency. I used to have this media on a memory stick and have all my students install on their work stations.

What are suitable locations to save the media?

There are couple of options. Choose one that best suits your needs.

1. Local Drive: When you start installing you will find out how much space is needed on your computer's local drive. At a minimum, you need 6 GB of disc space for the custom installation with most options. If you include all extras it is about 8GB. In order to work comfortably, it can be about 10GB depending on the databases you are considering. Herein, some sample databases are assumed. The installation process brings up the space requirements during installation. 

2. On a Network share. I could have set it up on the network share for my students, but I decided to give them a copy, if they wanted to play with it when not connected to the network.

3. If installation is on a virtual machine, an ISO would be suitable.

4. Mapped drive is another option.

5. On the Cloud: Cloud storage is yet another option. However, there may be cost in storge and data transfer involved. Latency is yet another factor to consider. Internet availability is most essential during the installation process.

The installation media folder can have any name of your choice. I usually accept the default name.

The SQL Server 2022 installation center's Option node is where you specify location of the media during installation.

I originally saved this media (SQL2022-SSEI-Dev.exe, 4191 KB) on my Download folder of the local drive.

On this blog (http://hodentekMSSS.blogspot.com), dedicated to SQL Servers you will find installation of SQL Server from SQL Server 2012 onwards. Even earlier ones are on my other blog, http://hodentek.blogspot.com

Installation of the latest version of SQL Server

Download the latest version of SQL Server from here.

There are a couple of download options as you can see on this page from the above link.

If you are a developer, the best option is the Developer version which provides:

• Full featured free edition
• Licensed for use as a development and test database
• It is for non-production use

However, most purposes the SQL Server 2022 Express is adequate. It provides:

• Free edition of SQL Server
• Good fit for desktop development
• Best fit for small server applications.

The SQL Server editions may be installed on Windows, Linux and Docker Containers.

In this blog for purposes of demonstration and use, the Developer edition will be installed on a Surface Laptop 5 with the following features.

Windows 11 Pro, Version 23H2, OS build 22631.3593. It has 16GB installed RAM

Downloading the developer edition begins by clicking the Download button in the above screen. In response you download the executable, SQL2022-SSEI-Dev.exe (4191 KB)file. This file gets displayed as shown.

If you want a quick installation use the BASIC option. 

In blog the Custom option will be used. If you want to differ to another time, you may just download the installation files by choosing Download Media.

Read the details of the SQL Server 2022 Installation Center

If you are concerned about the security of your SQL Server, you do this.

 SQL Server of different versions are still supported and their version and build number are important for securing your SQL Server.

This KB article (321185) brings together and consolidates all build versions and their current support cycle from SQL Server 2005 through the current version. An EXCEL file that can be downloaded provides the SQL Server's Complete version list.

Go here to get it!

Latest updates and version history for SQL Server - SQL Server | Microsoft Learn

One of the worksheets is shown here for your information from the downloaded EXCEL file.

If you are interested in finding what version of SQL Server you are running, just run the following SELECT query:

SELECT @@Version

You may look at the version when you try to use the Object Explorer in the management studio when you try to connect to a version from the drop-down list.

There are other ways to determine the version of SQL Server Version. This blog has posts on many versions that were used in writing some of my books on SQL Server shown hereunder.

However, for a secured SQL Server you should have the latest update to the version you are running.