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"






Comments

Post a Comment

Popular posts from this blog

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...
Read more

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...
Read more