보미네

프로파일러 SQLAgent JobStep id 값으로 어떤 Job 인지 구분 하려면 아래 쿼리를 이용한다. 

select master.dbo.fn_varbintohexstr(job_id), b.*
from msdb.dbo.sysjobs b

에러가 나면 버전에 맞는 다음 폴더를 찾는다. 

C:\Program Files\Microsoft SQL Server\150\Setup Bootstrap\Log

Summary.txt 를 확인 한다. 

키워드를 얻었으면, 설치시간의 Detail.txt 에서 구체적인 메시지를 확인한다. 

Ctrl-F "SQL Server Browser service group does not exist"

원래 hostname 이 WIN-6SVMHIB7101 이였나 보다. 

group 을 만들어준다. 

실패한 instance 를 지워준다. 

해당 인스턴스 삭제 후 재설치

$tempResults =  'filelist1.txt' 
Set-Content -Path $tempResults -Value 'Path\Name.Extension, FileSize' -Encoding UTF8

foreach( $file in (Get-ChildItem -File -Path 'C:\path' -Recurse) )
{
    $nameCount = Get-Content -Path $file.FullName -Encoding UTF8 | Select-String -Pattern 'userFindString' | Measure-Object | Select-Object -ExpandProperty Count

    if( $nameCount -gt 0 )
    {
                $line =  $file.DirectoryName + '\' + $file.BaseName + $file.Extension + ' ' + $file.Length + ' ' + $file.CreationTime
        Add-Content -Path $tempResults -Value $line -Encoding UTF8
    }
}
clear 
type $tempResults
#notepad $tempResults
remove-item $tempResults

SELECT 
	ISNULL(TRIM(',' FROM STRING), ''), -- 왼쪽 오른쪽 다 날리기
	ISNULL(LTRIM(RTRIM(STRING,','),','), ''), -- 왼쪽 오른쪽 다 날리기 
	STUFF(STRING,1,1,''), -- 첫 문자 하나만 날리기
	ISNULL(SUBSTRING (STRING, 2, NULLIF(LEN(STRING)-1,-1)), ''), -- 첫 문자 하나만 날리기
	ISNULL(SUBSTRING (STRING, 1, NULLIF(LEN(STRING)-1,-1)), '')  -- 마지막 문자 하나만 날리기 
FROM 
	( 
	  VALUES 
	  (NULL)   
	, ('1,2')   
	, (',,1,2,,,')
	, (',1,2')   
	, ('1,2,')   
	) A (STRING)

for /f "skip=9 tokens=1,2 delims=:" %i in ('netsh wlan show profiles') do @echo %j | findstr -i -v echo | netsh wlan show profiles %j key=clear

cmd 에서 위 명령어를 치면 해당 컴퓨터로 연결되었던 모든 wifi 비밀번호가 출력된다. 

-- database size, table size 
SP_SPACEUSED 
GO

-- log space 
dbcc sqlperf(logspace)
go
dbcc loginfo 
go
select * from sys.dm_db_log_info(6)
go

-- database size 
SELECT 
  A.fileid, 
  A.groupid, 
  A.size, 
  A.maxsize, 
  A.growth, 
  A.status, 
  A.perf, 
  B.NAME DBNAME, 
  A.NAME DBNAMEDETAIL, 
  A.filename, 
  COMPUTEDSIZEMB = Cast(A.size / 128.AS INT), 
  COMPUTEDGROWTH = CASE WHEN A.status & 0X100000 = 0X100000 THEN A.growth ELSE Cast(A.growth / 128.AS INT) END, 
  COMPUTEDGROWTHUNIT = CASE WHEN A.status & 0X100000 = 0X100000 THEN 'PERCENT' ELSE 'MB' END 
FROM 
  master.dbo.sysaltfiles A WITH (nolock) 
  JOIN master.dbo.sysdatabases B WITH (nolock) ON A.dbid = B.dbid 
WHERE 
  B.NAME NOT IN (
    'MASTER', 'MSDB', 'TEMPDB', 'MODEL', 
    'LAZYLOG'
  )

-- database size
SELECT 
  Db_name() AS DBNAME, 
  NAME AS FILENAME, 
  type_desc, 
  size / 128.0 AS CURRENTSIZEMB, 
  size / 128.0 - Cast(
    Fileproperty(NAME, 'SPACEUSED') AS INT
  ) / 128.0 AS FREESPACEMB 
