HodentekMSSS

When Identity Security Becomes a Wall — Not a Shield

2025-08-02T18:16:00.000-07:00

After a breach that forced a reset of my digital identity, I hit a roadblock I never anticipated: multi-factor authentication (2FA) locked me out of critical Microsoft services with no reliable way to prove who I was.

Despite years of interaction, billing history, and documented correspondence, access couldn’t be restored. Support channels were opaque. Recovery methods? Virtually nonexistent.

🧩 The Fallout

This isn’t just a tale of frustration — it’s a wake-up call for anyone who depends on digital platforms for professional continuity. Here's what made this situation particularly troubling:

2FA mechanisms ignored reset conditions and created a closed loop
Microsoft’s support structure lacked escalation flexibility for identity restoration
Existing billing relationships didn’t help validate re-entry
Submission of supporting materials was not possible due to access barriers
Communication was throttled by the very safeguards meant to protect users

📁 Appendix Overview (Bullet Format)

Though I’ve withheld raw screenshots for privacy, the underlying evidence includes:

Email chains across multiple support tiers
Billing confirmation across service subscriptions
Failed attempts to upload documents for verification
Timeline logs of authentication attempts
Chat transcripts documenting escalation effort
Account alerts post-identity reset
Case numbers and references from support tools
License access history and dashboard exclusions
Anomalies in MFA re-enrollment
Failed access attempts after password and device reset
Time-based snapshot of support delays and breakdowns

🔄 What’s Next?

This blog isn’t about placing blame — it’s about demanding resilience. If identity protection policies don’t account for edge-case scenarios, platform continuity suffers.

Lesson learned: Security tools should protect users with them, not from them.

What is unique to StarSQL ODBC connectivity?

2025-03-30T14:14:00.000-07:00

StarSQL is a database driver designed to facilitate connectivity between client applications and IBM DB2 databases. Specifically, StarSQL acts as an ODBC (Open Database Connectivity) or JDBC (Java Database Connectivity) driver, enabling seamless access to DB2 databases from various platforms and applications.

It essentially serves as a bridge, allowing users to query and interact with DB2 databases without requiring additional middleware or proprietary gateways.

How different is it from a generic driver like MSODBC?

StarSQL

Advantages:

Optimized for IBM DB2: StarSQL is specifically designed to work seamlessly with DB2, offering better performance and compatibility.

Advanced Features: Includes proprietary enhancements for DB2, such as support for DB2-specific SQL functions and features.

Security: Provides robust encryption options like SSL for secure data transfer.

Disadvantages:

Limited Scope: StarSQL is tailored for DB2, so it may not be as versatile for other database systems.

Cost: It might be a paid solution, which could be a consideration for budget-conscious projects.

MSODBC

Advantages:

Versatility: MSODBC is a general-purpose driver that supports a wide range of databases, including DB2.

Cost-Effective: Often included with Microsoft environments, making it a budget-friendly option.

Familiarity: Many developers are already familiar with ODBC, reducing the learning curve.

Disadvantages:

Performance: May not be as optimized for DB2 as StarSQL, potentially leading to slower data transfer.

Limited DB2-Specific Features: Lacks the advanced DB2-specific functionalities that StarSQL offers.

Which is Better, StarSQl or MS ODBC?

If your project heavily relies on DB2 and you need advanced DB2-specific features, StarSQL is likely the better choice.

On the other hand, if you're looking for a more general-purpose, cost-effective solution and can compromise on DB2-specific optimizations, MSODBC might suffice.

Happy migration!

Is Vector Search in SQL Server 2025 the Key to Unlocking New Data Insights?

2025-02-20T02:30:00.000-08:00

The advent of AI has ushered in ground breaking changes in most areas of technology. AI is synonymous with a humongous amount of data, data of all types including text, image, audio and video. The databases of the future needs to cater to this changing vista of data and yet compatible with existing technologies.

Microsoft SQL Server has always catered to the technological changes and along its developmental path has introduced many data types to handle diverse data varieties and in recent years has introduced data types such as, "Json', "Stock" and "Geographical" data types. These address the stock market and the geographical data that accompanies all global operations.

