SQL Server: It’s Already In There

Joshua Isaac Guttman with a lot of help from Bing- Database Consultant and DBA, I still don't know what the "A" in :DBA" means. I Had my own company for 25 years,I've worked for other people. I just prefer Database Guy or Director of Data and Database Services. King of Data will work if you're undecided.

SQL Server: It’s Already In There

We all know and love SQL Sever as a great database at all levels and throughout your enterprise. But did you know the system you know is part of a much larger product? It Includes SQLServer Analytics Services, SQL Server Integration Service, and SQL Server Reporting Service. Yet people seem to want to throw money out of the window to buy expensive third-party that try to replace what you already have. Unfortunately this is a case of RTFM or Read THE FREAKING Manual. Here’s just a short description form bing - you don’t even have to pick up one of those heart dusty books.

Analytics - SQL Server Analytics Services

SQL Server Analysis Services (SSAS) is an analytical data engine used in decision support and business analytics. It provides enterprise-grade semantic data models for business reports and client applications such as Power BI, Excel, Reporting Services reports, and other data visualization tools 1. It was introduced in SQL Server 7.0 as a multidimensional OLAP server 1. Later, in SQL Server 2012, tabular models were introduced to provide a simpler and more flexible way of creating data models.

The workflow of SQL Server Analysis Services includes installing an instance of SQL Server Analysis Services, creating a tabular or multidimensional data model, deploying the model as a database to a server instance, processing the database to load it with data, and then assigning permissions to allow data access 1. To create a model, Visual Studio with Analysis Services projects extension, also known as SQL Server Data Tools or simply SSDT, can be used, choosing either a Tabular or Multidimensional project template 1. The project template contains folders for all of the objects needed in a model. You can use wizards to create all of the basic elements, such as data sources, data source views, dimensions, cubes, and roles 1.

SQL Server Analysis Services supports tabular models at all compatibility levels (depending on version), multidimensional models, data mining, and Power Pivot for SharePoint 1. Models are populated with data from other data systems, usually data warehouses hosted on a SQL Server or Oracle relational database engine 1. Models specify query objects, calculations, and KPIs that encapsulate business logic 1.

To summarize, SQL Server Analysis Services is a powerful tool that provides enterprise-grade semantic data models for business reports and client applications. It has a rich history of development and has evolved over time to provide more flexible and simpler ways of creating data models. It supports tabular models, multidimensional models, data mining, and Power Pivot for SharePoint. The workflow of SQL Server Analysis Services includes installing an instance of

SQL Server Analysis Services, creating a tabular or multidimensional data model, deploying the model as a database to a server instance, processing the database to load it with data, and then assigning permissions to allow data access.

Source: Conversation with Bing, 12/31/2023 (1) SQL Server Analysis Services overview | Microsoft Learn. https:// learn.microsoft.com/en-us/analysis-services/ssas-overview?view=asallproducts- allversions. (2) What's new in SQL Server Analysis Services | Microsoft Learn. https:// learn.microsoft.com/en-us/analysis-services/what-s-new-in-sql-server-analysis- services?view=asallproducts-allversions. (3) An overview of Analytic Functions in SQL Server - SQL Shack. https:// www.sqlshack.com/an-overview-of-analytic-functions-in-sql-server/. (4) Top 75 Interview Questions For SSAS - JanBask Training. https:// www.janbasktraining.com/blog/ssas-interview-questions/. (5) Microsoft Analysis Services - Wikipedia. https://en.wikipedia.org/wiki/Microsoft_Analysis_Services.

Need to Move Data? ETL/ELT/Data Pipeline/Data Conversation: SSIS (SQL Server Integration Services )

SQL Server Integration Services (SSIS) is a Powerful Extract, Transform and Load (ETL/ELT) solution that comes with the Microsoft SQL Server database 2. It is used to extract, integrate, and transform data from a wide variety of sources such as XML data files, flat files, and relational data sources, and then load the data into one or more destinations 5. SSIS is a platform for building enterprise-level data integration and data transformations solutions 5.

The workflow of SQL Server Integration Services includes installing an instance of SQL Server Integration Services, creating an SSIS package, deploying the package to a server instance, processing the package to load it with data, and then assigning permissions to allow data access 1. To create a package, Visual Studio with SQL Server Data Tools (SSDT) can be used, choosing either an Integration Services project template 1. The project template contains folders for all of the objects needed in a package. You can use wizards to create all of the basic elements, such as data sources, data source views, dimensions, cubes, and roles 1.