FROM 
  sys.database_files 
WHERE 
  type IN (0, 1);

-- all database size
if object_id('tempdb..#filesize') is not null 
drop table #filesize
create table #filesize
(
dbname nvarchar(128), 
filename nvarchar(128), 
type_desc nvarchar(128), 
currentsizemb decimal(10, 2), 
freespacemb decimal(10, 2)
);
go 
insert into #filesize(dbname, filename, type_desc, currentsizemb, freespacemb)
exec sp_msforeachdb 'use [?]; 
 select db_name() as dbname, 
        name as filename, 
        type_desc,
        size/128.0 as currentsizemb,  
        size/128.0 - cast(fileproperty(name, ''spaceused'') as int)/128.0 as freespacemb
from sys.database_files
where type in (0,1);';
go 
select * from #filesize where dbname not in ('distribution', 'master', 'model', 'msdb') 
go 
drop table #filesize;
go

-- fragmentation check takes a long time
SELECT 
  OBJECT_SCHEMA_NAME(IPS.OBJECT_ID) AS SCHEMA_NAME, 
  OBJECT_NAME(IPS.OBJECT_ID) AS OBJECT_NAME, 
  I.NAME AS INDEX_NAME, 
  I.TYPE_DESC AS INDEX_TYPE, 
  IPS.AVG_FRAGMENTATION_IN_PERCENT, 
  IPS.AVG_PAGE_SPACE_USED_IN_PERCENT, 
  IPS.PAGE_COUNT, 
  IPS.ALLOC_UNIT_TYPE_DESC 
FROM 
  SYS.DM_DB_INDEX_PHYSICAL_STATS(
    DB_ID(), 
    DEFAULT, 
    DEFAULT, 
    DEFAULT, 
    'SAMPLED'
  ) AS IPS 
  INNER JOIN SYS.INDEXES AS I ON IPS.OBJECT_ID = I.OBJECT_ID 
  AND IPS.INDEX_ID = I.INDEX_ID 
ORDER BY 
  PAGE_COUNT DESC;

GO

-- tempdb size
SELECT 
  CAST(
    SUM(
      (TOTAL_PAGE_COUNT) * 8
    ) / 1024.AS DECIMAL(25, 2)
  ) AS TOTAL_SIZE_MB, 
  --WORKS ON SQL 2012 AND FUTURE EDITIONS
  CAST(
    SUM(
      (UNALLOCATED_EXTENT_PAGE_COUNT) * 8
    ) / 1024.AS DECIMAL(25, 2)
  ) AS FREE_SPACE_MB, 
  CAST(
    SUM(
      (
        USER_OBJECT_RESERVED_PAGE_COUNT
      ) * 8
    ) / 1024.AS DECIMAL(25, 2)
  ) AS USER_OBJECTS_MB, 
  CAST(
    SUM(
      (
        INTERNAL_OBJECT_RESERVED_PAGE_COUNT
      ) * 8
    ) / 1024.AS DECIMAL(25, 2)
  ) AS INTERNAL_OBJECTS_MB, 
  CAST(
    SUM(
      (
        VERSION_STORE_RESERVED_PAGE_COUNT
      ) * 8
    ) / 1024.AS DECIMAL(25, 2)
  ) AS VERSION_STORE_MB, 
  CAST(
    SUM(
      (MIXED_EXTENT_PAGE_COUNT) * 8
    ) / 1024.AS DECIMAL(25, 2)
  ) AS MIXED_EXTENT_MB 
FROM 
  TEMPDB.SYS.DM_DB_FILE_SPACE_USAGE
GO

-- tempdb capacity used by sessions
SELECT 
  GETDATE() AS RUNTIME, 
  A.*, 
  B.KPID, 
  B.BLOCKED, 
  B.LASTWAITTYPE, 
  B.WAITRESOURCE, 
  DB_NAME(B.DBID) AS DATABASE_NAME, 
  B.CPU, 
  B.PHYSICAL_IO, 
  B.MEMUSAGE, 
  B.LOGIN_TIME, 
  B.LAST_BATCH, 
  B.OPEN_TRAN, 
  B.STATUS, 
  B.HOSTNAME, 
  B.PROGRAM_NAME, 
  B.CMD, 
  B.LOGINAME, 
  REQUEST_ID 
