반응형

[스크립트 11-10]

--인증키생성

create master key encryption by password = 'www.SQLTAG.org';

GO

 

--인증키 추가(주 서버(A), 미러 서버(B), 모니터 서버(C)를 구분하자)

create certificate SQLTAG_A_Cert with subject = 'SQLTAG_A certificate', start_date = '2011-01-01', expiry_date = '2020-01-01';

GO

 

[스크립트 11-11]

--인증키백업

Backup certificate SQLTAG_Cert to file = 'C:\SQLTAG_A_Cert.cer';

 

[스크립트 11-12]

--ENDPOINT 생성

Create endpoint Mirroring state = started

as tcp(listener_port = 5022, listener_ip = all)

for data_mirroring (authentication = certificate SQLTAG_A_Cert, encryption = required, role = partner);

GO

 

[스크립트 11-13]

--미러 서버가 사용 할 계정 생성.

create login SQLTAG_B_Login with PASSWORD = 'www.SQLTAG.org';

GO

 

create user SQLTAG_B_User from login SQLTAG_B_Login;

GO

 

--미러 서버 인증서 등록.

Create certificate SQLTAG_B_cert Authorization SQLTAG_B_user From file = 'c:\SQLTAG_B_cert.cer';

GO

 

Grant CONNECT ON Endpoint::Mirroring to [SQLTAG_B_Login];

GO

 

[스크립트 11-14]

-- 모니터링 서버가 사용 할 계정 생성.

create login SQLTAG_C_Login with PASSWORD = 'www.SQLTAG.org';

GO

 

create user SQLTAG_C_User from login SQLTAG_C_Login;

GO

 

-- 모니터링 서버 인증서 등록.

Create certificate SQLTAG_C_cert Authorization SQLTAG_C_User From file = 'C:\SQLTAG_C_cert.cer';

GO

 

Grant CONNECT ON Endpoint::Mirroring to [SQLTAG_C_Login];

GO

 

[스크립트 11-15]

-- 미러 서버 파트너 설정

alter database MirrorTest set partner ='TCP://SECOND2008R2.localdomain:5022';

GO

 

-- 모니터링 서버 파트너 설정

alter database MirrorTest set witness = 'TCP://THIRD2008R2.localdomain:5022';

GO

 

[스크립트 11-16]

-- 주 서버가 사용할 계정 생성.

create login SQLTAG_A_login with PASSWORD = 'www.SQLTAG.org';

GO

 

create user SQLTAG_A_User from login SQLTAG_A_login;

GO

 

--주 서버의 인증서 등록

Create certificate SQLTAG_A_cert Authorization SQLTAG_A_user From file = 'C:\SQLTAG_A_cert.cer';

GO

 

Grant CONNECT ON Endpoint::Mirroring to [SQLTAG_A_login];

GO

 

[스크립트 11-17]

--모니터링 서버가 사용할 계정 생성

create login SQLTAG_C_login with PASSWORD = 'www.SQLTAG.org';

GO

 

create user SQLTAG_C_user from login SQLTAG_C_login;

GO

 

--모니터링 서버의 인증서 등록

Create certificate SQLTAG_C_cert Authorization SQLTAG_C_user From file = 'C:\SQLTAG_C_cert.cer';

GO

Grant CONNECT ON Endpoint::Mirroring to [SQLTAG_C_login];

GO

 

[스크립트 11-18]

-- 파트너 설정

alter database Mu_Log set partner = 'TCP://first2008r2:5022';

 

[스크립트 11-19]

-- 주 서버 사용할 계정 생성

create login SQLTAG_A_login with PASSWORD = 'www.SQLTAG.org';

GO

 

create user SQLTAG_A_user from login SQLTAG_A_login;

GO

 

--주 서버 인증키 등록

Create certificate SQLTAG_A_cert Authorization SQLTAG_A_user From file = 'c:\SQLTAG_A_cert.cer';

GO

 

Grant CONNECT ON Endpoint::Mirroring to [SQLTAG_A_login];

GO

 

[스크립트 11-20]

--미러 서버 사용할 계정 생성

create login SQLTAG_B_login with PASSWORD = 'www.SQLTAG.org';

GO

 

create user SQLTAG_B_user from login SQLTAG_B_login;

GO

 

--미러 서버 사용할 인증키 등록

Create certificate SQLTAG_B_cert Authorization SQLTAG_B_user From file = 'c:\SQLTAG_B_cert.cer';

GO

 

Grant CONNECT ON Endpoint::Mirroring to [SQLTAG_B_login];

GO

 

[스크립트 11-27]

--스크립트 제공(한국마이크로소프트 송혁)

USE MSDB

GO

 

ALTER DATABASE [msdb] SET ENABLE_BROKER

GO

 

CREATE TABLE tbl_SQLTAG

(

        MirrorStateChange int,

    DBName varchar(100),

    ServerName varchar(100),

    PostTime datetime,

    SPID int,

    TextData nvarchar(500),

    DatabaseID int,

    TransactionsID int,

    StartTime datetime

 )

GO

 

CREATE proc Failover_SQLTAG

as

DECLARE @SQL VARCHAR(8000);

Declare @Message XML

 

WHILE 1=1

BEGIN

