443 CHapter5.Designing SQL Server Endpoints

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

 

转载于:https://www.cnblogs.com/yang_sy/archive/2009/04/16/1437388.html

RAG面试内容整理-7. 多轮问答与上下文拼接策略 在对话式的RAG应用中,多轮问答需要处理上下文相关性问题:用户的提问往往依赖于之前的对话内容。为正确理解用户每一轮的问题,系统需要保留和传递对话历史信息。 阅读详情

相关推荐

瑞芯微rv1126 rtsp+mpp+rga取流

瑞芯微rv1109 rtsp+mpp+rga取流

ZuoSeDiao的博客 6341

sql server 查询镜像端点和删除

查询 select * from sys.endpoints  下图是默认存在的 删除某终端点(终端点不带引号)    drop endpoint    name

兰辉 2305

GPU算力租用平台推荐

阿里云作为中国领先的云计算服务提供商,提供了丰富的GPU云服务器实例,包括NVIDIA Tesla V100、A100等多种高性能GPU型号。阿里云GPU云服务器适用于深度学习、科学计算、图形渲染等多种场景,能够满足不同用户的计算需求。

hong161688的博客 8182

sql server 2005 T-SQL ALTER ENDPOINT (Transact-SQL)

允许通过以下方法修改现有端点: 向现有端点中添加一个新方法 修改或删除端点中的现有方法 更改端点的属性 注意: 本主题描述了特定于 ALTER ENDPOINT 的语法和参数。有关对 CREATE ENDPOINT...

weixin_34392843的博客 201

CAPL 中的变量类型详解与应用

CAPL 中的变量类型详解与应用

qq_42268594的博客 1451

FPGA实现高效数字下变频:CORDIC与CIC滤波器的Verilog优化设计

本文深入探讨了FPGA实现高效数字下变频的技术方案,重点介绍了CORDIC算法与CIC滤波器的Verilog优化设计。通过详细的算法原理分析、FPGA实现技巧和实战经验分享,展示了如何利用CORDIC的旋转运算替代乘法操作,以及CIC滤波器在降采样中的高效性能。文章还提供了完整的系统集成方案和资源优化策略,为无线通信和雷达信号处理领域的工程师提供了实用参考。

redis7keeper的博客 724

177.Agent-LangGraph-MCP的构成说明

MCP架构通过MCP主机、客户端与服务器的协同,实现工具、资源与提示词的统一管理。MCP服务器提供Tools、Resources、Prompts能力,MCP客户端则负责发现、调用与读取,使Agent可按需获取上下文。其核心在于将工具从Agent中抽离,封装为可复用的服务,提升灵活性与可维护性,推动AI应用高效协作。

计算机王的博客 315

【AI大模型:前沿】44、大模型+机器人:从自动化工具到通用智能体的技术革命与应用全景

大模型与机器人的融合催生了“具身智能体”,突破传统机器人智能化缺失、功能单一等瓶颈。大模型赋予机器人三大能力:常识推理(如热牛奶需去包装)、模糊指令解析(如“整理书桌”拆解步骤)、零样本任务泛化(如搭乐高桥)。技术实现上,通过多模态融合(视觉-语言-动作)、仿真训练(虚拟试错)和实时反馈(动态调整动作)形成闭环。主流框架如Google RT-2(端到端动作生成)、斯坦福Q-Transformer(强化学习优化)和Figure 01(ChatGPT驱动人形机器人)各具优势,可适配工业、家庭等场景。该技术正重塑

2025博客之星Top81。专注AI工程化与架构实战。从分布式思维到模型部署,用工程化视角为你厘清AI落地的真实路径。 2022

Docker Compose:搭建本地多服务开发环境

从一个 Java API、PostgreSQL 和 Redis 开始,配置服务名、端口、环境变量、健康检查、数据卷和依赖关系。理解 Compose 文件如何被解析、项目如何隔离,以及哪些数据不能依赖“重建容器”保留,能够把个人开发环境整理成团队可复用的启动入口。

m0_62221377的博客 478

【ROS】ROS2导航Nav2:简介、安装、测试效果、错误处理