FROM 
  SYS.DM_TRAN_ACTIVE_SNAPSHOT_DATABASE_TRANSACTIONS A 
  INNER JOIN SYS.SYSPROCESSES B ON A.SESSION_ID = B.SPID

-- table size
SELECT 
  T.NAME AS TABLENAME, 
  S.NAME AS SCHEMANAME, 
  P.ROWS, 
  SUM(A.TOTAL_PAGES) * 8 AS TOTALSPACEKB, 
  CAST(
    ROUND(
      (
        (
          SUM(A.TOTAL_PAGES) * 8
        ) / 1024.00
      ), 
      2
    ) AS NUMERIC(36, 2)
  ) AS TOTALSPACEMB, 
  SUM(A.USED_PAGES) * 8 AS USEDSPACEKB, 
  CAST(
    ROUND(
      (
        (
          SUM(A.USED_PAGES) * 8
        ) / 1024.00
      ), 
      2
    ) AS NUMERIC(36, 2)
  ) AS USEDSPACEMB, 
  (
    SUM(A.TOTAL_PAGES) - SUM(A.USED_PAGES)
  ) * 8 AS UNUSEDSPACEKB, 
  CAST(
    ROUND(
      (
        (
          SUM(A.TOTAL_PAGES) - SUM(A.USED_PAGES)
        ) * 8
      ) / 1024.00, 
      2
    ) AS NUMERIC(36, 2)
  ) AS UNUSEDSPACEMB 
FROM 
  SYS.TABLES T 
  INNER JOIN SYS.INDEXES I ON T.OBJECT_ID = I.OBJECT_ID 
  INNER JOIN SYS.PARTITIONS P ON I.OBJECT_ID = P.OBJECT_ID 
  AND I.INDEX_ID = P.INDEX_ID 
  INNER JOIN SYS.ALLOCATION_UNITS A ON P.PARTITION_ID = A.CONTAINER_ID 
  LEFT OUTER JOIN SYS.SCHEMAS S ON T.SCHEMA_ID = S.SCHEMA_ID 
WHERE 
  T.NAME NOT LIKE 'DT%' 
  AND T.IS_MS_SHIPPED = 0 
  AND I.OBJECT_ID > 255 
GROUP BY 
  T.NAME, 
  S.NAME, 
  P.ROWS 
ORDER BY 
  TOTALSPACEMB DESC, 
  T.NAME

-- index stats takes a long time (commentout 	,STAT3.*)
IF OBJECT_ID('SP_HELPINDEX2') IS NULL
	EXEC ('CREATE PROC SP_HELPINDEX2 AS SELECT 1 ')
GO

ALTER PROCEDURE SP_HELPINDEX2 @NAME NVARCHAR(4000) = ''
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

-- SCRIPT BY MINSOUK KIM 
-- 01099670955
-- A.TO.Z@NAVERCORP.COM
SELECT ISNULL(PS.TYPE_DESC, 'NORMAL') AS ISPTN
	,PS.NAME AS PTNFUNCNAME
	,D.PARTITIONCNT AS PTNCNT
	,T1.TYPE_DESC
	,FI.NAME AS FGNAME
	,SCHEMA_NAME(SCHEMA_ID) AS SNAME
	,T1.CREATE_DATE AS TBLCREDATE
	,T1.NAME AS OBJNAME
	,I.TYPE_DESC ISCLUSTERORHEAP
	,I.NAME AS IXNAME
	,INDEXSIZEKB AS IXSIZEKB
	,SUBSTRING(B.INDEX_COLUMN_DESC, 3, 1000) AS IXDESC
	,SUBSTRING(C.INCLUDE_COLUMN_DESC, 3, 1000) AS INCOLDESC
	,STATS_DATE(i.object_id, i.index_id) AS [STATISTICUPDATEDATE]
	,I.IS_UNIQUE
	,I.IS_UNIQUE_CONSTRAINT
	,I.IS_PRIMARY_KEY
	,I.HAS_FILTER
	,I.FILTER_DEFINITION
	,I.IS_DISABLED
	,STAT1.*
	,STAT2.*
	,STAT3.*
