443CHapter5.DesigningSQLServerEndpoints

Designing SQL Server Endpoints


Lesson 1:  Overview of Endpoint


Endpoints control the capability to connect to an instance of SQL Server as well as dictating the communications methods that are acceptable.


1. Endpoint types of payloads


An endpoint has two basic parts: a transport and payload.


Transport Payload


TCP TSQL


TCP SERVICE BROKER


TCP DATABASE MIRRORING


HTTP SOAP


By combing an endpoint transport and payload, SQL Server can filter acceptable traffic before a command event reached the SQL Server instance. (First the validate the transport and payload, then authenticate)


2. Endpoint access


(1) Even if traffic going to the endpoint matches the correct transport and payload, a connection is still not allowed unless access has been granted on the endpoint.


(2) The first layer of access security is determined by the endpoint state. An endpoint can have one of three states: STARTED, STOPPED, and DISABLED. 


 STARTED: The endpoint is actively listening for connections and will replay to an application


 STOPPED: The endpoint is actively listening, but returns a connection error to an application


 DISABLED: The endpoint does not listen and does not respond to any connection attempted


(3) The second layer of security is permission to connect to the endpoint. An application muse have a login created in SQL Server hat has the CONNECT permission granted on the endpoint before the connection is allowed through the endpoint.


(4) SQL Server 2005 ensures that only valid requests can be submitted by a valid user before a request is scheduled within the engine.  Administrators also have a master switch to immediately shut off access if they feel someone is attempting to compromise their SQL Server, by setting the state of the endpoint being used to DISABLED


3. Practice: Inspecting existing endpoints


select * from sys.endpoints


select * from sys.tcp_endpoints


select * from sys.http_endpoints


select * from sys.database_mirroring_endpoints


select * from sys.service_broker_endpoints

Lesson 2:  TCP Endpoints


1. TCP protocol arguments


(1) TCP endpoints are configured to listen on specific IP addresses and port numbers. The two arguments that can be specified that are universal for all TCP endpoints are the following. LISENER_PORT and LISENER_IP.


(2) LISENER_PORT argument is required. The TCP or TSQL endpoint that is created for each instance during installation is already configured for port 1433 o the alternative port number for the instance.


(3) LISENER_IP argument is an optional argument that can provide a powerful security layer for some types of applications. You can specify a specific IP address for the endpoint to listen on. The default setting is ALL.


2. Database mirroring and service broker common arguments


(1) Database mirroring and service broker endpoints provide options to specify the authentication method and the encryption setting. You can use either Microsoft Windows-based authentication or certificates.


(2) Windows-based authentication: NTLM, KERBEROS, NEGOTIATE (Negotiate means that dynamical select the authentication method.)


(3) Best practices


 If the same domain or across trusted domain, use the Windows-based authentication


 If different non-trusted domain, use the certification


(4) All communication between endpoints can be encrypted, and you can specify which algorithm to use for the communications. The default algorithm is RC4, but you can specify the much stronger advanced encryption standard (AES) algorithm.


3. Database mirroring specific arguments


(1) Database mirroring endpoints include a third argument related to the role within the database mirroring session.


(2) Database mirroring endpoints role


Role Description


PARTNER The endpoint can be only as the principal or the mirror


WITNESS The endpoint can be only as the witness


ALL The endpoints can be either partner or witness

(3) Other


4. Database mirroring Practice


(1) structure

(2) preparing works


 Set the recovery mode of the principal to FULL.


 Backup the database on principal


 Restore the database on mirror with NORECOVERY


 Backup the transaction log on principal, restore the transaction log on the mirror


 Transfer to the instance hosting the mirror all logins, jobs, linked server, and other objects external to the database.


--on the principal server


use master


go


backup database DB_Mirror_Sample


to disk = 'c:\test\DB_Mirror_Sample.bak'


with format


go

backup log DB_Mirror_Sample


to disk = 'c:\test\DB_Mirror_Sample_Log.bak'


with norecovery


go

--on the mirroring server


use master


go

restore database DB_Mirror_Sample


from disk='c:\test\DB_Mirror_Sample.bak'


with file=1, norecovery


go

restore log DB_Mirror_Sample


from disk='c:\test\DB_Mirror_Sample_Log.bak'


with file=1, norecovery


go


(3) Establishing endpoints.


Enable the database mirror


 


Configure security


 


 


 

Change the SQL Server Service Account for Principal, Mirror, and Witness.


 

 


 


(4) You can change operation mode if possible


Mode Witness Explanation


