profiler TSQL JobStep id
프로파일러 SQLAgent JobStep id 값으로 어떤 Job 인지 구분 하려면 아래 쿼리를 이용한다.
select master.dbo.fn_varbintohexstr(job_id), b.*
from msdb.dbo.sysjobs b
프로파일러 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 를 이용한 암호화 방법
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