--	, * 
FROM SYS.OBJECTS AS T1
INNER JOIN SYS.INDEXES AS I ON T1.[OBJECT_ID] = I.[OBJECT_ID]
LEFT JOIN SYS.FILEGROUPS FI ON I.DATA_SPACE_ID = FI.DATA_SPACE_ID
--    AND I.[TYPE] IN (0,1) 
LEFT JOIN SYS.PARTITION_SCHEMES PS ON I.DATA_SPACE_ID = PS.DATA_SPACE_ID
OUTER APPLY (
	SELECT TOP 100 PERCENT ', ' + AC.NAME + CASE 
			WHEN IS_DESCENDING_KEY = 1
				THEN ' DESC'
			ELSE ' ASC'
			END AS [text()]
	FROM SYS.INDEX_COLUMNS IC
	INNER JOIN SYS.ALL_COLUMNS AC ON IC.OBJECT_ID = AC.OBJECT_ID
		AND IC.COLUMN_ID = AC.COLUMN_ID
	WHERE IC.OBJECT_ID = I.OBJECT_ID
		AND IC.INDEX_ID = I.INDEX_ID
		AND IC.IS_INCLUDED_COLUMN = 0
	ORDER BY IC.INDEX_COLUMN_ID
	FOR XML PATH('')
	) B(INDEX_COLUMN_DESC)
OUTER APPLY (
	SELECT TOP 100 PERCENT ', ' + AC.NAME AS [text()]
	FROM SYS.INDEX_COLUMNS IC
	INNER JOIN SYS.ALL_COLUMNS AC ON IC.OBJECT_ID = AC.OBJECT_ID
		AND IC.COLUMN_ID = AC.COLUMN_ID
	WHERE IC.OBJECT_ID = I.OBJECT_ID
		AND IC.INDEX_ID = I.INDEX_ID
		AND IC.IS_INCLUDED_COLUMN = 1
	ORDER BY IC.INDEX_COLUMN_ID
	FOR XML PATH('')
	) C(INCLUDE_COLUMN_DESC)
OUTER APPLY (
	SELECT COUNT(*)
	FROM SYS.TABLES AS T
	INNER JOIN SYS.INDEXES AS I ON T.OBJECT_ID = I.OBJECT_ID
	INNER JOIN SYS.PARTITIONS AS P ON I.OBJECT_ID = P.OBJECT_ID
		AND I.INDEX_ID = P.INDEX_ID
	INNER JOIN SYS.PARTITION_SCHEMES AS S ON I.DATA_SPACE_ID = S.DATA_SPACE_ID
	INNER JOIN SYS.PARTITION_FUNCTIONS AS F ON S.FUNCTION_ID = F.FUNCTION_ID
	LEFT JOIN SYS.PARTITION_RANGE_VALUES AS R ON F.FUNCTION_ID = R.FUNCTION_ID
		AND R.BOUNDARY_ID = P.PARTITION_NUMBER
	WHERE T.NAME = T1.NAME
		AND I.TYPE <= 1
	) D(PARTITIONCNT)
OUTER APPLY (
	SELECT 8 * SUM(A.USED_PAGES) AS 'INDEXSIZE(KB)'
	FROM SYS.INDEXES AS III
	INNER JOIN SYS.PARTITIONS AS P ON P.OBJECT_ID = III.OBJECT_ID
		AND P.INDEX_ID = I.INDEX_ID
	INNER JOIN SYS.ALLOCATION_UNITS AS A ON A.CONTAINER_ID = P.PARTITION_ID
	WHERE III.OBJECT_ID = T1.OBJECT_ID
		AND III.INDEX_ID = I.INDEX_ID
	) E(INDEXSIZEKB)
OUTER APPLY (
	SELECT TOP 1 *
	FROM SYS.DM_DB_INDEX_OPERATIONAL_STATS(DB_ID(), T1.OBJECT_ID, I.INDEX_ID, NULL)
	WHERE 1 = CASE 
			WHEN T1.TYPE_DESC = 'SQL_TABLE_VALUED_FUNCTION'
				THEN 0
			ELSE 1
			END
	) STAT2
