보미네

$dbServerIpOrDomain = 'domain'
$dbServerPort = '1433'
$dbName = 'master'
$dbServerAccountId = 'id' 
$dbServerAccountPass = 'pass'

$query = 
@"
select @@servername Servername , @@version Version
; 
"@

function SqlQuery 
{
    param (
        [parameter(Mandatory=$true)]  [string] $server, 
        [parameter(Mandatory=$true)]  [string] $port,
        [parameter(Mandatory=$true)]  [string] $database, 
        [parameter(Mandatory=$true)]  [string] $id, 
        [parameter(Mandatory=$true)]  [string] $pass, 
        [parameter(Mandatory=$true)]  [string] $query,
        [parameter(Mandatory=$false)] [switch] $isRead = $false , 
        [parameter(Mandatory=$false)] [int]    $queryTimeout = 0 ,
        [parameter(Mandatory=$false)] [int]    $connTimeout = 5 ,
        [parameter(Mandatory=$false)] [string] $appName = "PowerShell"  
    ) 
    try
    {
        $substringLen = 100
        if ($query.Length -lt $substringLen)
        {
            $substringLen = $query.Length
        }
        $querySubstring = $query.substring(0, $substringLen)
        
        $conn = New-Object System.Data.SQLClient.SQLConnection
        $conn.ConnectionString = "server=$($server),$($port);database=$($database);User Id=$($id);Password=$($pass);Connect Timeout=$($connTimeout);Application Name=$($appName)"
        $conn.Open()
        $cmd = New-Object System.Data.SQLClient.SQLCommand
        $cmd.Connection = $conn
        $cmd.CommandText = $query
        $cmd.CommandTimeout=$queryTimeout
        if($isRead)
        {
            $ds=New-Object system.Data.DataSet
            $da=New-Object system.Data.SqlClient.SqlDataAdapter($cmd)
            $da.fill($ds) | Out-Null
            Write-Host ($ds.Tables | Format-Table | Out-String)
        }
        else
        {
            $cmd.ExecuteNonQuery() | Out-Null
        }
        $conn.Close()

        Write-Host "SUCCESS, SERVER : $($server), QUERY : $($querySubstring) ..."
    }
    catch 
    {
        Write-Host "FAILED, SERVER : $($server), QUERY : $($querySubstring) ..."
        throw "FAILED SERVER : $($server), ERROR MESSAGE : $($_)"
    }
}

Clear 
ipconfig /all 
Resolve-DnsName $dbServerIpOrDomain
Test-NetConnection $dbServerIpOrDomain -Port $dbServerPort
SqlQuery -server $dbServerIpOrDomain -port $dbServerPort -database $dbName -id  $dbServerAccountId -pass $dbServerAccountPass -query $query -isRead:$true

# DNS 를 조회하면 2개의 ip와 TTL 1200 이 출력된다. 
Resolve-DnsName lissql1

#PS C:\Users\dba> Resolve-DnsName lissql1
#Name                                           Type   TTL   Section    IPAddress                                
#----                                           ----   ---   -------    ---------                                
#lissql1.foo.company.local                A      1200  Answer     100.107.61.251                            
#lissql1.foo.company.local                A      1200  Answer     100.107.62.51                             



# 모듈을 불러들인다. 
Import-Module FailoverClusters  



# Network Name 을 찾는다. 
Get-ClusterResource -Cluster avconsole-clu1



# agsql1_lissql1 Network Name을 대상으로 아래 설정을 수행한다. 
Get-ClusterResource -Cluster avconsole-clu1 -Name "agsql1_lissql1" | Get-ClusterParameter
Get-ClusterResource -Cluster avconsole-clu1 -Name "agsql1_lissql1" | Set-ClusterParameter -Name RegisterAllProvidersIP 0
Get-ClusterResource -Cluster avconsole-clu1 -Name "agsql1_lissql1" | Set-ClusterParameter -Name HostRecordTTL 30



# 리소스 재시작과 클러스터 시작을 해줘야 적용된다. 
Stop-ClusterResource -Cluster avconsole-clu1 -Name "agsql1_lissql1"
Start-ClusterResource -Cluster avconsole-clu1 -Name "agsql1_lissql1"
Start-Clustergroup -Cluster avconsole-clu1 -Name "agsql1"



# 최종 확인, 아래처럼 1개의 ip와 ttl 30초로 설정 되어야 한다. 
Resolve-DnsName lissql1

#PS C:\Users\dba> Resolve-DnsName lissql1
#
#Name                                           Type   TTL   Section    IPAddress                                
#----                                           ----   ---   -------    ---------                                
#lissql1.foo.company.local                A      30    Answer     100.107.61.251      



# AG 에서 failover 해본다. (30초 기다려보고 Resolve-DnsName lissql1 응답을 확인한다.)

AD DNS 서버에서 아래값을 셋팅 한다. 기본값은 180초

*************************************************************** 

Set-DnsServerDsSetting -PollingInterval 30

Get-DnsServerDsSetting

-- 테이블 스위치를 이용한 일반 컬럼에 identity 속성 추가 

if object_id('t_product') is not null
drop table t_product 
go
if object_id('t_product_identity') is not null
drop table t_product_identity
go

-- int 컬럼
create table t_product 
( idx             int not null
, shopid          int
, productid       int
, contentOtherCol char(400)
) 
go

-- PK 인덱스를 추가한다. 
alter table t_product add constraint PK_t_product primary key (idx) 
go

with temp as
(
	select 
	 top 2000000 cast(row_number() over (order by (select 1)) as int) idx
	 , cast('contents other column' as char(400)) contentOtherCol
	from sys.objects a1
	 cross join sys.objects a2
	 cross join sys.objects a3
	 cross join sys.objects a4
	 cross join sys.objects a5
)
insert into t_product (idx, shopid, productid, contentOtherCol) 
select 
	 idx
	 , cast(abs(checksum(newid())) % 2 as int) shopid
	 , cast(abs(checksum(newid())) % 100000 as int) productid
	 , contentOtherCol
	--into t_product
from temp
go

-- 교체할 테이블 생성 identity 가 있는 테이블
create table t_product_identity
( idx             int identity(1,1) not null 
, shopid          int
, productid       int
, contentOtherCol char(400)
) 
go

alter table t_product_identity add constraint PK_t_product_identity primary key (idx) 
go

-- identity 없는 테이블과 idneitty 있는 테이블의 스키마를 바꿔치기 한다. 
alter table t_product switch to t_product_identity
go

-- 테이블 삭제 
drop table t_product
go

-- 이름 변경
exec sp_rename 't_product_identity','t_product'
go

-- 인덱스 이름 변경 
exec sp_rename 't_product.PK_t_product_identity', 'PK_t_product';
go

select top 10 * from t_product
go

-- 0 으로 출력됨
select IDENT_CURRENT( 't_product' )  
go

-- modify identity value
declare @max int
select @max=max(idx) from t_product
if @max IS NULL   --check when max is returned as null
  SET @max = 0
select @max
DBCC CHECKIDENT ('t_product', RESEED, @max)
go

-- 현재 max 값으로 셋팅 확인
select IDENT_CURRENT( 't_product' )  
go

-- 이름 확인 
exec sp_helpindex t_product
go

-- identity 속성 확인 
exec sp_help t_product 
go