High performance (asynchronous) N/A To maximize performance, the mirror database always lags somewhat behind the principal database, never quite catching up. However, the gap between the databases is typically small. The loss of a partner has the following effect:


 If the mirror server instance becomes unavailable, the principal continues.


 If the principal server instance becomes unavailable, the mirror stops; but if the session has no witness (as recommended) or the witness is connected to the mirror server, the mirror server is accessible as a warm standby; the database owner can force service to the mirror server instance (with possible data loss).


High safety without automatic failover (synchronous) No All committed transactions are guaranteed to be written to disk on the mirror server.


Manual failover is possible when the partners are connected to each other and the database is synchronized.


The loss of a partner has the following effect:


 If the mirror server instance becomes unavailable, the principal continues.


 If the principal server instance becomes unavailable, the mirror stops but is accessible as a warm standby; the database owner can force service to the mirror server instance (with possible data loss).


High safety with automatic failover (synchronous) Yes All committed transactions are guaranteed to be written to disk on the mirror server.


Availability is maximized by including a witness server instance to support automatic failover. Note that you can select the High safety with automatic failover (synchronous) option only if you have first specified a witness server address.


Manual failover is possible when the partners are connected to each other and the database is synchronized.


Important:  If the witness becomes disconnected, the partners must be connected to each other for the database to be available.

In the presence of a witness, the loss of a partner has the following effect:


 If the principal server instance becomes unavailable, automatic failover occurs. The mirror server instance switches to the role of principal, and it offers its database as the principal database.


 If the mirror server instance becomes unavailable, the principal continues.

 


 


(5) Failover


 

 


(6) Removing the mirror


alter database DB_Mirror_Sample set partner OFF

 


5. Service broker-specific arguments


(1) In addition to authentication modes and encryption, the service broker endpoints implement arguments related to message forwarding.


(2) The MESSAGE_FORWAREDING (DISABLED | ENABLED) option enables messages destined for a different broker instance to be forwarded to a specified forwarding address.


6. Service broker practice


 

use master


go


alter database DB_SB


set enable_broker


go

use DB_SB


go

create master key


encryption by password = 'Pa$$w0rd'


go

--message type


create message type SubmitBOMProduct


validation = well_formed_xml


create message type ReceiveBOM


validation = well_formed_xml


-- create contract


create contract BOMContract


(SubmitBOMProduct sent by initiator,


ReceiveBOM sent by target)

/*create queue*/


--1. create queue


create queue BOMProductQueue


create queue BOMResultQueue


--2 create a service


create service BOMRequestService


on queue BOMProductQueue(BOMContract)


create service BOMResultService


on queue BOMResultQueue(BOMContract)

/*create a conversation*/


declare @dialoghandle uniqueidentifier

begin dialog conversation @dialoghandle


from service BOMRequestService


to service 'BOMResultService'


on contract BOMContract

select @dialoghandle

/*send and reveive message*/


select * from BOMProductQueue


select * from BOMResultQueue

--1. send msg


send on conversation 'AC0996FF-1C16-DE11-AA62-0003FF1D2E78'


message type SubmitBOMProduct


(N'123')

select * from BOMProductQueue


select * from BOMResultQueue

--2. receive msg


receive top(1) *


from BOMResultQueue

select * from BOMProductQueue


select * from BOMResultQueue

Lesson 3:  HTTP Endpoints


1. HTTP endpoint security


(1) In addition to specifying the HTTP protocol with a SOAP payload that restricts the endpoints to accepting only a well-formed SOAP Request, HTTP endpoints provide additional layers of security.


(2) Authentication method


Type details


Windows NTLM, KERBEROS, or NEGOTIATE (dynamic select)


Certificate Use a certificate from a trusted authority or generate your own Windows certificate


(3) Encryption


Clear text or SSL


(4) Login type


Windows or Mixed


(5) Specifying web methods


2. Creating an endpoint


CREATE ENDPOINT    sample_endpoint


STATE = STARTED


AS HTTP


(


    PATH='/hp',


    AUTHENTICATION=(INTEGRATED),


    PORTS=(SSL),


    SSL_PORT = 443,


    SITE='www.sample.com'


)

FOR SOAP


(


    WEBMETHOD 'ListCourse' (NAME='DB_Mirror_Sample.dbo.Course', SCHEMA=DEFAULT,       FORMAT=ALL_RESULTS),


    WSDL = DEFAULT,


    DATABASE = 'DB_Mirror_Sample',


    NAMESPACE='http://temUri.org'


)

443 CHapter5.Designing SQL Server Endpoints