OUTER APPLY (
	SELECT TOP 1 *
	FROM (
		SELECT *
		FROM SYS.DM_DB_INDEX_USAGE_STATS
		) A
	WHERE OBJECT_ID = T1.OBJECT_ID
		AND INDEX_ID = I.INDEX_ID                    AND DATABASE_ID = DB_ID()
	) STAT1
OUTER APPLY (
	SELECT TOP 1 *
	FROM SYS.dm_db_index_physical_stats(DB_ID(), T1.OBJECT_ID, I.INDEX_ID, NULL, NULL)
	) STAT3
WHERE IS_MS_SHIPPED = 0
	AND T1.NAME = CASE 
		WHEN @NAME = ''
			THEN T1.NAME
		ELSE @NAME
		END
ORDER BY 1
	,2
	,3
	,4
	,5
	,6
	,7
	,I.INDEX_ID
GO

EXEC sp_helpindex2
GO

개념 이해를 위해 mdf, ndf 파일은 OS에서 받은 데이터베이스 공간이다. 이 공간은 여러 테이블이나 인덱스 등이 사용한다. 테이블이 어떤 이유로 매우 커졌다가 내부 데이터를 delete로 지우면 할당을 해지한다. 이 때 해지한 공간은 완전히 할당 해지 되는 경우가 별로 없고 그 공간은 해당 테이블이나 인덱스에서만 재활용 가능하다. 이를 다른 테이블이나 인덱스에서도 사용 가능하게 하려면 heap rebuild, clustered index rebuild, index rebuild 등을 해 완전히 할당 해지 해주어야 한다. 또 다른 방법은 reorganize 하는 방법이 있다.  이렇게 다른 테이블이나 인덱스도 사용할 수 있는 공간이 되면 비로서 mdf 파일의 크기를 줄여 OS로 공간을 반환할 수도 있다. reorganize 는 하드 디스크 조각 모음으로 이해하면 비슷하다. rebuild 는 순차 읽기 성능이 좋고 reorganize 는 순차 읽기 성능에 좋지 않다. 그런데, 요즘 대부분 SSD를 쓰니 논리 순서와 할당 순서의 일관성이 성능에 영향을 끼치지 않는다. 

아래 결과에서 unallocated space 와 unused 사이즈에 주목한다. 

sp_spaceused 

database_name                       database_size         unallocated space
------------------------------      ------------------    ------------------
SqlLogManDmvRepositoryCafeV3        5354120.00 MB         66097.92 MB

reserved           data               index_size         unused
------------------ ------------------ ------------------ ------------------
5411846224 KB      549654168 KB       178159848 KB       4684032208 KB


Completion time: 2024-01-08T15:50:42.0522621+09:00


--> 인덱스 리빌드 스크립트 수행 후 (6시간)
-- 인덱스 리빌드는 unused 사이즈를 unallocated space 로 반환하게 된다. 

sp_spaceused 

database_name                       database_size         unallocated space
------------------------------      ------------------    ------------------
SqlLogManDmvRepositoryCafeV3        5440840.00 MB         4692463.71 MB

reserved           data               index_size         unused
------------------ ------------------ ------------------ ------------------
760496424 KB       574814688 KB       184509664 KB       1172072 KB

줄이기 늘이기

-- 조회
select * from sysaltfiles where dbid = db_id()

-- TargetSize 는 무시된다. 사이즈는 MB 단위
-- 2022 에는 WAIT_AT_LOW_PRIORITY 로 장기 수행 Sch-S의 영향을 줄일수 있다. 
DBCC SHRINKFILE ('SqlLogManDmvRepositoryCafeV3', 0, TRUNCATEONLY);

-- 늘이는 명령어 
ALTER DATABASE SqlLogManDmvRepositoryCafeV3 MODIFY FILE ( NAME = 'SqlLogManDmvRepositoryCafeV3', SIZE = 1TB )

reorganize 방법 (수정 좀 해서 써야 할 듯)

DECLARE @i int, @sql varchar(1000)
DECLARE @tablename varchar(1000),@ownerName  varchar(1000)

SET @i = 1

DECLARE DB_Cursor CURSOR FOR 
 SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES  WHERE TABLE_TYPE = 'BASE TABLE' ORDER BY TABLE_SCHEMA, TABLE_NAME
OPEN DB_Cursor