In addition to data types, it has also changed itself to accommodate not only relational data but also non-relational NoSQL type of data using integration with polyphase technology.

Vector Data Type

SQL Server 2025 has introduced a new **vector data type** designed to store vector data optimized for operations like similarity search and machine learning applications. This is quite a leap for a relational database management system (RDBMS), as it traditionally handles structured data but can handle NoSQL Data.

How is Vector datatype stored in the SQL Server 2025?

A vector in SQL Server is typically stored as a JSON array of floating-point values. Here's an example of how you might define and use a vector in SQL Server:

-- Create a table with a vector column

CREATE TABLE dbo.vectors (

id INT PRIMARY KEY,

v VECTOR(3) NOT NULL

);

-- Insert data into the table

INSERT INTO dbo.vectors (id, v) VALUES

(1, '[0.11, 2, -35]'),

(2, '[-100.2, 0.0123, 9.876]'

-- Select data from the table

SELECT * FROM dbo.vectors;

This table with two columns has a column of vector data type not null. The vectors are [0.11, 2, -35] and [-100.2, 0.0123, 9.876]

The columns store three dimensional vectors with each dimension stored as a single-precision floating point (float type).

Vector Data Type is not available in 2022

The vector data type is not available in SQL Server 2022. You may have to store vector data type in a JSON column or something customized.

Image Processing and AI

In recent times image processing has been intensely studied. One may ask how to store the dimensional representation of an image. Well, images can have a very high dimensional representation.

• Grayscale Image: A 100x100 pixel grayscale image can be represented as a 10,000-dimensional vector (100x100 = 10,000).

• Color Image: A 100x100 pixel color image with RGB channels can be represented as a 30,000-dimensional vector (100x100x3 = 30,000).

In SQL Server 2025, the maximum number of dimensions that can be stored using the vector data type is 1998. Each element of the vector is stored as a single-precision (4-byte) floating-point value.

Can image dimensions be stored in SQL Server 2025?

The short answer for storing image dimensions in SQL Server is that it is not possible to store image dimensions without reducing the number of dimensions. There are techniques to reduce the dimensions with various pay offs in quality. We shall discuss them in a later post using a programming language such as python with the use of certain libraries.

Going forward in populating vector data type into the server the SQL Server Integration services can be used to preprocess the image data and then populate the database. This will also be for a future post.

If you are looking for working with databases, stay in and learn from over 15 years of blogging on databases here. http://hodentekMSSS.blogspot.com

You may also have a look at my database related books:

Encryption and connecting to SQL Server using SSMS

2025-02-01T19:43:00.000-08:00

This post is describes connecting to SQL Server 2022 using the SQL Server Management Studio and the encryption of connection.

For installing SQL Server 2022 and Microsoft SQL Server Management Studio go here[https://hodentekmsss.blogspot.com/2024/09/installing-sql-server-2022-on-laptop.html],here [https://hodentekmsss.blogspot.com/2024/09/customized-installing-sql-server-2022.html] and here[https://hodentekmsss.blogspot.com/2025/01/install-latest-version-of-sql-server.html].

This post assumes you completed the above two tasks and explores the SQL Server 2022.

Type SQL Server Management Studio App in the search box and click the app in the Search's pop-up.

You should be seeing this image with the Studio interface in the back of the Connect to Server window.

The Server Type: Database Engine

The Server Name: Computer name/SQL Server Instance name

Authentication: Windows Authentication

User name: greyed Computer Name/Computer admin name

These are all the choices you made during installation. Refer to the earlier links in this post.

Connection Security: Early versions did not have this Encryption in this screen and it has three options:

1. Mandatory

2. Optional

3. Strict (SQL Server 2022 and Azure SQL)

This is followed by a checkbox to trust the Server Certificate. Place check mark here and click Connect.

This opens up the Object Explorer, the navigation hub of the SQL Server Management Studio App.

In earlier versions you would have come across Always Encrypted feature. This feature ensures that sensitive data is always encrypted, even when it is being processed by the SQL Server. It uses client-side encryption to protect data before it is sent to the server, ensuring that the data remains encrypted throughout its lifecycle.

Mandatory Encryption: This option ensures that the connection to the SQL Server is always encrypted. It uses Transport Layer Security (TLS) to encrypt the data transmitted between the client and the server. This is the default setting in SSMS 20 and later versions.

Optional Encryption: With this option, the connection can be encrypted if the server supports it, but it is not required. If the server does not support encryption, the connection will proceed without encryption. Note: This means that all features of the server can still be accessed, but the data transmitted between the client and the server will not be encrypted, potentially exposing it to security risks.

Strict Encryption: This option is available in SQL Server 2022 and Azure SQL. It enforces the highest level of encryption and does not allow the connection to proceed if the server does not support encryption. This ensures that the connection is always secure.

These encryption options are related to SQL Server encryption in the following ways:

Transport Layer Security (TLS): All three options use TLS to encrypt the data transmitted between the client and the server. TLS is a widely used protocol for securing data in transit and is supported by SQL Server.

Encryption Hierarchy: SQL Server uses a hierarchical encryption and key management infrastructure to protect sensitive data. This includes the use of encryption keys, certificates, and algorithms to secure data at rest and in transit.

The encryption options in SQL Server Management Studio (SSMS) have evolved over time. The "Always Encrypted" feature, which provides client-side encryption to protect sensitive data, was introduced in SQL Server 2016 and became available in SSMS starting with the October 2015 preview version (13.0.700.242) . This feature allows users to encrypt data in their databases without the database system having access to the encryption keys, ensuring that sensitive data remains protected even if the database is compromised.

wyatt from atlas

Mandatory Encryption: This option ensures that the connection to the SQL Server is always encrypted. It uses Transport Layer Security (TLS) to encrypt the data transmitted between the client and the server. This is the default setting in SSMS 20 and later versions.

Optional Encryption: With this option, the connection can be encrypted if the server supports it, but it is not required. If the server does not support encryption, the connection will proceed without encryption. Note: This means that all features of the server can still be accessed, but the data transmitted between the client and the server will not be encrypted, potentially exposing it to security risks.

Strict Encryption: This option is available in SQL Server 2022 and Azure SQL. It enforces the highest level of encryption and does not allow the connection to proceed if the server does not support encryption. This ensures that the connection is always secure.

These encryption options are related to SQL Server encryption in the following ways:

Install the latest version of SQL Server Management Studio 20.2

2025-01-14T22:29:00.005-08:00

This is the latest version of SQL Server Management Studio [build no:20.2.30.0, released July 9,2024]. Note: it is not an upgrade to SQL Server 19.x

SQL Server Management Studio (SSMS) is an integrated environment for managing any SQL infrastructure, from SQL Server to Azure SQL Database.
SSMS provides tools to configure, monitor, and administer instances of SQL Server and databases.
Use SSMS to deploy, monitor, and upgrade the data-tier components used by your applications and build queries and scripts.

You may download the executable from this link:

https://learn.microsoft.com/en-us/sql/ssms/release-notes-ssms?view=sql-server-ver16

You will find the following (SSMS-ENU) in your download:

You can doble-click the application to start installing. It takes a very short time to install as shown in these screenshots:

Click Install and installation begins.

These are some new features of the SQL Server Management Studio (from the Microsoft documentation).

Connecting to an existing SQL Server 2022:

Kindly follow the links for installing the SQL Server 2022:

https://hodentekmsss.blogspot.com/2024/09/installing-sql-server-2022-on-laptop.html

https://hodentekmsss.blogspot.com/2024/09/customized-installing-sql-server-2022.html

If your SSMS 20.2 installation is successful you should be seeing in the Windows Control Panel several additional components to ensure a smooth and comprehensive experience in using SQL Server Management Studio:

Microsoft Visual Studio Tools for Applications 2019: This is necessary for certain features within SSMS that rely on Visual Studio's development environment.
SSMS 20.2: The main application for managing SQL Server instances.
Microsoft Help Viewer: Provides access to documentation and help files within SSMS.
Microsoft OneDrive: This might have been included to facilitate cloud storage and synchronization of your files.

The SSMS20.2 installation also adds the launch program for the SSMS that you can find in the Search as shown here:

C:\ProgramData\Microsoft\Windows\Start Menu\Programs\Microsoft SQL Server Tools.

This folder has the following files:

Shortcut to the SSMS 20.2 launch

Shortcut to Analysis Services Deployment Wizard 20

Performance tools folder

You can doble click to launch from this location or on the Search results:

The SSMS 20.2 above when clicked launches the application and the SSMS user interface is displayed as shown:

Although the program connects to the installed version of SQL Server it is looking for a trusted connection by way of a certificate. There is a check box below Encryption (that has three options) shown above.

If you do not check this box and try to connect you get the following exception:

If you do place a checm mark saying you trust and then connect you get the following display:

This connection is now OK and the SSMS is OPEN FOR BUSINESS!!!

If you are looking for working with databases, stay in and learn from over 15 years of blogging on databases here. http://hodentekMSSS.blogspot.com

You may also have a look at my database related books:

What is SQLAlchemy?

2025-01-11T20:32:00.002-08:00

SQLAlchemy a toolkit for Python programming language. You can build full blown database applications with it and use the full range of the SQL Language. It is also a Object Relational Mapper(ORM).

SQLAlchemy is what you need (libraries and other tools) for building data-centric apps using Python and using other libraries the possibilities are limitless including AI and Machine Learning.

SQLAlchemy and connecting to data:

The connectors are elements that connect your application to data but in the case of SQLAlchemy they are called 'database dialects'. There are database dialects for each kind of database. These are the most common kinds:

SQLite: A lightweight, disk-based database that doesn’t require a separate server process.

https://hodentekmsss.blogspot.com/2016/09/sqlite-using-visual-studio-community.html

PostgreSQL: A powerful, open-source object-relational database system.

https://hodentekmsss.blogspot.com/search?q=postgresql

MySQL: A widely used open-source relational database management system.

https://hodentekmsss.blogspot.com/2024/11/install-latest-mysql-and-improve.html

Oracle: A proprietary database management system by Oracle Corporation.

https://hodentekmsss.blogspot.com/2013/11/a-collection-of-sql-server-related.html

Microsoft SQL Server: A relational database management system by Microsoft.

https://hodentekmsss.blogspot.com/2024/11/sql-server-2025-ready-to-go.html

MariaDB: A community-developed fork of MySQL.

https://hodentekhelp.blogspot.com/2015/11/how-do-you-access-mariadb-in-xampp.html

These connectors make it easy to connect SQLAlchemy to various database systems, ensuring compatibility and flexibility in developing data-centric applications.

If you are looking for working with databases, stay in and learn from over 15 years of blogging on databases here.

You may also have a look at my database related books:

You may

SQL Server 2025 ready to go

2024-11-23T14:11:00.000-08:00

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?

2024-11-17T20:04:00.000-08:00

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

2024-11-13T12:10:00.000-08:00

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?

2024-09-22T21:15:00.000-07:00

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

2024-09-15T21:07:00.000-07:00

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

2024-09-08T18:40:00.000-07:00

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

2024-09-08T18:30:00.000-07:00

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?

2024-06-29T21:00:00.000-07:00

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

2024-06-01T20:02:00.000-07:00

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?

2024-05-29T20:48:00.000-07:00

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

2024-05-22T20:28:00.000-07:00

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.

2024-05-21T20:15:00.000-07:00

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.

I am not paying this invoice. I am disregarding it, because....

2023-09-25T13:49:00.001-07:00

I am not paying this invoice because MICROSOFT has not provided me with a satisfactory way to access my account since June of this year despite my various attempts. The whole problem arose out of my identity being stolen and its consequences. These are documented in these links.

Hodentek: Identity theft and two-factor authentication (or Two-factor HELL)

HodentekHelp: Is there a workaround for the two-factor verification problem?

HodentekHelp: Identity theft and two-factor authentication (or Two-factor HELL)--Part 2

Here is an invoice that Microsoft emailed to me. I can reply to this email but it does not reach Microsoft.

I also have a bunch of emails that either do not reach Microsoft or it does not care to reply.

If I choose to see my invoice, I get this screen,

This is my old phone number which was changed when my identity was stolen. The More information is just a circular reference.

Storing and Processing Big Data with SQL Server

2022-08-03T20:42:00.007-07:00

Here is a reasonable article comparing SQL vs. NoSQL. Here you can also look up the differences between RDBMS and Document Databases.

https://phoenixnap.com/kb/sql-vs-nosql

While Amazon has its own DocumentDB, MongoDB is used in a lot of places (Forbes, Toyota, etc) and Amazon's DocumentDB is compatible with MongoDB.

Of course, Microsoft's SQL Server is a mature product and it can even handle BigData using Polybase virtualization. You can query data from any SQL Server, Oracle, Teradata, MongoDB, and other data sources using external tables.

https://docs.microsoft.com/en-us/sql/big-data-cluster/big-data-options?view=sql-server-ver16

Connectivity to HDFS now uses published REST APIs instead of the Java Hadoop client. all you need to do is to configure connectors while configuring the AZURE Storage.

Here is a schematic from Microsoft's documentation of BigData storage and processing in the Microsoft platform.

Also, storing data in itself is not sufficient and Microsoft has POWER BI which also visualization of data from a huge number of database products. It is hard to beat Microsoft at this game.

I am somewhat slanted towards Microsoft due to my association with Microsoft database products for a long time. I have not received any remuneration from Microsoft for this post.

What is the latest MySQL Version available?

2020-02-11T19:16:00.000-08:00

The latest version for Windows (x86) version of MSI installer can be found here:

https://dev.mysql.com/downloads/windows/installer/

Installing SQL Server Management Studio v 18.4

2020-01-24T19:40:00.001-08:00

Since SQL Server 2016, SQL Server Management is not installed when you install SQL Server. It will be a separate install.

You can download the latest SQL Server Management Studio v18.4 (the most recent version) from here:
https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-ver15

It is backward compatible with the earlier versions.

Double click the downloaded file (SQL2019-SSEI-Dev.exe) to begin the installation.

Click Install. It takes a little while and gets installed easily.

You should see this in your Windows Display of programs.

These programs get added to your Control Panel.

When you launch the SQL Server Management Studio you may get this display. Since there was an existing SSMS v 17.x was present, this display came up.

The user settings from v17 were imported. You could also import later after a default installation.

The SQL Server Management Studio opens displaying the Object Browser. It assumes Windows Authentication with the current user as server administrator.

The version information is as seen in the following screen:

PowerShell cmdlets version that works with (compatible) SQL Server 2019

2020-01-17T18:26:00.001-08:00

PowerShell is in continuous development mode. New challenges require updating of software to accommodate the changes. This is the nature of such things. PowerShell cmdlets also follow this natural course and we have many versions.

I had an older version, and when I tried to launch contextually from SSMS v 8.4, I got this response:

In order to get the version you want, you may access this link:

SQL Server with PowerShell

Here is a list from the above site:

Scripting Options in SQL Server Management Studio v 18.4

2020-01-17T11:51:00.002-08:00

The Scripting options can be accessed from Tools: Options in the SQL Server Management Studio v18.4.

In the Options window drill-down on SQL Server Object Explorer | Scripting as shown.

Expand the node, General scripting options to display the following:

Expand the node, Object scripting options to display the following:

Expand the node, Table and view options to display the following:

Expand the node, Version options to display the following:

In each version of SSMS, the Options window is slightly different or arranged differently.

Get head start in Basic SQL at PCATT

2020-01-15T11:05:00.000-08:00

A new session is going to start soon. Sign-up here.

Microsoft SQL Azure Database: SQL Server in Microsoft Cloud
Windows PowerShell for SQL Server

My sincere thanks and best wishes to my students in the previous meets of this course.