SQL Server Integration Services includes a rich set of built-in tasks and transformations, graphical tools for building packages, and an SSIS Catalog database to store, run, and manage packages 5. You can use the graphical Integration Services tools to create solutions without writing a single line of code 5. You can also program the extensive Integration Services object model to create packages programmatically and code custom tasks and other package objects 5.

To summarize, SQL Server Integration Services is a powerful tool that provides a platform for building enterprise-level data integration and data transformations solutions. It is used to extract, integrate, and transform data from a wide variety of sources such as XML data files, flat files, and relational data sources, and then load the data into one or more destinations. The workflow of SQL Server Integration Services includes installing an instance of SQL Server Integration Services, creating an SSIS package, deploying the package to a server instance, processing the package to load it with data, and then assigning permissions to allow data access.

Source: Conversation with Bing, 12/31/2023 (1) What is SSIS - SQL Server Integration Services (An Introduction). https:// www.comparitech.com/net-admin/what-is-microsoft-ssis/. (2) SQL Server Integration Services (SSIS) - learn.microsoft.com. https:// learn.microsoft.com/en-us/sql/integration-services/sql-server-integration- services?view=sql-server-ver16. (3) Install SQL Server Integration Services - SQL Server Integration .... https:// learn.microsoft.com/en-us/sql/integration-services/install-windows/install- integration-services?view=sql-server-ver16. (4) SQL Server Integration Services - Wikipedia. https://en.wikipedia.org/wiki/ SQL_Server_Integration_Services. (5) What Are SQL Server Integration Services? - SolarWinds. https:// www.solarwinds.com/resources/it-glossary/ssis-sql-server-integration-services. (6) Install SQL Server Integration Services in Visual Studio 2019. https:// www.mssqltips.com/sqlservertip/6481/install-sql-server-integration-services-in- visual-studio-2019/. (7) Difference between SSDT in VS and Integration Services in SQL Server. https:// stackoverflow.com/questions/56190367/difference-between-ssdt-in-vs-and- integration-services-in-sql-server. (8) SQL Server Integration Services vs Informatica PowerCenter. https:// www.selecthub.com/etl-tools/sql-server-integration-services-vs-informatica- powercenter/.

Reporting: SQL Server Reporting Service

SQL Server Reporting Services (SSRS) is a server-based report generating software system from Microsoft 2. It is part of a suite of Microsoft SQL Server services, including SSAS (SQL Server Analysis Services) and SSIS (SQL Server Integration Services) 2. SSRS allows users to produce formatted reports with tables in the form of data, graph, images, and charts 3. These reports are hosted on a server that can be executed any time using parameters defined by the users 3. You can use SSRS to create tabular, interactive, graphical, and other more complexreports using charts and other reporting elements 4.If you are interested in learning more about SSRS, you can visit the Microsoft Learn website to find out how to install SQL Server Reporting Services, configure your report server, create a report server database, and access the report server with a Windows service 1.

Source: Conversation with Bing, 12/31/2023 (1) SQL Server Reporting Services - Wikipedia. https://en.wikipedia.org/wiki/ SQL_Server_Reporting_Services. (2) SSRS Tutorial: What is SQL Server Reporting Services? - Guru99. https:// www.guru99.com/ssrs-tutorial.html. (3) SSRS – SQL Server Reporting Services - sqlsplus.com. https:// www.sqlsplus.com/ssrs-sql-server-reporting-services/. (4) Install SQL Server Reporting Services - SQL Server Reporting Services .... https://learn.microsoft.com/en-us/sql/reporting-services/install-windows/install- reporting-services?view=sql-server-ver16.

Scheduling - SQL Server Agent

SQL Server Agent is a Microsoft Windows service that executes scheduled administrative tasks, which are called jobs in SQL Server 1. It is a component of Microsoft SQL Server that is responsible for executing and scheduling tasks or jobs in SQL Server 3. SQL Server Agent uses SQL Server to store job information. Jobs contain one or more job steps. Each step contains its own task, for example, backing up a database 1. SQL Server Agent can run a job on a schedule, in response to a specific event, or on demand 1.

SQL Server Agent is used to automate tasks such as backups, alerts, and events 1. It can execute numerous types of jobs, either on a schedule or on-demand 4. SQL Server Agent is a job scheduling agent that ships with SQL Server 4. It can be used to execute scripts, procedures, packages, or databases as part of a job 5.

