实操演练第003讲-数据通途:客户端连接SQL Server的完美攻略

SQL Server简介

基本概念

SQL Server是由微软公司开发的关系型数据库管理系统。它基于SQL（Structured Query Language，结构化查询语言）来管理和操作数据。SQL Server可以存储大量结构化数据，如客户信息、订单记录、库存数据等，并且能够高效地对这些数据进行查询、更新、插入和删除等操作。

主要特点

高性能

优化的查询引擎：SQL Server的查询优化器能够自动分析SQL语句，选择最优的执行计划。例如，当执行一个复杂的连接查询时，优化器会根据表的大小、索引情况等因素，决定是先进行表扫描还是先使用索引，以最快的速度返回查询结果。
并行处理能力：它可以利用多核处理器的优势，对查询和数据操作进行并行处理。在处理大规模数据集时，如对一个包含数百万条记录的表进行聚合查询，SQL Server可以将任务分解，分配给多个处理器核心同时执行，大大缩短了查询时间。

高可用性

故障转移群集：通过在多台服务器上配置故障转移群集，当主服务器出现故障时，可以自动将数据库服务切换到备用服务器上。这个过程对用户是透明的，几乎不会影响到应用程序的正常运行。例如，在银行的交易系统中，使用故障转移群集可以确保即使服务器硬件出现问题，客户的交易数据仍然能够被安全地处理。
数据库镜像：这是一种高可用性解决方案，通过在两台服务器之间实时同步数据库数据。当主数据库服务器出现问题时，镜像服务器可以接管数据库服务。它支持高安全模式和高性能模式，可以根据不同的业务需求进行选择。

安全性

身份验证和授权：SQL Server支持多种身份验证方式，包括Windows身份验证和SQL Server身份验证。通过精细的权限控制，可以为不同的用户和角色分配不同的权限。例如，数据库管理员可以拥有完全控制数据库的权限，而普通用户可能只能查询数据，不能进行修改和删除操作。

数据加密：它提供了透明数据加密（TDE）功能，可以对整个数据库或数据文件进行加密。这样即使数据文件被盗取，没有加密密钥也无法读取其中的数据内容。此外，还可以对数据传输过程进行加密，防止数据在传输过程中被窃取。

易用性

图形化管理工具：SQL Server Management Studio（SSMS）是一个功能强大的图形化管理工具。通过它，用户可以方便地连接到数据库服务器，创建和管理数据库、表、视图等对象。例如，可以通过图形界面直观地设计表结构，添加字段、设置数据类型和约束条件等。同时，还可以在SSMS中编写和执行SQL脚本，查看查询结果。
丰富的文档和社区支持：微软为SQL Server提供了详细的官方文档，涵盖了从安装配置到高级功能应用的各个方面。此外，还有大量的在线社区，如Stack Overflow等，用户可以在这些社区中提问和分享经验，解决在使用过程中遇到的问题。
o

应用场景

企业级应用

ERP系统（企业资源规划系统）：SQL Server可以作为ERP系统的后端数据库，存储企业的生产、销售、财务等各个部门的数据。例如，在制造业企业的ERP系统中，SQL Server数据库可以记录原材料的采购信息、生产过程中的物料消耗、产品的销售订单等数据，并且能够快速响应ERP系统的各种数据查询和更新请求，支持企业的日常运营和决策分析。
CRM系统（客户关系管理系统）：用于存储客户的基本信息、购买历史、沟通记录等数据。销售团队可以通过CRM系统查询客户数据，制定销售策略。SQL Server能够高效地处理大量客户数据的存储和查询，同时保证数据的安全性和一致性。

数据分析和商业智能

数据仓库构建：利用SQL Server的分析服务，可以构建数据仓库。数据仓库是将企业各个业务系统的数据进行整合、清洗和转换后，存储在一个统一的结构中，用于数据分析。例如，企业可以将销售系统、客服系统和市场推广系统中的数据导入数据仓库，通过数据挖掘算法发现客户购买行为的模式，为精准营销提供依据。
报表生成：通过SQL Server的报表服务，企业可以生成各种业务报表。比如，财务部门可以生成月度财务报表，包括收入、支出、利润等指标；市场部门可以生成市场活动效果评估报表，展示不同市场活动对销售额的贡献等。这些报表可以以多种格式（如PDF、Excel等）导出，方便不同部门的人员查看和分析。

