Tag: sql

Categories
Articles SQL Windows Server

What are the TLS supports in SQL Server?

SQL Server can support different TLS (Transport Layer Security) versions across various editions. The TLS versions supported by SQL Server may vary depending on the SQL Server version and the Windows operating system in use.

In general, SQL Server 2008 and later versions typically support TLS 1.0, TLS 1.1, and TLS 1.2. However, it’s essential to obtain the most up-to-date information from Microsoft’s official sources, as security updates and patch releases are primarily designed to address security vulnerabilities.

Below is a table illustrating the commonly supported TLS versions. Keep in mind that this information may change over time:

SQL Server VersionMin. TLS VersionMax. TLS Version
SQL Server 2008TLS 1.0TLS 1.2
SQL Server 2008 R2TLS 1.0TLS 1.2
SQL Server 2012TLS 1.0TLS 1.2
SQL Server 2014TLS 1.0TLS 1.2
SQL Server 2016TLS 1.0TLS 1.2
SQL Server 2017TLS 1.0TLS 1.2
SQL Server 2019TLS 1.2TLS 1.3*

* SQL Server 2019 may support TLS 1.3, but this is contingent on the operating system and configuration.

From a security perspective, it is recommended to use the latest version of SQL Server and keep the operating system up to date. Additionally, avoiding the use of unsupported TLS versions is crucial to prevent potential security vulnerabilities.

If you have any questions or details you would like to add, feel free to write me.

Categories
Articles Backups SQL

How many types of backups can be taken in SQL Server?

In SQL Server, backups are typically taken in four different types:

  1. Full Backup
  2. Differential Backup
  3. Transaction Log Backup
  4. File or Filegroup Backup

Let’s delve into the details of each type of backup in SQL Server:

1.Full Backup:

  • Description: A complete backup of the entire database.
  • Purpose: Provides a baseline for a complete restore of the database in case of a failure.
  • Frequency: Typically performed on a regular basis, such as daily or weekly.

2.Differential Backup :

  • Description: Captures only the data that has changed since the last full backup.
  • Purpose: Reduces the time and space required for backups by including only the changes.
  • Frequency: Can be taken between full backups to provide incremental updates.

3.Transaction Log Backup:

  • Description: Backs up the transaction log, recording changes made to the database since the last transaction log backup.
  • Purpose: Allows for point-in-time recovery and minimizes data loss.
  • Frequency: Usually taken more frequently, especially in databases with high transaction volumes.

4.File or Filegroup Backup:

  • Description: Targets specific files or filegroups within the database.
  • Purpose: Enables more granular backup and restore operations, useful for large databases.
  • Frequency: Can be used based on the need to selectively backup specific portions of the database.

These backup types collectively form a comprehensive strategy for ensuring data integrity, availability, and recoverability in SQL Server environments. The choice of which backup type(s) to use depends on factors such as the database size, recovery objectives, and the desired balance between backup frequency and resource utilization.

If you have any questions or details you would like to add, feel free to write me.

Categories
Articles Backups SQL Windows Server

How to Perform a Database Copy in SQL Server

Copying databases can often be quite useful, but knowing how to do it is crucial. In SQL Server, an easy way to copy a database is to use the “Database Copy Wizard.” Here’s how to do it using this wizard:

  1. First, open the SQL Server Management Studio (SSMS) application and connect to your SQL Server.

You can access the article where I previously explained the installation process from here.

2. In the “Databases” tab on the left, locate the database you want to copy. This is the database you’ll be duplicating.

3. Now, right-click on it and select the “Tasks” option, then click on “Copy Database” to start the Database Copy Wizard.

4. On the wizard’s initial screen, you’ll see the “Welcome to the Copy Database Wizard” message. Click “Next” to proceed.

5. On the “Select a Source Server and Database” screen, enter the name of your source SQL Server instance and, if necessary, provide authentication credentials. Then, choose the database you want to copy.

6. On the “Select a Destination Server and Database” screen, specify the name of your destination SQL Server instance and enter a new name for the copied database.

7. On the “Select Transfer Method” screen, you typically prefer to use the “Use the SQL Management Object method” option.

8. On the “Select Databases” screen, select the relevant database for the copy operation..

9. Next, on the “Configure Destination Database” screen, you can configure settings like database size, growth options, and other configurations.

10. “Configure the Package” will create an Integration Services package with your specified settings.

11. On the “Schedule and Start Copying” screen, you can choose to start the process immediately or create a scheduling plan.

12. In the final step, review the operation and click “Finish” to initiate the database copying process.

This process can take some time depending on your settings and the database’s size. Once completed, the new database will be created on the destination server.

So, you’ve successfully copied your database!

If you have any questions or details you would like to add, feel free to write me.

Categories
Articles Monitoring Windows Server

Part II — Monitoring MSSQL on Windows Server: A Guide to Maximizing Database Performance

MSSQL Monitoring
MSSQL Monitoring

Here’s how to perform this comprehensive monitoring:

TL;DR

  1. Enable SQL Server Agent: Activate the SQL Server Agent for interaction with external collectors such as Prometheus.
  2. Install Prometheus Windows Exporter: Download and install the Prometheus Windows Exporter from GitHub.
  3. Set Up Prometheus Scraper/Database: Install Prometheus on your monitoring server/computer and configure the scraper.
  4. Access Prometheus Server and Add Targets: Access the Prometheus server through a browser and add a new target.
  5. Query SQL Server Processes: Execute the necessary query to query SQL Server processes.
  6. Install Grafana and Connect to Prometheus: Install Grafana, connect it to the Prometheus server, and visualize the data.