在ROS2中自动导航使用Nav2来实现。Nav2 使用几个独立的模块化服务,通过 ROS 2接口(例如动作服务器或服务)与行为树 (BT) 通信。Nav2 输入包括:TF转换、一个地图源、一个行为树 (BT) XML 文件和相关的传感器数据源;Nav2 输出速度命令;Nav2 支持多种机器人,比如:四足机器人、履带机器人、轮式机器人等。

郭老二 5094

1553B总线介绍及其硬件方案设计

军用1553B总线 1.简介 简单介绍可参考(https://blog.csdn.net/ScilogyHunter/article/details/108444231)博主写的很详细。 1553B总线是MIL-STD-1553总线的简称,其中B就是BUS,MIL-STD-1553B总线是飞机内部时分制命令/响应式多路复用数据总线。1553B数据总线标准是20世纪70年代由美国公布的一种串行多路数据总线标准。1553B总线能挂31个远置终端,1553B总线采用指令/响应型通信协议,它有三种终端类型: BC:

小幽余生不加糖 1万+

AI全栈实战 | 3.9-01 爬虫入门:requests 加 BeautifulSoup 三分钟跑通,但 90% 的人栽在这 4 个“礼貌“问题上

⚠️:本文内容仅用于。请遵守目标站点的服务条款与相关法律法规,不得对目标服务造成负担,不得采集、传播个人隐私数据。技术是中性的,边界在你手里。爬虫的入门技术(requests 发请求 + BeautifulSoup 解析)三分钟就能学会。真正区分"会用爬虫"和"会做爬虫的人"的,是这篇我们跑通"请求→解析→存储"的最小闭环,然后重点把"合规与礼貌"这根弦给你绷上。

源码获取 | 运行部署 | 定制二开修改,如果回复不及时请耐心等待,请见谅。 445

【100%通过率】2024华为OD机试真题E卷 C++语言 实现【流浪地球】

流浪地球计划在赤道上均匀部署了N个转向发动机,按位置顺序编号为0~N-1。 1) 初始状态下所有的发动机都是未启动状态; 2) 发动机起动的方式分为“手动启动”和“关联启动”两种方式; 3) 如果在时刻1一个发动机被启动,下一个时刻2与之相邻的两个发动机就会被“关联启动”; 4) 如果准备启动某个发动机时,它已经被启动了,则什么都不用做; 5) 发动机0与发动机N-1是相邻; 地球联合政府准备挑选某些发动机在某些时刻进行“手动启动”,当然最终所有的发动机都会被启动。 哪些发动机最晚被启动呢?

MISAYAONE的博客 4214

spring boot 4集成SQLLite,及其SQLLite的建库

spring boot 4集成SQLLite,及其SQLLite的建库

weixin_41463944的博客 239

jdk-8u144-linux-x64.tar.gz.

该资源为搭建Linux系统Java环境用,资源的后缀为tar.gz格式的压缩包

WINCC7.3破解文件

WINCC7.3硬件够破解文件,有需要的下载!WINCC7.3硬件够破解文件,有需要的下载!

【基于 WebSocket 的多用户网页五子棋】—— 数据管理模块

但行好事,莫问前程

qq_42996461的博客 260

假人有限元模型

假人模型,车辆碰撞假人有限元模型,适合做乘员安全的研究者利用

OpenClaw 深度调查:把 AI 助手做成你自己的控制平面

对 openclaw/openclaw 的完整技术尽调:Gateway 架构、Agent 运行时、工具与沙箱、30 个消息通道、181 个插件、安全信任边界、工程代价与采用建议。17 章 / 10 张图,全部数字标注口径,数据截至 2026-10-31。

厚积薄发 363

IMSL CNL 7.0.0 (x86-64)Crack

IMSL CNL 7.0.0 (x86-64)Crack intel的算法库破解文件,这是C++版本的破解文件

IDEA 简介:从零认识 IntelliJ IDEA

