보미네

--------------------------------------------
-- 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

# TLS 1.0, 1.1 을 서버에서 disable 하는게 문제가 아니라 client 가 TLS 1.0, 1.1 을 써 통신하는게 있다면, 서버에서 막으면 장애난다.

reg add "HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.0\Server" /v Enabled /t REG_DWORD /d 0 /f 
reg add "HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.0\Server" /v DisabledByDefault /t REG_DWORD /d 1 /f
reg add "HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.0\Client" /v Enabled /t REG_DWORD /d 0 /f 
reg add "HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.0\Client" /v DisabledByDefault /t REG_DWORD /d 1 /f
reg add "HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.1\Server" /v Enabled /t REG_DWORD /d 0 /f 
reg add "HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.1\Server" /v DisabledByDefault /t REG_DWORD /d 1 /f
reg add "HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.1\Client" /v Enabled /t REG_DWORD /d 0 /f 
reg add "HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.1\Client" /v DisabledByDefault /t REG_DWORD /d 1 /f