Web应用

网站后端数据库：在许多网站应用中，SQL Server作为后端数据库，存储用户信息、文章内容、评论等数据。例如，在一个新闻网站中，SQL Server数据库可以存储新闻文章的标题、内容、作者、发布时间等信息。当用户访问网站时，网站的前端应用通过SQL查询从数据库中获取新闻数据，并展示给用户。同时，当用户提交评论时，SQL Server可以快速地将评论数据插入到数据库中，并且能够处理高并发的用户访问，保证网站的响应速度。

SQL Server安装

准备工作

硬件要求

处理器：推荐使用多核处理器，最低要求是1GHz的x86或x64处理器，但实际使用中，为了保证性能，建议使用2GHz或更高主频的处理器。
内存：根据SQL Server的版本和应用场景有所不同。对于开发版或小型应用的标准版，至少需要4GB内存；对于企业版，尤其是处理大量数据和高并发请求的场景，建议配置16GB或更多内存。
存储空间：需要足够的磁盘空间来安装SQL Server软件和存储数据库文件。一般来说，安装程序本身可能需要2 - 3GB的空间，而数据库文件的大小则取决于数据量。例如，一个中等规模的企业数据库可能需要几十GB的存储空间。

软件要求

操作系统：SQL Server支持多种Windows操作系统版本，如Windows Server 2016、2019等服务器操作系统，以及Windows 10等客户端操作系统（对于开发和测试用途）。需要确保操作系统的版本与所要安装的SQL Server版本兼容。
.NET Framework：部分SQL Server版本可能需要特定版本的.NET Framework。在安装前，要检查系统是否已安装所需的.NET Framework版本，如果没有，则需要先进行安装。例如，SQL Server 2019可能需要.NET Framework 4.8。

权限要求

安装SQL Server需要具有管理员权限。在安装过程中，会进行文件写入、注册表修改等一系列操作，只有管理员账户才有足够的权限来完成这些任务。所以，在开始安装之前，要确保以管理员身份登录操作系统。

安装步骤（以SQL Server 2022为例）

获取安装介质

可以从微软官方网站下载SQL Server 2022的安装文件。在下载页面，根据自己的需求（试用本地或云中的 SQL Server或下载免费的专用版本等）选择相应的版本，本文选择下载免费的专用版本Developer版。下载完成后，通常会得到一个ISO镜像文件或一个可执行的安装程序（.exe文件）。
官网下载地址：https://www.microsoft.com/zh-CN/sql-server/sql-server-downloads
百度网盘下载地址：https://pan.baidu.com/s/1ir8NctS0MLOLV_4ZOCwtdw?pwd=dpdi

运行安装程序

下载好安装包找到SQL2022-SSEI-Dev.exe文件并运行

选择”自定义”安装类型可以逐步完成SQL Server安装向导

设置媒体位置即安装程序包存放路径,点击安装可下载安装程序包

下载成功后会弹出如下图所示对话框

选择左侧安装点击后选择全新SQL Server独立安装或向现有安装添加功能

版本：选择要安装的SQL Server2022版本为Developer后点击下一步

许可条款：勾选我接受许可条款，点击下一步

Microsoft更新：不用勾选使用Microsoft更新检查更新,直接点击下一步

安装规则检查：直接点击下一步（安装程序会进行一系列的规则检查，以确保系统环境符合安装要求。如果检查通过，会显示安装配置规则界面，点击下一步继续安装；如果有错误或警告，需要根据提示进行相应的调整，如修改配置、安装缺失的组件等）

适用SQL Server的Azure扩展：取消勾选,点击下一步

功能选择：可根据自己需要勾选,点击下一步（在功能选择界面，可以选择要安装的SQL Server组件。常见的组件包括数据库引擎服务，SQL Server Management Studio（SSMS）分析服务，报表服务等，如果需要进行数据分析和数据挖掘，可以安装分析服务。对于需要生成报表的应用场景，可以安装报表服务。根据实际需求选择相应的组件，如果不确定，可以选择默认安装，它会安装一些常用的组件）