FETCH NEXT FROM DB_Cursor
INTO @ownerName, @tablename
WHILE @@FETCH_STATUS = 0
BEGIN

 SET @sql = 'ALTER INDEX ALL ON ' + @ownerName + '.' + @tablename + ' REORGANIZE '
 EXEC (@sql)

 PRINT CONVERT(VARCHAR, @i) + '__' + @ownerName + '.' + @tablename + '............ OK'
 SET @i = @i + 1

 FETCH NEXT FROM DB_Cursor
 INTO @ownerName, @tablename

END

CLOSE DB_Cursor
DEALLOCATE DB_Cursor

온라인에 사용 불가능하다. 엔터프라이즈는 온라인 옵션 가능 (수정 좀 해서 써야 할 듯)

DECLARE @i int, @sql varchar(1000)
DECLARE @tablename varchar(1000),@ownerName  varchar(1000)

SET @i = 1

DECLARE DB_Cursor CURSOR FOR 
 SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES  WHERE TABLE_TYPE = 'BASE TABLE' ORDER BY TABLE_SCHEMA, TABLE_NAME
OPEN DB_Cursor

FETCH NEXT FROM DB_Cursor
INTO @ownerName, @tablename
WHILE @@FETCH_STATUS = 0
BEGIN

 SET @sql = 'ALTER INDEX ALL ON ' + @ownerName + '.' + @tablename + ' REBUILD WITH (PAD_INDEX = ON, FILLFACTOR = 90) '
 EXEC (@sql)

 PRINT CONVERT(VARCHAR, @i) + '__' + @ownerName + '.' + @tablename + '............ OK'
 SET @i = @i + 1

 FETCH NEXT FROM DB_Cursor
 INTO @ownerName, @tablename

END

CLOSE DB_Cursor
DEALLOCATE DB_Cursor

-- size 
select 
	 b.name dbname
	, a.name dbnameDetail
	, c.recovery_model_desc 
	, c.log_reuse_wait_desc 
	, c.user_access_desc 
	, a.fileid
	, a.groupid
	, a.size
	, a.maxsize
	, a.growth
	, a.status
	, a.perf
	, a.filename
	, computedsizemb = cast(a.size / 128. as int)
	, computedgrowth = case when a.status & 0x100000 = 0x100000 then a.growth else cast(a.growth / 128. as int) end
	, computedgrowthunit = case when a.status & 0x100000 = 0x100000 then 'percent' else 'mb' end
	, d.first_lsn, d.last_lsn, d.checkpoint_lsn, d.database_backup_lsn, d.backup_finish_date
from master.dbo.sysaltfiles a  with (nolock)
	join master.dbo.sysdatabases b  with (nolock)
		on a.dbid = b.dbid
	join master.sys.databases  c 
		on a.dbid = c.database_id 
	outer apply 
		(
		select top 1 first_lsn,	last_lsn,	checkpoint_lsn,	database_backup_lsn, backup_finish_date 
		from msdb.dbo.backupset 
		where database_name = db_name(a.dbid)
		order by backup_finish_date desc 
		) d
where b.name not in ('master', 'msdb', 'tempdb', 'model', 'lazylog') 


-- ****************************************

dbcc sqlperf(logspace) -- 사용량
go

use sampledb 
go
dbcc loginfo -- 로그파일 확인 
go

-- 혹은 아래 명령으로 확인 
-- select * from sys.dm_db_log_info(6)
-- 6은 select * from sysdatabases의 해당 dbid입니다. 

use sampledb 
go

-- 파일 끝에서 자르기  
-- truncateonly 옵션이 들어가면 사이즈는 무시된다. 그래서 0
-- 백업되지 않으면 비활성 상태로 사용된다. 그러므로 백업 후에 자르거나
-- reocvery level 을 simple 로 하고 자른 후 원래 recovery level 로 변경한다. 
DBCC SHRINKFILE ('sampledb_log', 0, TRUNCATEONLY);
go 


-- 로그파일의 끝을 변경시킴 (linux 의 dev/null 로 백업)
-- 실제 프러덕션 환경은 파일 이름 바꾸어서 수행해야 함
backup log sampledb to 
disk ='NUL'
;

-- 1GB 로 늘이기 (프러덕션은 최소 10GB 이상 확보할 것)
USE [master]
GO
ALTER DATABASE [sampledb] MODIFY FILE ( NAME = N'sampledb_log', SIZE = 1024000KB )
GO

