Posts

Migrating SQL Server with minimal downtime from On premise to Azure using DAG (Distributed Availability Group)

Image
We have to migrate the SQL server from 2016 to 2019 with minimal downtime by configuring DAG. See the below process how to migrate with minimal downtime I have a single standalone server having version SQL 2016 and need to migrate the server to 2019 version. The user is not giving downtime to migrate the server having multiple database more than 200GB each. We have tried on lower version to migrate using DAG with minimal downtime. 1. We have to create different Cluster on both servers. 2. If we are migrating the server on Azure cloud then distributed availability groups are not allowed for DNN, SQL should not be DNN (DNN Listener). creating windows cluster on both server, select the server those need to configure always on and do validations and give cluster name and IP     if  we are creating the WFCS on Azure cloud there will be no option to provide cluster IP, it will take ip of one of the server those are in always on. Hence we have to use script to create cluster usi...
Post a Comment
Read more

Database Growth in % daily and Incremental

 USE [DailyDatabaseGrowthReport] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[DailyDatabaseGrowthReport]  AS BEGIN     SET NOCOUNT ON;     -- Check and create the table only if it does not exist     IF NOT EXISTS (SELECT 1 FROM sys.tables WHERE name = 'DBSizeDailyReport')     BEGIN         CREATE TABLE [dbo].[DBSizeDailyReport](             [ServerName] nvarchar(100) NOT NULL,             [DbName] nvarchar(100) NOT NULL,             [SizeInMB] int NOT NULL,             [WeekID] int NOT NULL,             [Date] datetime NOT NULL,             [DayWisePercentageGrowth] decimal(18, 2),             [IncrementalPercentage] decimal(18, 2)         );      ...
Post a Comment
Read more

Montioring the LogFile usage by which query

 create table #log_file_logical_usage (database_name sysname primary key clustered, log_file_used_size int) create table #log_file_physical_usage (database_name sysname primary key clustered, log_file_physical_size int) insert into #log_file_logical_usage SELECT  instance_name as database_name, cntr_value as log_file_used_size FROM sys.dm_os_performance_counters where counter_name = 'Log File(s) Used Size (KB)' and instance_name not in ('master', '_Total' , 'mssqlsystemresource' , 'model' , 'DBA_Admin' , 'msdb')  insert into #log_file_physical_usage SELECT instance_name as database_name, cntr_value as log_file_physical_size FROM sys.dm_os_performance_counters where counter_name = 'Log File(s) Size (KB)' and instance_name not in ('master', '_Total' , 'mssqlsystemresource' , 'model' , 'DBA_Admin' , 'msdb') use tempdb checkpoint go use master insert into DBA_Admin.dbo.tlog_usage...
Post a Comment
Read more

Capture the deadlocks for review purpose

/*capture the deadlock scripts*/  DECLARE @deadlock TABLE ( DeadlockID INT IDENTITY PRIMARY KEY CLUSTERED,         DeadlockGraph XML         ); CREATE TABLE #errorlog ( LogDate DATETIME , ProcessInfo VARCHAR(100) , [Text] VARCHAR(MAX) ); DECLARE @tag VARCHAR (MAX) , @path VARCHAR(MAX); INSERT INTO #errorlog EXEC sp_readerrorlog; SELECT @tag = text FROM #errorlog WHERE [Text] LIKE 'Logging%MSSQL\Log%'; DROP TABLE #errorlog; SET @path = SUBSTRING(@tag, 38, CHARINDEX('MSSQL\Log', @tag) - 29); INSERT  INTO @deadlock (DeadlockGraph) SELECT CONVERT(xml, event_data).query('/event/data/value/child::*') AS DeadlockReport FROM sys.fn_xe_file_target_read_file(@path + '\system_health*.xel', NULL, NULL, NULL) WHERE OBJECT_NAME like 'xml_deadlock_report';              WITH CTE AS ( SELECT  DeadlockID,         DeadlockGraph FROM    @deadlock ), Victims AS ( SELECT    ID = V...
Post a Comment
Read more

Capturing errors thru EXTENDED EVENTS

CREATE EVENT SESSION [Capture_SQLReport_Error] ON SERVER ---- You can change name as you want. ADD EVENT sqlserver.error_reported ------------------------- Any error reported by SQL Server     (ACTION (package0.last_error,  sqlserver.database_name,  sqlserver.nt_username,  sqlserver.query_hash,  sqlserver.query_plan_hash,  sqlserver.session_id,  sqlserver.sql_text,  sqlserver.username) ), ADD EVENT sqlserver.errorlog_written------------------------- Any error written by SQL Server in the error log (ACTION (package0.last_error,  sqlserver.database_id,  sqlserver.nt_username,  sqlserver.plan_handle,  sqlserver.query_hash,  sqlserver.query_plan_hash,  sqlserver.session_id,  sqlserver.sql_text,  sqlserver.username ) )  ADD TARGET package0.event_file (SET filename=N'Capture_SQLReport_Error.xel', --------Mention the file name which you want to create r  max_file_size=(600)  --------at default e...
Post a Comment
Read more

Using PowerShell adding list of servers in CMS in SQL Server

  $path = get-location $servers = @(get-content "$path\servers.txt") { $q = use [msdb] insert into [dbo].[sysmanagement_shared_registered_servers_internal] (server_group_id,name,server_name,description,server_type) values ('45','$server','$server','','0'))" }  Invoke-Sqlcmd -serverInstance SQL2019 | -database "master" -query $q Note: Value 45 is not a default value it may vary according to server group we created in CMS. once we create a group in CMS this will save with on group id, we can see group id in "sysmanagement_shared_server_groups_internal"
Post a Comment
Read more