实例配置：默认实例点击下一步（在实例配置界面，需要选择安装SQL Server的实例类型。有两种实例类型可供选择：默认实例和命名实例，默认实例是指在计算机上只有一个SQL Server实例的情况。当连接到这个实例时，不需要指定实例名称，直接使用服务器的名称即可。例如，服务器名称为ServerA，那么连接字符串就是ServerA。命名实例是指在同一台计算机上可以安装多个SQL Server实例，每个实例都有一个唯一的名称。例如，在ServerA上安装了一个名为MyInstance的命名实例，那么连接字符串就是ServerA\MyInstance。如果是在开发环境中，需要安装多个不同版本或配置的SQL Server进行测试，可以选择命名实例）

Polybase配置：不用配置直接点击下一步

服务器配置：不用配置直接下一步（服务器配置”界面，需要设置服务账户。服务账户是SQL Server服务运行的身份。可以选择使用内置的账户（如Network Service），也可以指定一个域账户或本地账户。使用域账户可以提供更好的安全性和管理性，尤其是在企业环境中，可以通过域策略来管理服务账户的权限，还需要设置SQL Server代理服务。SQL Server代理用于执行计划任务，如定期备份数据库、运行维护任务等。同样需要为SQL Server代理服务指定一个服务账户）

数据库引擎配置：1.勾选混合模式,2.输入密码,3.添加当前用户点击下一步（数据库引擎服务是核心组件，必须安装。它提供了数据存储和管理的基本功能，在数据库引擎配置界面，主要有两个配置选项：身份验证模式和排序规则。身份验证模式有Windows身份验证模式和SQL Server和Windows身份验证模式两种。Windows身份验证模式安全性较高，它利用Windows系统的账户和密码进行身份验证。在这种模式下，只有Windows用户和组可以连接到SQL Server。SQL Server和Windows身份验证模式则同时支持Windows身份验证和SQL Server自身的身份验证。如果应用程序需要使用SQL Server身份验证连接到数据库，或者需要为非Windows用户（如一些非Windows平台的应用程序）提供访问权限，可以选择这种模式。在选择SQL Server和Windows身份验证模式时，需要添加至少一个SQL Server登录名，可以输入sa作为登录名，并设置一个强密码。排序规则决定了数据的排序和比较方式。不同的排序规则会影响字符串数据的存储和检索。一般情况下，可以选择默认的排序规则，除非有特殊的语言或业务需求）

Analysis Services配置：1勾选多维模式,2添加当前用户点击下一步

功能配置规则：直接点下一步进入准备安装，点击安装

安装进度：等待安装即可（安装程序会开始安装SQL Server的各个组件，并显示安装进度。这个过程可能需要一些时间，具体时间取决于计算机的性能和安装的组件数量。安装完成后，会弹出安装完成的提示界面）

完成安装：点击关闭即可

SSMS安装

SSMS介绍

SQL Server Management Studio (SSMS) 是一种集成环境，用于管理从 SQL Server 到 Azure SQL 数据库的任何 SQL 基础结构。 SSMS 提供用于配置、监视和管理 SQL Server 和数据库实例的工具。 使用 SSMS 部署、监视和升级应用程序使用的数据层组件，以及生成查询和脚本。

SSMS获取

官网下载地址：
https://learn.microsoft.com/zh-cn/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-ver16

百度网盘下载地址：
https://pan.baidu.com/s/1ir8NctS0MLOLV_4ZOCwtdw?pwd=dpdi

SSMS安装

双击SSMS-Setup-CHS.exe安装文件进行安装，如果报如下错误请下载百度网盘SSMS-Setup-ENU.exe即可完成安装

SSMS-Setup-ENU.exe安装成功

SQL Server连接

SSMS连接SQL Server

启动SQL Server Management Studio

Windows身份验证：勾选Trust server certificate即可登录

SQL Server身份验证：输入用户名和密码，勾选Trust server certificate即可登录

Navicat连接SQL Server

自定义SQL Server驱动

找到Navicat安装目录Navicat\Navicat Premium 12下有sqlncli_x64.msi文件双击可以进行安装默认为SQL Server Native Client 10.0

SQL Server Native Client 11.0下载安装
官网下载地址：
https://www.microsoft.com/zh-cn/download/details.aspx?id=56041

百度网盘下载地址：
https://pan.baidu.com/s/1ir8NctS0MLOLV_4ZOCwtdw?pwd=dpdi

安装很简单直接下一步…完成,这里就省略…

Windows身份验证

主机输入localhost,选择Windows验证能正常连接

主机输入127.0.0.1,选择Windows验证能报错