WAITFOR(

RECEIVE TOP (1) @Message = Cast(message_body as XML) from DBMirrorQueue

), TIMEOUT 100;

 

IF (@@ROWCOUNT <> 0)

BEGIN

               insert into Failover_SQLTAG values(

               @Message.value('(/EVENT_INSTANCE/State)[1]', 'int')

               ,@Message.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'sysname')

               ,@Message.value('(/EVENT_INSTANCE/ServerName)[1]', 'sysname')

               ,@Message.value('(/EVENT_INSTANCE/PostTime)[1]', 'datetime')

               ,@Message.value('(/EVENT_INSTANCE/SPID)[1]', 'int')

               ,@Message.value('(/EVENT_INSTANCE/TextData)[1]', 'nvarchar(500)')

               ,@Message.value('(/EVENTr_INSTANCE/DatabaseID)[1]', 'int')

               ,@Message.value('(/EVENT_INSTANCE/TransactionsID)[1]', 'int')

               ,@Message.value('(/EVENT_INSTANCE/StartTime)[1]', 'datetime')

               ) 

 

               IF @Message.value('(/EVENT_INSTANCE/State)[1]', 'int')  IN (7,8)

               BEGIN

               SET @SQL = 'USE MASTER  '

               SELECT @SQL = @SQL + 'ALTER DATABASE '+db_name(database_id)

               +' SET PARTNER FAILOVER; '

               FROM sys.database_mirroring WHERE mirroring_state=4 and mirroring_role = 1

                       --SELECT @SQL

                       exec (@sql)

               END

              

               IF @Message.value('(/EVENT_INSTANCE/State)[1]', 'int')  IN (11) -- agent disable

               BEGIN

                      

               SET @SQL = 'USE MSDB '

               SELECT @SQL = @SQL + 'exec sp_update_job @job_id = '''+cast(job_id as varchar(100))+''', @enabled = 0;' 

               FROM msdb.dbo.sysjobs

               where name like '[[mirroring%' ESCAPE '['

               --SELECT @SQL

               exec (@sql)

               END

              

               IF @Message.value('(/EVENT_INSTANCE/State)[1]', 'int')  IN (13) -- agent enable

               BEGIN

                       --DECLARE @SQL VARCHAR(1000)

               SET @SQL = 'USE MSDB '

               SELECT @SQL = @SQL + 'exec sp_update_job @job_id = '''+cast(job_id as varchar(100))+''', @enabled = 1;' 

               FROM msdb.dbo.sysjobs

               where name like '[[mirroring%' ESCAPE '['

                                                            

               --SELECT @SQL

               exec (@sql)

        END

END

END

GO

GO

 

/* Create Queue */

    ALTER Queue DBMirrorQueue

        With Status = On,

        Retention = Off,

        Activation (

        Procedure_Name = dbo.Failover_SQLTAG,

            Max_Queue_Readers = 1,

            Execute As Self)

GO

/* Create Service */

    Create Service DBMirrorService

        On Queue DBMirrorQueue ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]);

GO

/* Create Route*/

    Create Route DBMirrorRoute

        With Service_Name = 'DBMirrorService',

        Address = 'Local';

GO

/* Create Event */

    Create Event Notification DBMirrorStateChange

        On Server

        For DATABASE_MIRRORING_STATE_CHANGE

        To Service 'DBMirrorService', 'current database';

 

[예제실습 소스 코드]

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Linq;

using System.Text;

using System.Windows.Forms;

 

using System.Data.SqlClient;

 

namespace MirrorTest

{

    public partial class Form1 : Form

    {

        public Form1()

        {

            InitializeComponent();

        }

 

        SqlConnection myConn;

        SqlCommand myCmd;

        SqlDataReader myReader;

 

        private string conn = "Server=First2008r2; Failover Partner=Second2008r2; Database=MirrorTest; user=sa; password=패스워드";

        private string StrMsg = "";

       

       

        private void btn_Start_Click(object sender, EventArgs e)

        {

            this.myConn = new SqlConnection();

            this.myConn.ConnectionString = conn;

           

            this.myCmd = new SqlCommand();

            this.myCmd.CommandType = System.Data.CommandType.Text;

            this.myCmd.Connection = myConn;

 

            myConn.Open();

            myCmd.CommandText = this.txt_Query.Text;

            myCmd.ExecuteNonQuery();

 

            this.myReader = myCmd.ExecuteReader();

 

            this.myReader.Read();

 

            this.txt_Result.Text = myReader.GetSqlString(0).ToString(); ;

         

 

 

           

 

        }

 

        private void btn_Stop_Click(object sender, EventArgs e)

        {

            myConn.Close();

 

        }

    }

}

 

반응형
저작자표시

'책 리뷰 > SQL Server 운영과튜닝' 카테고리의 다른 글

[참고] 백업 자동화용 프로시저  (0) 2012.04.30
[챕터-06] 데이터 전송 최적화 스크립트  (0) 2012.04.30
[챕터-07-04] 병렬처리 [챕터-09] 실전 쿼리 튜닝  (0) 2012.04.30
[챕터-13] 로그전달(Log Shipping)  (0) 2012.04.30
445 PAGE SP_LOCK2 스크립트  (0) 2012.04.30

+ Recent posts

티스토리툴바