In our previous article, you can find information on how to perform monitoring on a Windows server.

  1. Enable SQL Server Agent in SSMS:
  • To allow external collectors (e.g., Prometheus) to query the SQL Server engine, enable the SQL Server Agent.

  • SQL Server Agent
  • Change the startup type of the SQL Server Agent service to “Automatic” so that it starts automatically upon system startup.
Services / Automatic
Services / Automatic
2.Install Prometheus Windows Exporter:
  • Download  the Prometheus Windows Exporter from GitHub.
  • Use the installer found in the “Assets” section to install the exporter.
  • You can use the default Windows node exporter provided by Prometheus, which includes basic SQL Server metrics. No additional exporter installation is needed.
  • Install the exporter using the MSI installer.
  • After installation, the binary will be located in theC:\Program Files\windows_exporterfolder.
  • If a process/task named “windows_exporter” is running, terminate it, as we will need to manually start the exporter with some additional flags.
  • Manually run the exporter with the following command to fetch SQL Server statistics:
C:\Program Files\windows_exporter\windows_exporter.exe --collectors. Enabled="cpu,cs,logical_disk,net,os,service,system,textfile,mssql"
  • Access http://localhost:9182/metrics in your browser to view available metrics.
  • To make the changes persistent, add the following settings to the registry:
"C:\Program Files\windows_exporter\windows_exporter.exe" --collectors.enabled="cpu,cs,logical_disk,net,os,service,system,textfile,mssql" --log.format logger:eventlog?name=windows_exporter

Search for “regedit” in the Start menu.

regedit

Open regedit.

HKEY_LOCAL_MACHONE/SYSTEM/Current ControlSet/Services/windows_exporter

in the opened window and make the registry changes we provided above to the ‘ImagePath’.

windows_exporter
windows_exporter

3-  Install Prometheus and Configure the Scraper:

  • Download the latest version of Prometheus from prometheus.io. ,
  • Extract the downloaded .tgz file to an appropriate folder.
  • Run the binary file named prometheus.exelocated in the extracted folder.
  • There will be a file named prometheus.yamlin the directory where Prometheus is installed.
  • Open this file with a text editor. In thestatic_configssection, add the IP address of the Windows server you want to add as a target.

Example:

 - job_name: "MSSQL_SERVER"

    # metrics_path defaults to '/metrics'
    # scheme defaults to 'http'.

    static_configs:
      - targets: ["MSSQLSERVERIP:9182"]
  • Replace “MSSQLSERVERIP” with the actual IP address of your Windows server.
  • Save and close the file.

4.Access the Prometheus Server and Add Targets:

  • Open a browser and go to localhost:9090
  • In the Status -> Targets tab, make sure the target at MSSQLSERVERIP:9182/metrics is UP.
Prometheus — UP
Prometheus — UP
5. Query SQL Server Processes Through Prometheus:
  • Tarayıcınızı açın ve Prometheus sunucusuna gidin. Varsayılan olarak localhost:9090 .Open your browser and go to the Prometheus server. By default, it’s localhost:9090
  • Click on the “Console” tab in the upper left corner. This is where you can run PromQL queries.
  • Query SQL Server processes by entering the following query:
rate(windows_mssql_sqlstats_batch_requests[30s]) * 60
sql sorgu
sql sorgu

6.Install Grafana and Connect to Prometheus:

  • Install Grafana and access the interface at localhost:3000.
  • Use the “Import” option to add the MSSQL Windows Exporter Dashboard theme. Enter ‘15024’ as the ID in the “Import via grafana.com” section and proceed with “Load.”
  • In the “Data Sources” section, add the “Prometheus” data source and “Import” it.
  • Now, you can visually examine SQL Server statistics in the added dashboard.
MSSQL Monitoring
MSSQL Monitoring
If you have any questions or details you would like to add, feel free to write me.
Categories
Articles SQL Windows

Azure Data Studio or SSMS — which should I use?

Azure Data Studio (ADS) and SQL Server Management Studio (SSMS) are both database management tools used for different purposes, and which tool to use depends on your needs and preferences.

Azure Data Studio (ADS):

Azure Data Studio (ADS):
Azure Data Studio (ADS):
  • ADS offers cross-platform support, meaning it can be used on Windows, macOS, and Linux. This can be essential for collaboration among team members using different operating systems.
  • It is primarily designed for database development and query creation. It is used for editing queries, visualizing query results, and managing databases at a high level.
  • It comes with an integrated query editor with advanced development features such as syntax highlighting, auto-completion, and code hints.
  • ADS can work with multiple database systems (SQL Server, PostgreSQL, MySQL, MongoDB, etc.) and allows you to create different connection profiles.
  • You can extend its functionality using extensions and plugins, enabling you to customize your workflow by installing or developing specific extensions.

SQL Server Management Studio (SSMS):

SQL Server Management Studio (SSMS):
  • SSMS only runs on the Windows operating system and is specifically designed for managing SQL Server. Therefore, it is recommended for those working primarily with SQL Server databases.
  • SSMS allows you to create, edit, manage, and back up database objects. It provides tools for tasks like database backup, security configuration, and performance monitoring.
  • It offers specialized reports and performance monitoring tools for professional SQL Server administration.
  • SSMS provides specialized tools and design surfaces for tasks like creating databases, writing stored procedures, and designing workflows.
  • It offers advanced monitoring and security features for database administrators.

In conclusion, the choice between Azure Data Studio (ADS) and SQL Server Management Studio (SSMS) depends on your project requirements, team member preferences, and the database system you are working with. If you have general database development and query-writing needs across various platforms, ADS might be a better fit. However, if you are primarily working with SQL Server and handling administrative tasks, SSMS is the more suitable choice. To make the best decision for your needs, consider trying out both tools and assessing which one aligns better with your workflow.

If you have any questions or details you would like to add, feel free to write me.