亚洲免费在线-亚洲免费在线播放-亚洲免费在线观看-亚洲免费在线观看视频-亚洲免费在线看-亚洲免费在线视频

443 CHapter5.Designing SQL Server Endpoints

系統(tǒng) 1947 0

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'<pdtID>1</pdtID><pdtID>2</pdtID><pdtID>3</pdtID>')

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


更多文章、技術(shù)交流、商務(wù)合作、聯(lián)系博主

微信掃碼或搜索:z360901061

微信掃一掃加我為好友

QQ號聯(lián)系: 360901061

您的支持是博主寫作最大的動力,如果您喜歡我的文章,感覺我的文章對您有幫助,請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧,狠狠點(diǎn)擊下面給點(diǎn)支持吧,站長非常感激您!手機(jī)微信長按不能支付解決辦法:請將微信支付二維碼保存到相冊,切換到微信,然后點(diǎn)擊微信右上角掃一掃功能,選擇支付二維碼完成支付。

【本文對您有幫助就好】

您的支持是博主寫作最大的動力,如果您喜歡我的文章,感覺我的文章對您有幫助,請用微信掃描上面二維碼支持博主2元、5元、10元、自定義金額等您想捐的金額吧,站長會非常 感謝您的哦!!!

發(fā)表我的評論
最新評論 總共0條評論
主站蜘蛛池模板: 欧美一级www片免费观看 | 久久久久在线 | 国产99视频精品免费视频免里 | 黄色免费观看视频网站 | 精品国产91 | 免费观看大片bbb | 久久久久久久99精品免费 | 国产成人亚洲精品久久 | 欧美性xxxxxx性| 亚洲在线中文 | 国产精品一区二区久久不卡 | 久久精品免费i 国产 | 中文字字幕乱码视频 | 四虎影视最新网站在线播放 | 亚洲欧美成人网 | 97影院理论在线观看 | 久久精品日日躁夜夜躁欧美 | 91国视频在线 | 在线观看 中文字幕 | 亚洲欧美日韩中文在线 | 日本高清影院 | 午夜一级在线 | 成人午夜亚洲影视在线观看 | 四虎精品成人免费影视 | 九九国产精品九九 | 青青青青久久精品国产h | 免费网站看v片在线成人国产系列 | 久久精品日日躁夜夜躁欧美 | 久久久精品 | h片在线播放免费高清 | 酒色网站| 午夜操一操 | 雅虎日本免费一区二区三区 | 亚洲欧美日韩中文无线码 | 在线成人天天鲁夜啪视频 | 日韩午夜片 | 一区二区三区视频观看 | 久久久网站 | 久久www免费人成看片入口 | 亚洲免费视频在线 | 国产99精品免费视频看6 |