IDEA 全称,是由 JetBrains 公司开发的一款主流 Java 集成开发环境(IDE)。它集代码编辑、编译、调试、测试、版本控制、构建管理等功能于一体,被大量 Java 开发者视为日常开发的首选工具。所谓 IDE(Integrated Development Environment,集成开发环境),就是把写代码所需的各类工具整合到同一个界面中。相比只用文本编辑器加命令行编译,IDE 能显著减少重复操作,让开发者把精力集中在业务逻辑上。

ztI10的博客 187

TransactionInactiveError:await 之后事务已经不活跃了

TransactionInactiveError 出现于 IndexedDB 事务已结束(非 active 状态)时调用 put、get 等操作。常见于 await 后或 setTimeout 中执行请求,因任务已完成,事务已提交或进入 finished 状态。请求必须在创建事务的同一任务中挂起,否则无效。解决方法:在 onsuccess 回调内同步发起后续操作,或提前将所有请求挂入事务。避免在异步上下文中使用事务操作,确保请求在事务活跃期内完成。

qq_40822000的博客 160

Docker部署linkding:HTTPS与数据备份

部署私人书签库,除了让页面能够打开,还需要考虑几个实际问题:重建容器后数据是否保留、登录和保存是否正常、升级前怎样备份,以及出现 502 或 CSRF 错误时从哪里检查。本文使用 Docker Compose 部署 linkding,采用默认 SQLite 数据库,通过 Caddy 提供 HTTPS,并完成账户创建、数据持久化验证和备份。如果重建后账户或书签消失,应先检查是否换了项目目录、改了挂载路径,或者启动了另一套实例,不要急着重新初始化。遇到权限问题,应根据日志确认具体目录和运行用户,再调整权限。

tedcloud123的博客 194

云计算课程小组PPT汇报任务书

36人分为6组,每组6人;形式:生活案例调研 + 理论解读;汇报时长7‑9min;PPT建议10‑14页。

Michelle8023的博客 229

企业官网源码与数据资产交接:四项权利、五组自动检查

企业官网源码交接不仅要看压缩包,还要核域名、云资源、构建脚本、数据库恢复、权限与密钥。

sweetofmine1的博客 187

软件项目交付时,源代码之外还应该交付哪些技术资料?

软件项目交付时,除可运行程序外,还应核对源代码、数据库结构、接口文档、部署运维、测试验收和账号资产。本文从实际交付环节拆解完整资料清单,并说明合同如何约定交付范围、知识产权与维护边界。

chengduyoushu的博客 309

Java 项目实战:交友项目后台系统——网关统一鉴权与 B 端管理后台

本次改造通过引入SpringCloud Gateway实现网关统一鉴权与路由,新建mf-admin管理端复用C端域服务(Dubbo调用),实现管理员登录与用户管理四件查询。核心套路:网关白名单+双体系JWT(同工具不同payload)、管理端零直连业务库、分页形态区分(MySQL自动count/Mongo手动count)、浏览器端独立聚合。踩坑12条,附自检清单与后续改造方向,可复用于多角色后台系统。

Wang的专栏 73

大表 ALTER TABLE 怎么做?NineData OnlineDDL 配置与执行实践

介绍NineData OnlineDDL大表变更实践,涵盖执行策略、切表控制、拷贝批次与可视化进度追踪。

dbasenewbie的博客 296

Axum 初步入门学习笔记

)自定义错误实现后,可以直接作为Result的ErrAxum 是一个构建在 Tokio、Tower、Hyper 之上的 Rust Web 框架,核心能力是:Router 路由、Extractor 提取器、Tower 中间件、IntoResponse 响应机制、State 共享状态。那张分层架构图是“地图”不是“说明书”:它帮你建立全局观,但 Service、Repository、数据库、Redis、MQ 都是你自己在 Handler 内部调用的业务/基础设施层,不是 Axum 自带的。

weixin_43219667的博客 206
上一篇: [Reference]SQL Server tempdb optimization tips
下一篇: 443 Chapter4.Designing Database Server Security Policies
aoding8407
博客等级 码龄11年 2粉丝 0原创
评论
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符  | 博主筛选后可见
 
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付:10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值