--------------------------------------------
-- full & transaction log backup and norecovery mode
--------------------------------------------

-- principal server
backup database mirrortest to disk = 'd:\mssql\share\mirrortest.full'
backup log mirrortest to disk = 'd:\mssql\share\mirrortest.log'

-- mirror server
restore database mirrortest from disk = '\\ip\share\mirrortest.full' with norecovery 
restore log mirrortest from disk = '\\ip\share\mirrortest.log' with norecovery

--------------------------------------------
-- mirror mirroring endpoint add
--------------------------------------------

CREATE ENDPOINT [Mirroring] STATE=STARTED AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL) FOR DATA_MIRRORING (ROLE = PARTNER, AUTHENTICATION = WINDOWS NEGOTIATE, ENCRYPTION = DISABLED)

--------------------------------------------
-- principal mirroring endpoint add
--------------------------------------------
CREATE ENDPOINT [Mirroring] STATE=STARTED AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL) FOR DATA_MIRRORING (ROLE = PARTNER, AUTHENTICATION = WINDOWS NEGOTIATE, ENCRYPTION = DISABLED)

--------------------------------------------
-- mirroring patner setting (mirror server) partner ip
--------------------------------------------
alter database mirrortest set partner = 'tcp://ip:5022'

--------------------------------------------
-- mirroring patner setting (principal server) partner ip
--------------------------------------------
alter database mirrortest set partner = 'tcp://ip:5022'

--------------------------------------------
-- mirroring start (principal server)
--------------------------------------------
alter database mirrortest set safety full


--------------------------------------------
-- ** mirroring off
--------------------------------------------
alter database mirrortest set partner off

--------------------------------------------
-- ** mirroring failover
--------------------------------------------
alter database mirrortest set partner failover 

--------------------------------------------
-- ** mirroring suspend 
--------------------------------------------
alter database mirrortest set partner suspend 

--------------------------------------------
-- ** mirroring suspend 
--------------------------------------------
alter database mirrortest set partner resume 

--------------------------------------------
-- ** mirroring endpoint
--------------------------------------------
SELECT name, port FROM sys.tcp_endpoints;  

--------------------------------------------
-- ** mirroring status
--------------------------------------------

select 
	db.name, 
	db.state_desc, 
	dm.mirroring_role_desc, --**
	dm.mirroring_state_desc, --**
	dm.mirroring_safety_level_desc, --**
	dm.mirroring_partner_name, 
	dm.mirroring_partner_instance 
from sys.databases db
	inner join sys.database_mirroring dm
	on db.database_id = dm.database_id
where dm.mirroring_role_desc is not null
order by db.name


--------------------------------------------
-- ** go home~
--------------------------------------------
ALTER DATABASE [databaseName] SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS

아래 github 소스는 어플리케이션과 SQL 어디에서든 암호화 복호화가 가능한 방법입니다. 

sql(암호화) -> sql(복호화)

c#(암호화) -> c#(복호화)

sql(암호화) -> c#(복호화)

c#(암호화) -> sql(복호화)

https://github.com/krcs/SQLServerCrypto

-- sql sample 

EncryptByPassPhrase 와 DecryptByPassPhrase 를 이용한 암호화 방법

-- https://docs.microsoft.com/ko-kr/sql/t-sql/functions/encryptbypassphrase-transact-sql?view=sql-server-ver16

IF OBJECT_ID ('PasswordKey') IS NOT NULL

DROP FUNCTION dbo.PasswordKey

GO

CREATE FUNCTION PasswordKey()

RETURNS varchar(100)

WITH ENCRYPTION

AS

BEGIN

RETURN 'P@ssw0rd'

END

go

SELECT EncryptByPassPhrase(dbo.passwordkey(), 'target message')

GO

-- 결과

-- 0x0100000093B09F6A22BD125C758C41C0B7C1EF5EA5433F5B31FD71E3D3CFF8C073C2ECEB

SELECT CAST(DecryptByPassPhrase(dbo.passwordkey(), 0x01000000068564F4F8218FE14236AD6EA97FD0EA6A9146C91C2F7ABDC69FE7E96AB7CDB8) as varchar(8000))

-- 결과

-- target message