Migrate a database from one server to another using log shipping in SQL Server

Migrate a database from one server to another using log shipping  in SQL Server.

                                                                                                                        Download

Migrating a database from one server to another using the attach and detach method

 Migrating a database from one server to another using the attach and detach method.

                                                                                                                        Download

Side-by-side migration in the context of SQL Server using Backup and restore

 Side-by-side migration in the context of SQL Server using Backup and restore 

                                                                                                            Download

Capture all SQL and Stored Proc calls using Extended Events in SQL Server

-- Capture all SQL and Stored Proc calls

-- Part of the SQL Server DBA Toolbox at https://github.com/DavidSchanzer/Sql-Server-DBA-Toolbox

-- This script creates an Extended Events session called "CaptureAllSQLAndStoredProcCalls" that includes the following events:

-- error_reported (for severity 20 and above, as well as certain error numbers)

-- existing_connection

-- login

-- logout

-- rpc_completed

-- sql_batch_completed


CREATE EVENT SESSION [CaptureAllSQLAndStoredProcCalls]

ON SERVER

    ADD EVENT sqlserver.error_reported

    (ACTION

     (

         package0.callstack,

         sqlserver.client_app_name,

         sqlserver.client_hostname,

         sqlserver.database_name,

         sqlserver.nt_username,

         sqlserver.session_id,

         sqlserver.session_nt_username,

         sqlserver.sql_text,

         sqlserver.tsql_stack,

         sqlserver.username

     )

     WHERE (

               [severity] >= (20)

               OR

               (

                   [error_number] = (17803)

                   OR [error_number] = (701)

                   OR [error_number] = (802)

                   OR [error_number] = (8645)

                   OR [error_number] = (8651)

                   OR [error_number] = (8657)

                   OR [error_number] = (8902)

                   OR [error_number] = (41354)

                   OR [error_number] = (41355)

                   OR [error_number] = (41367)

                   OR [error_number] = (41384)

                   OR [error_number] = (41336)

                   OR [error_number] = (41309)

                   OR [error_number] = (41312)

                   OR [error_number] = (41313)

               )

           )

    ),

    ADD EVENT sqlserver.existing_connection

    (ACTION

     (

         package0.event_sequence,

         sqlserver.client_hostname,

         sqlserver.session_id

     )

    ),

    ADD EVENT sqlserver.login

    (SET collect_options_text = (1)

     ACTION

     (

         package0.event_sequence,

         sqlserver.client_hostname,

         sqlserver.session_id

     )

    ),

    ADD EVENT sqlserver.logout

    (ACTION

     (

         package0.event_sequence,

         sqlserver.session_id

     )

    ),

    ADD EVENT sqlserver.rpc_completed

    (SET collect_statement = (1)

     ACTION

     (

         package0.event_sequence,

         sqlserver.client_app_name,

         sqlserver.client_hostname,

         sqlserver.database_name,

         sqlserver.nt_username,

         sqlserver.session_id,

         sqlserver.session_nt_username,

         sqlserver.sql_text,

         sqlserver.tsql_stack,

         sqlserver.username

     )

     WHERE ([package0].[equal_boolean]([sqlserver].[is_system], (0)))

    ),

    ADD EVENT sqlserver.sql_batch_completed

    (ACTION

     (

         package0.event_sequence,

         sqlserver.client_app_name,

         sqlserver.client_hostname,

         sqlserver.database_name,

         sqlserver.nt_username,

         sqlserver.session_id,

         sqlserver.session_nt_username,

         sqlserver.sql_text,

         sqlserver.tsql_stack,

         sqlserver.username

     )

     WHERE ([package0].[equal_boolean]([sqlserver].[is_system], (0)))

    )

    ADD TARGET package0.event_file

    (SET filename = N'c:\temp\CaptureAllSQLAndStoredProcCalls.xel')

WITH

(

    MAX_MEMORY = 16384KB,

    EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,

    MAX_DISPATCH_LATENCY = 5 SECONDS,

    MAX_EVENT_SIZE = 0KB,

    MEMORY_PARTITION_MODE = PER_CPU,

    TRACK_CAUSALITY = ON,

    STARTUP_STATE = OFF

);

GO 

Here are key points about the buffer cache in SQL Server:

    The buffer cache in SQL Server is a part of the SQL Server memory architecture that is responsible for caching database pages in memory. When SQL Server reads data from a disk, it stores a copy of that data in the buffer cache. Subsequent queries that need the same data can then be served from the in-memory buffer rather than reading from disk, which is significantly faster.

Here are key points about the buffer cache in SQL Server:

Buffer Pool:

The buffer cache is often referred to as the "buffer pool" or "data cache." It is a region in the SQL Server memory space dedicated to storing data pages.

Pages and Extents:

SQL Server divides its storage into fixed-size pages (usually 8 KB). These pages are grouped into larger structures called extents. The buffer cache holds these pages in memory.

Data Access:

When a query needs data, SQL Server first checks if the required pages are already in the buffer cache. If the data is present, it's called a "cache hit," and the data can be retrieved quickly from memory. If not, it's a "cache miss," and the data must be read from disk.

Read-Ahead Mechanism:

SQL Server uses a read-ahead mechanism to anticipate and pre-fetch pages into the buffer cache before they are needed. This helps to minimize the impact of physical I/O on query performance.

LRU (Least Recently Used) Algorithm:

The buffer cache uses an LRU algorithm to manage the contents of the cache. When the cache becomes full, pages that haven't been used recently are candidates for removal to make room for new pages.

Dirty Pages and Checkpoints:

When modifications are made to data in the buffer cache, the modified pages become "dirty." SQL Server periodically writes these dirty pages back to the data files during a process called a checkpoint. This ensures that changes are persisted to disk.

Monitoring Buffer Cache:

Performance monitoring tools and DMVs (Dynamic Management Views) can be used to monitor the state of the buffer cache. For example, the sys.dm_os_buffer_descriptors view provides information about the pages currently in the buffer cache.

SELECT * FROM sys.dm_os_buffer_descriptors;

Configuring Buffer Cache:

SQL Server provides configuration options for the size and behavior of the buffer cache. The "max server memory" configuration option limits the amount of memory that SQL Server can use for the buffer cache.

sp_configure 'max server memory', <value>;

The buffer cache plays a crucial role in optimizing SQL Server performance by reducing the need to perform expensive disk I/O operations. Properly configuring and monitoring the buffer cache is important for maintaining optimal database performance.