If you are interested in learning more about SQL Server Agent, you can visit the Microsoft Learn website to find out how to start, create, and view jobs, steps, schedules, alerts, and notifications using the graphical user interface or T-SQL scripts 15.

Editors Note: I would like to add the SQL Agent is not just for SQL Server: It can run executables, send email, run reports, schedule schedule enterprise wide maintenance., it can run your HA (High Availability), and DR (Disaster Recovery etc. Anything you can do to from a server via ODBC, Command Line, or scripting from any scripting

Source: Conversation with Bing, 12/31/2023 (1) SQL Server Agent - SQL Server Agent | Microsoft Learn. https:// learn.microsoft.com/en-us/sql/ssms/agent/sql-server-agent?view=sql-server- ver16. (2) Understanding the Role of SQL Server Agent in SQL Server. https:// www.sqlservercentral.com/blogs/understanding-the-role-of-sql-server-agent-in- sql-server. (3) Setting Up Your SQL Server Agent Correctly - Simple Talk. https://www.red- gate.com/simple-talk/databases/sql-server/database-administration-sql-server/ setting-up-your-sql-server-agent-correctly/. (4) Introduction to the SQL Server Agent - SQL Shack. https://www.sqlshack.com/ introduction-to-the-sql-server-agent/. (5) SQL Server Agent - Wikipedia. https://en.wikipedia.org/wiki/SQL_Server_Agent. Monitoring - You can see it all in Windows Performance Monitor

Microsoft Performance Monitor is a Windows program that allows system administrators to monitor various system areas and is a useful tool for database administrators as well 2. It can show you information in real-time or from log files for later viewing 2. Performance Monitor is bundled by Windows in Computer Management together with other components like Event Viewer and Device Manager 4.

When you first open Performance Monitor, you see the following:


At the very top level, "Performance" gives you an overview of your system's memory usage, network usage, disk usage, etc. 1. You can right-click on "Performance" and connect to another computer to view a remote computer's performance statistics 1. From the Monitoring Tools icon, you can right-click and launch the Resource Monitor 1. Resource Monitor is another powerful tool that can help you see how your system resources are being used 1. You also have the ability to launch the System Reliability Monitor 1. This utility allows you to see information about software updates and installations. You can also see critical events that occurred and on what day those events occurred. Finally, you can see all of the problem reports that have been sent from your computer by clicking on the "View all problem reports" link at the bottom of the window 1.

The Performance Monitor is primarily for viewing real-time statistics. By default, only one counter is selected; the %Processor Time counter. However, you can add additional counters by clicking on the green plus sign. This will allow you to monitor any counters you wish in real-time 1. While you can see all of the performance counters you like here, the real power of Performance Monitor is found in its ability to capture performance metrics over an elapsed period of time 1. Capturing data over a period of time allows you to see trends, and these trends are what are most useful for determining the overall performance of your system 1. To capture this data, you can create what are called "Data Collector Sets" 1. Data Collector Sets collect data from your system so that you can view changes in configuration information and performance information over a specified period of time 1. There are basically three types of Data Collector Sets: Performance Counter Capture data based on the polling of an event at a specified time interval, Event Traces Capture data based on events that occur rather than based on a specified time interval, and System Configuration Information 1.

If you are interested in learning more about Microsoft Performance Monitor, you can visit the Microsoft Community Hub website to find out how to use Performance Monitor to monitor your system's performance and troubleshoot performance issues 1..

Source: Conversation with Bing, 12/31/2023 (1) Introduction to Windows Performance Monitor for SQL Server. https:// www.mssqltips.com/sqlservertip/6933/windows-performance-monitor-sql-server- introduction/. (2) 11 ways to start Performance Monitor in Windows (all versions). https:// www.digitalcitizen.life/start-performance-monitor-windows/. (3) Windows Performance Monitor Overview - Microsoft Community Hub. https:// techcommunity.microsoft.com/t5/ask-the-performance-team/windows- performance-monitor-overview/ba-p/375481. (4) How to Open the Windows 11 Performance Monitor - MUO. https:// www.makeuseof.com/open-windows-11-performance-monitor/. (5) Windows Performance Toolkit | Microsoft Learn. https://learn.microsoft.com/ en-us/windows-hardware/test/wpt/. (6) en.wikipedia.org. https://en.wikipedia.org/wiki/Performance_Monitor.

Encryption at levels - from SQL Server Backups to making your data private even to this who manage

Encryption is the process of obfuscating data by the use of a key or password. This process can make the data useless without the corresponding decryption key or password. Encryption doesn't solve access control problems. However, it

enhances security by limiting data loss even if access controls are bypassed1.

Microsoft SQL Server provides several encryption options for securing data, credentials, and connection information stored in a server database1. You can use encryption in SQL Server for connections, data, and stored procedures1.

Here are some of the encryption options available in SQL Server:

Transparent Data Encryption (TDE): This feature encrypts the entire database, including the data, log files, and backups12. 2. Always Encrypted: This feature encrypts sensitive data in the application before it is sent to the database1. 3. Dynamic data masking: This feature masks sensitive data to non-privileged users1.

SQL Server uses encryption keys to protect sensitive data. Encryption keys include a combination of public, private, and symmetric keys that are used to protect sensitive data12.

ISource: Conversation with Bing, 12/31/2023 (1) SQL Server encryption - SQL Server | Microsoft Learn. https:// learn.microsoft.com/en-us/sql/relational-databases/security/encryption/sql- server-encryption?view=sql-server-ver16. (2) SQL Server & database encryption keys - SQL Server | Microsoft Learn. https:// learn.microsoft.com/en-us/sql/relational-databases/security/encryption/sql- server-and-database-encryption-keys-database-engine?view=sql-server-ver16. (3) SQL Server Encryption Explained: TDE, Column-Level Encryption ... - Netwrix. https://blog.netwrix.com/2019/06/13/sql-server-encryption-explained-tde- column-level-encryption-and-more/. (4) Configure SQL Server Database Engine for encryption - SQL Server. https:// learn.microsoft.com/en-US/sql/database-engine/configure-windows/configure- sql-server-encryption?view=sql-server-ver16. (5) SQL Server and client encryption summary - SQL Server | Microsoft Learn. https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/sql- server-and-client-encryption-summary?view=sql-server-ver16.

Security Monitoring. - Change Data Capture How do you know what data was changed during an intrusion?

SQL Server Change Data Capture (CDC) is a feature that records activity on a database when tables and rows have been modified 1. CDC utilizes the SQL Server Agent to log insertions, updates, and deletions occurring in a table 1. The column data and essential metadata required to apply these changes to a target

environment are captured for the modified rows and stored in change tables that mirror the column structure of the tracked source tables 1.

CDC makes these data changes accessible to be easily consumed using a relational format 1. Table-valued functions are available for systematic access to this change data by consumers 1. A good example of a data consumer this technology targets is an extraction, transformation, and loading (ETL) application 1. An ETL application incrementally loads change data from SQL Server source tables to a data warehouse or data mart 1.

Before changes to any individual tables within a database can be tracked, change data capture must be explicitly enabled for the database 1. This is done by using the stored procedure sys.sp_cdc_enable_db 1. When the database is enabled, source tables can be identified as tracked tables by using the stored procedure sys.sp_cdc_enable_table 1. When a table is enabled for change data capture, an associated capture instance is created to support the dissemination of the change data in the source table 1.

Source: Conversation with Bing, 12/31/2023 (1) What is change data capture (CDC)? - SQL Server | Microsoft Learn. https:// learn.microsoft.com/en-us/sql/relational-databases/track-changes/about-change- data-capture-sql-server?view=sql-server-ver16. (2) Change Data Capture (SSIS) - SQL Server Integration Services (SSIS). https:// learn.microsoft.com/en-us/sql/integration-services/change-data-capture/change- data-capture-ssis?view=sql-server-ver16. (3) SQL Server CDC (Change Data Capture): 5 Easy Steps | Hevo. https:// hevodata.com/learn/sql-server-cdc/. (4) Change Data Capture for auditing SQL Server - SQL Shack. https:// www.sqlshack.com/change-data-capture-for-auditing-sql-server/. (5) Enable and Disable change data capture - SQL Server. https:// learn.microsoft.com/en-us/sql/relational-databases/track-changes/enable-and- disable-change-data-capture-sql-server?view=sql-server-ver16.

Encrypted All the Time from Everyone - Eleven the people monitoring your databases - Always Encrypted

Microsoft Always Encrypted is a feature designed to protect sensitive data, such as credit card numbers or national identification numbers, stored in Azure SQL Database or SQL Server databases1. Always Encrypted allows clients to encrypt sensitive data inside client applications and never reveal the encryption keys to the Database Engine1. This provides a separation between those who own the data and can view it, and those who manage the data but should have no access - on-premises database administrators, cloud database operators, or other high-privileged unauthorized users1.

Always Encrypted makes encryption transparent to applications. An Always Encrypted-enabled driver installed on the client computer achieves this by automatically encrypting and decrypting sensitive data in the client application1. The driver encrypts the data in sensitive columns before passing the data to the Database Engine, and automatically rewrites queries so that the semantics to the application are preserved1. Similarly, the driver transparently decrypts data, stored in encrypted database columns, contained in query results1.

There are several encryption options available in SQL Server, including Transparent Data Encryption (TDE), Always Encrypted, and Dynamic data masking1. Always Encrypted can be configured to support limited confidential queries on encrypted data, the queries that involve equality comparisons1. For example, point lookup-searches or equality joins. Such queries leverage deterministic encryption1. Secure enclaves extend confidential computing capabilities of Always Encrypted with pattern matching, other comparison operators and in-place encryption1.

To set up Always Encrypted in your database, you need to provision cryptographic keys to protect your data. Always Encrypted uses two types of keys: Column encryption keys and Column master keys1. A column encryption key is used to encrypt data in an encrypted column. A column master key is a key-protecting key that encrypts one or more column encryption keys. You need to store column master keys in a trusted key store outside of the database system, such as Azure Key Vault, Windows certificate store, or a hardware security module1. Then, you need provision column encryption keys and encrypt each of them with a column master key. Finally, you need to store the metadata about the keys in your database1.

Source: Conversation with Bing, 12/31/2023 (1) Always Encrypted - SQL Server | Microsoft Learn. https://learn.microsoft.com/ en-us/sql/relational-databases/security/encryption/always-encrypted-database- engine?view=sql-server-ver16. (2) Always Encrypted - Microsoft Research. https://www.microsoft.com/en-us/ research/project/always-encrypted/. (3) Always Encrypted with .NET Framework Data Provider - SQL Server. https:// learn.microsoft.com/en-us/sql/relational-databases/security/encryption/develop- using-always-encrypted-with-net-framework-data-provider?view=sql-server- ver16. (4) Develop applications using Always Encrypted - SQL Server. https:// learn.microsoft.com/en-us/sql/relational-databases/security/encryption/always- encrypted-client-development?view=sql-server-ver16. (5) Tutorial: Getting started with Always Encrypted - SQL Server. https://

learn.microsoft.com/en-us/sql/relational-databases/security/encryption/always- encrypted-tutorial-getting-started?view=sql-server-ver16.

Windows Management and Control for Enterprise Windows Users

A Windows domain is a network of computers that are managed by a central server called a domain controller. Windows domains are typically used in large organizations such as corporations, schools, and governments to manage and control a large number of computers from one place1.

When a computer is joined to a domain, the settings and user accounts on the computer are controlled on the domain controller instead of the computer itself1. This allows network administrators to manage a large number of PCs and control them from one place1.

If you have a home computer, it is not part of a domain. However, if you have a laptop provided by your employer or school, it might be part of a domain1.

Source: Conversation with Bing, 12/31/2023 (1) What is a Windows Domain and How Does It Affect My PC? - How-To Geek. https://www.howtogeek.com/194069/what-is-a-windows-domain-and-how-does- it-affect-my-pc/. (2) Windows domain - Wikipedia. https://en.wikipedia.org/wiki/Windows_domain. (3) What Is a Windows Domain and What Are Its Advantages? - MUO. https:// www.makeuseof.com/tag/windows-domain/.

There you go! Now you are smarter than 1/2 the expensive database consultants and developers you hire! Let me know if I missed something. Have a great New Year’s Eve!

And now for the hashtags you don't have to read

#Executive Summary #SQL Server #SQLServer #ETL #ELT #SSIS #Analytics #Microsoft #Database #Cloud #Encryption #Enterprise #DisasterRecovery #HighAvailability #humor #SQL #T-SQL #StoredProcedures #LearningHowToHearAgain #CochlearImplant #Deaf #NewlyDeaf #DAHH #IDontBeAfraidofWheDonfMind #Visualization #theMythicalManMonth #blockchain