解决办法：打开Sql Server Configuration Manager->SQL Server网络配置->
MSSQLSERVER的协议->启动TCP/IP

SQL Server服务->重启SQL Server服务既可连接成功

本人更改过host,所以ip是通过ipconfig获取到的

SQL Server身份验证

输入用户名和密码即可登录

PDI(kettle)连接SQL Server

下载 Microsoft SQL Server JDBC 驱动程序

官网下载地址：
https://learn.microsoft.com/zh-cn/sql/connect/jdbc/download-microsoft-jdbc-driver-for-sql-server?view=sql-server-ver16
百度网盘下载地址：
https://pan.baidu.com/s/1ir8NctS0MLOLV_4ZOCwtdw?pwd=dpdi

Microsoft JDBC Driver 12.8 for SQL Server简介

支持的操作系统包括：
Windows Server 2012
Windows Server 2012 R2
Windows Server 2016
Windows Server 2019
Windows Server 2022
Windows 8.1
Windows 10
Windows 11
Linux
macOS
JRE 系列版本可实现最大程度支持的驱动程序JAR
8+ | mssql-jdbc-12.8.1.jre8.jar
11+ | mssql-jdbc-12.8.1.jre11.jar

有关每个组件的详细信息，请参阅联机文档
Microsoft JDBC Driver for SQL Server 文档：https://aka.ms/mssql-jdbc
Microsoft SQL Server JDBC 驱动程序放到PDI对应目录
将下载的驱动程序mssql-jdbc-12.8.1.jre8.jar (根据自己JDK版本选择是jre8还是jre11对应的jar) 放到 \data-integration\lib启动PDI

SQL Server身份验证

JDBC连接方式配置如图：

报如下错误解决办法：选项中增加Encrypt=false

JNDI连接方式配置(\data-integration\simple-jndi\jdbc.properties)
##SQL Server身份认证JNDI方式配置
sqlserver_auth/type=javax.sql.DataSource
sqlserver_auth/driver=com.microsoft.sqlserver.jdbc.SQLServerDriver
sqlserver_auth/url=jdbc:sqlserver://172.16.20.116:1433;DatabaseName=master;integratedSecurity=false;Encrypt=false
sqlserver_auth/user=sa
sqlserver_auth/password=your sa password

Windows身份验证

JDBC连接方式配置：勾选 Use Intergrated Security代表用Windows身份验证登录，此时不需要填写用户名和密码，如下配置发现报错

解决方法:
将下载的Microsoft SQL Server JDBC 驱动程序
sqljdbc_12.8.1.0_chs\sqljdbc_12.8\chs\auth\x64路径下的
mssql-jdbc_auth-12.8.1.x64.dll文件放到JDK\jre\bin和JDK\bin目录下

重启PDI问题解决

JNDI连接方式配置
##Windows身份认证JNDI方式配置((\data-integration\simple-jndi\jdbc.properties))
windows_auth/type=javax.sql.DataSource
windows_auth/driver=com.microsoft.sqlserver.jdbc.SQLServerDriver
windows_auth/url=jdbc:sqlserver://172.16.20.116:1433;DatabaseName=master;integratedSecurity=true;Encrypt=false
#windows_auth/user=sa
#windows_auth/password=your sa password(Windows认证方式不需要用户名和密码)

HOP连接SQL Server

SQL Server身份验证

使用数据库内置安全特性不勾选代表使用SQL Server身份验证模式连接，
apache-hop-client-2.11.0版本默认带了JDBC驱动（mssql-jdbc-12.6.3.jre11.jar）不用单独安装，HOP连接配置配置如图：

Windows身份验证

勾选 Use Intergrated Security代表用Windows身份验证登录，此时不需要填写用户名和密码，配置相关连接后发现报错，报错信息如图

解决方法：将下载的Microsoft SQL Server JDBC 驱动程序
sqljdbc_12.8.1.0_chs\sqljdbc_12.8\chs\auth\x64路径下的
mssql-jdbc_auth-12.8.1.x64.dll文件放到\jdk-23_windows-x64_bin\jdk-23.0.1\bin目录下

替换apache-hop-client-2.11.0\hop\lib\jdbc路径下mssql-jdbc-12.6.3.jre11.jar
为mssql-jdbc-12.8.1.jre11.jar

重启HOP再次连接成功

Java连接SQL Server

Java连接SQL Server代码示例