SQLServer數(shù)據(jù)庫(kù)技術(shù)架構(gòu)概述

1、SQLServer數(shù)據(jù)庫(kù)技術(shù)概述技術(shù)創(chuàng)新，變革未來(lái)智慧IT0102目 錄Contents安裝簡(jiǎn)介權(quán)限0304 備份恢復(fù)、遷移05 高可用01 簡(jiǎn)介01SQL server發(fā)展歷程1988年 微軟、Sybase和Ashton-Tate合作，在Sybase的基礎(chǔ)上生產(chǎn)出了在OS/2操作系統(tǒng)上使用的SQL Server 1.0；1995年 SQL Server 6.0發(fā)布,是一款完全由微軟獨(dú)立完成的產(chǎn)品；2000年 SQL server 2000發(fā)布（8.0），使用方便，伸縮性好，集成度高；2005年 SQL server 2005發(fā)布（9.0），全面的數(shù)據(jù)庫(kù)平臺(tái)，加入了分析報(bào)表和集成服務(wù)等功能；2

2、008年 SQL server 2008發(fā)布（10.0），增加了許多新特性并改進(jìn)了關(guān)鍵性功能；2008年 SQL server 2008 R2發(fā)布（10.0），在2008的基礎(chǔ)上強(qiáng)化了業(yè)務(wù)模塊的規(guī)模，增強(qiáng)了開(kāi)發(fā)能了等； 2012年 SQL server 2012發(fā)布（11.0），添加了always on等新功能；2017年 SQL server 2017發(fā)布（14.0），首個(gè)支持windows、Linux、macOS、Docker的SQLserver版本；01SQL server2012新特性高可用性和災(zāi)難恢復(fù) 靈活和有效的安全機(jī)制 超快性能集成服務(wù) 大數(shù)據(jù)支持01SQL server 201

3、2 各版本比較SQL server 2012 主要版本SQL server 2012 各版本詳解/qr-k/p/10559593.html02 安裝02獲取SQL server安裝包/02安裝要求_SQL server 2012操作系統(tǒng)要求Windows 7, Windows Server 2008 R2, Windows Server 2008 Service Pack 2,Windows Vista Service Pack 2硬件要求32 位系統(tǒng)具有Intel 1 GHz（或同等性能的兼容處理器）或速度更快的處理器（建 議使用 2 GHz 或速度更快的處理器）的計(jì)算機(jī)64 位系統(tǒng)1.4

4、GHz 或速度更快的處理器最低 1 GB RAM（建議使用 2 GB 或更大的RAM）2.2 GB 可用硬盤(pán)空間02安裝步驟_SQL server 201202SQL server 啟動(dòng)在Control PanelAdministrative ToolsServices，找到SQL Server (XXX)進(jìn)行啟動(dòng)；在SQL Server Configuration Manager中啟動(dòng)；在SSMS的Object Explorer中啟動(dòng)；在CMD下，直接運(yùn)行“sqlserver.exe”。02壓測(cè)測(cè)試工具：SQL Query Stress下載路徑：/ErikEJ/SqlQueryStress測(cè)

5、試環(huán)境：服務(wù)器版本 內(nèi)存CPU硬盤(pán)測(cè)試方案：服務(wù)器CPU的利用率用戶響應(yīng)時(shí)間02SQL server 管理工具SQL Server Management Studio：為數(shù)據(jù)庫(kù)管理和開(kāi)發(fā)人員提供圖形化和集成開(kāi) 發(fā)環(huán)境；于提供分析服務(wù)、集成服SQL Server Busi務(wù)、報(bào)表服務(wù)SQL Server 配置的服務(wù)，網(wǎng)絡(luò)和端口設(shè)置；SQL server 包含ness Intelligence Development Studio：用等項(xiàng)目的集成開(kāi)發(fā)環(huán)境；管理器：用于管理與SQL Server 相關(guān)聯(lián)哪些組件02SQL server 管理工具概覽SQLservermanagementstudio0

6、2SQL server 管理工具概覽對(duì)象資源管理器：對(duì)象資源管理器提供一個(gè)層次結(jié)構(gòu)用戶界面，用于查看和管理每個(gè) SQL Server 實(shí)例中的對(duì)象?！皩?duì)象資源管理器詳細(xì)信息”窗格顯示一個(gè)實(shí)例對(duì)象的表格視圖以及用于搜索特 定對(duì)象的功能。對(duì)象資源管理器的功能根據(jù)服務(wù)器的類(lèi)型稍有不同，但一般都包括用于數(shù)據(jù)庫(kù)的開(kāi)發(fā)功能和用于所有服務(wù)器類(lèi)型的管理功能。02SQL server 管理工具概覽SQLserver 配置管理工具02SQL server 管理工具概覽SQLserverprofiler03 權(quán)限03安全機(jī)制03權(quán)限權(quán)限兩個(gè)字，一個(gè)權(quán)力，一個(gè)限制。在軟件領(lǐng)域通俗的解釋就是哪些人可以對(duì)哪些資源做哪些操

7、作。在SQL Server中，”哪些人”，“哪些資源”,”哪些操作”則分別對(duì)應(yīng)SQL Server中的三個(gè)對(duì)象，分別 為主體(Principals),安全對(duì)象(Securable)和權(quán)限(Permissions)，而權(quán)力和限制則是對(duì)應(yīng)了SQL Server中的 GRENT和DENY。03權(quán)限主體“主體”是可以請(qǐng)求 SQL Server 資源的實(shí)體。主體可以按照作用范圍被分為三類(lèi):Windows級(jí)別主體：包括Windows 域登錄名和Windows 本地登錄名。服務(wù)器級(jí)別主體：包括服務(wù)器登錄名和服務(wù)器角色。數(shù)據(jù)庫(kù)級(jí)別主體：包括數(shù)據(jù)庫(kù)用戶、數(shù)據(jù)庫(kù)角色、固定數(shù)據(jù)庫(kù)角色以及應(yīng)用程序角色。可以看到主體包

8、括登錄名以及角色。03權(quán)限登錄賬號(hào)和數(shù)據(jù)庫(kù)用戶SQL Server的服務(wù)器和數(shù)據(jù)庫(kù)是兩個(gè)層次的概念，SQL Server的用戶也分為兩種，一種是服務(wù)器登陸賬號(hào)，另一種是數(shù)據(jù)庫(kù)用戶。一個(gè)人要操作SQL Server數(shù)據(jù)庫(kù)，首先要為其創(chuàng)建服務(wù)器登陸賬號(hào)，使得他可以登錄到服務(wù)器上，然后 還要在要操作的數(shù)據(jù)庫(kù)上創(chuàng)建和這個(gè)登陸賬號(hào)對(duì)應(yīng)的數(shù)據(jù)庫(kù)用戶?？梢越o登陸賬號(hào)賦予相應(yīng)權(quán)限，使得這個(gè)賬號(hào)可以執(zhí)行指定的管理服務(wù)器的任務(wù)。也可以給數(shù)據(jù)庫(kù)用戶 賦予相應(yīng)權(quán)限，使得這個(gè)數(shù)據(jù)庫(kù)用戶可以在這個(gè)數(shù)據(jù)庫(kù)中執(zhí)行指定的操作。服務(wù)器登陸賬號(hào)分為為Windows驗(yàn)證及SQL Server驗(yàn)證兩種。03權(quán)限登錄名是服務(wù)器級(jí)別的主體

9、，但無(wú)論是上級(jí)的主體都需要一個(gè)與之對(duì)應(yīng)的登錄名。對(duì)于Windows級(jí)別的主體來(lái)說(shuō)，Windows用 到登錄名中。而登錄名可以不映射到數(shù)據(jù)QL Server實(shí)例，所以每一個(gè)層的主體來(lái)說(shuō)，其用戶必須映射：述哪個(gè)層級(jí)的主體，因?yàn)樾枰卿浀絊戶會(huì)映射到登錄名。對(duì)于數(shù)據(jù)庫(kù)級(jí)別 庫(kù)用戶，如圖所示登錄名的映射關(guān)系03權(quán)限數(shù)據(jù)庫(kù)用戶用 的C于訪問(wèn)數(shù)據(jù)庫(kù)層面的對(duì)象。每一個(gè)數(shù)據(jù)庫(kù)用戶都必須要一個(gè)與之對(duì) 庫(kù)中，而登錄名存在實(shí)例級(jí)別的Master數(shù)據(jù)庫(kù)中(但SQL SERVER2012 數(shù)據(jù)庫(kù)級(jí)別)。的登錄名不一致，但由于這種做法會(huì)引起混淆，因此并不推薦，如下通 圖數(shù)據(jù)庫(kù)用戶是數(shù)據(jù)庫(kù)級(jí)別的主體,被用的登錄名。數(shù)據(jù)庫(kù)用

10、戶的信息存在數(shù)據(jù)ontained Database允許將登錄名也存在常來(lái)說(shuō)，數(shù)據(jù)庫(kù)層級(jí)的用戶可以和映射 所示。03權(quán)限角色角色可以看成是權(quán)限的集合體，為了方便權(quán)限管理，可以把一些常用權(quán)限賦予角色，然后再把角色賦予相關(guān) 用戶，則這些用戶就繼承了橘色中的所有權(quán)限。一般情況下，數(shù)據(jù)庫(kù)會(huì)內(nèi)置一些角色，用戶也可以創(chuàng)建自定 義角色。對(duì)角色進(jìn)行權(quán)限管理的方式與對(duì)用戶進(jìn)行權(quán)限管理的方式是相同的。角色在SQL Server中被分為三類(lèi)，分別為:內(nèi)置角色-這類(lèi)角色在服務(wù)器安裝時(shí)已經(jīng)默認(rèn)存在，其權(quán)限是固定的，并且不能被刪除 用戶自定義角色-這類(lèi)角色由用戶按照需求自定義創(chuàng)建應(yīng)用程序角色-這類(lèi)特殊角色用于管理應(yīng)用程序的

11、數(shù)據(jù)訪問(wèn)角色也分為兩個(gè)方面：服務(wù)器級(jí)別的角色，用于數(shù)據(jù)庫(kù)服務(wù)器方面的控制權(quán)限 數(shù)據(jù)庫(kù)級(jí)別的角色，用于數(shù)據(jù)庫(kù)中數(shù)據(jù)的控制權(quán)限。03權(quán)限服務(wù)器級(jí)，添加或刪除服務(wù) 數(shù)據(jù)庫(kù)連固定能更改分QL Server，且不錄。別的角色器級(jí)角色的權(quán)限作用域?yàn)榉?wù)器范圍。例如創(chuàng)建、修改、刪除數(shù)據(jù)庫(kù)，管理磁盤(pán)文件 接等等，都是需要服務(wù)器上的權(quán)限才能進(jìn)行操作。服務(wù)器角色具有一組固定的權(quán)限，并且適用于整個(gè)服務(wù)器范圍。 它們專(zhuān)門(mén)用于管理 S配給它們的權(quán)限。 可以在數(shù)據(jù)庫(kù)中不存在用戶帳戶的情況下向固定服務(wù)器角色分配登03權(quán)限數(shù)據(jù)庫(kù)庫(kù)中的哪些數(shù)建的“用戶定于每個(gè)數(shù)據(jù)庫(kù)定數(shù)據(jù)庫(kù)角色數(shù)據(jù)表、SQL義的數(shù)固 中。Db_ 不能滿級(jí)別的角

12、色據(jù)庫(kù)級(jí)角色的權(quán)限作用域?yàn)閿?shù)據(jù)庫(kù)范圍。例如可以訪問(wèn)哪個(gè)數(shù)據(jù)庫(kù)，可以訪問(wèn)哪個(gè)數(shù)據(jù)哪些視圖、哪些存儲(chǔ)過(guò)程等等，都需要數(shù)據(jù)庫(kù)上的權(quán)限才能進(jìn)行操作。Server存在兩種類(lèi)型的數(shù)據(jù)庫(kù)級(jí)角色：數(shù)據(jù)庫(kù)中預(yù)定義的“固定數(shù)據(jù)庫(kù)角色”和可以創(chuàng) 據(jù)庫(kù)角色”。定數(shù)據(jù)庫(kù)角色是SQL Server預(yù)定義的數(shù)據(jù)庫(kù)角色，具有數(shù)據(jù)庫(kù)級(jí)別的管理權(quán)限，并且存在owner 數(shù)據(jù)庫(kù)角色的成員可以管理固定數(shù)據(jù)庫(kù)角色成員身份。自定義數(shù)據(jù)庫(kù)角色是當(dāng)固足要求時(shí)，可以自定義數(shù)據(jù)庫(kù)角色。03權(quán)限安全對(duì)象安全對(duì)象是SQL Server安全對(duì)象分別屬于3個(gè)數(shù)據(jù)庫(kù)引擎授權(quán)系統(tǒng)控制對(duì)其訪問(wèn)的資源。層次：服務(wù)器、數(shù)據(jù)庫(kù)、架構(gòu)。03權(quán)限架構(gòu)概述：架構(gòu)是數(shù)據(jù)庫(kù)

13、對(duì)象的容器 特點(diǎn)：多個(gè)數(shù)據(jù)庫(kù)用戶可以共享一個(gè)默認(rèn)的架構(gòu)；單個(gè)架構(gòu)可以包含多個(gè)數(shù)據(jù)庫(kù)用戶擁有的對(duì)象； 架構(gòu)的所有權(quán)和架構(gòu)內(nèi)的安全對(duì)象可以轉(zhuǎn)移； 對(duì)象可以在不同的架構(gòu)之間移動(dòng)；可以刪除數(shù)據(jù)庫(kù)用戶，而不刪除相應(yīng)架構(gòu)中的對(duì)象。03權(quán)限架構(gòu)所有者和權(quán)限通過(guò)用戶架構(gòu)分離，可實(shí)現(xiàn)管理數(shù)據(jù)庫(kù)對(duì)象權(quán)限的更大靈活性。因?yàn)閷?duì)象不再綁定到用戶賬號(hào)上，所 以你根本不用擔(dān)心當(dāng)一個(gè)賬號(hào)被刪除時(shí)需要變換對(duì)象的擁有者。默認(rèn)情況下，當(dāng)開(kāi)發(fā)人員創(chuàng)建了一個(gè)對(duì)象時(shí)，該對(duì)象并不屬于開(kāi)發(fā)人員而屬于一個(gè)數(shù)據(jù)庫(kù)架構(gòu)。03權(quán)限內(nèi)置架構(gòu)SQL Server創(chuàng)建了十個(gè)預(yù)定義的架構(gòu)，它們與內(nèi)置數(shù)據(jù)庫(kù)用戶和角色具有相同的名稱。這些架構(gòu)主要 用于向后兼容

14、性。如果您不需要與固定數(shù)據(jù)庫(kù)角色具有相同名稱的架構(gòu)，則可以刪除它們。您不能刪除下 列架構(gòu)：dbo：dbo 是新創(chuàng)建的數(shù)據(jù)庫(kù)的默認(rèn)架構(gòu)。 dbo 架構(gòu)由dbo 用戶帳戶擁有。 默認(rèn)情況下，使用 CREATE USER命令創(chuàng)建的用戶的默認(rèn)架構(gòu)為dbo，但是分配了dbo 架構(gòu)的用戶不繼承dbo 用戶帳戶的權(quán)限。 guestsys：為系統(tǒng)對(duì)象而保留的。INFORMATION_SCHEMA：為系統(tǒng)對(duì)象而保留的。如果從模型數(shù)據(jù)庫(kù)中刪除這些架構(gòu)，它們將不會(huì)顯示在新數(shù)據(jù)庫(kù)中。03權(quán)限權(quán)限Select 、dGrnat 、 grelete、update、referenceant option、 deny、revo

15、ke04 備份恢復(fù)、遷移04備份恢復(fù)為什么要備份提高數(shù)據(jù)的安全性宕機(jī)之后恢復(fù)的高可用性備份的對(duì)象有哪些數(shù)據(jù)庫(kù) 文件 磁盤(pán)04備份恢復(fù)什么時(shí)候需要備份系統(tǒng)數(shù)據(jù)庫(kù)？修改master數(shù)據(jù)庫(kù)之后master數(shù)據(jù)庫(kù)記錄了系統(tǒng)的所有系統(tǒng)信息。包括實(shí)例范圍的元數(shù)據(jù)（如登陸賬戶）、端點(diǎn)、鏈接服務(wù)器和系統(tǒng)配置設(shè)置。還記錄了其他數(shù)據(jù)庫(kù)是否存在及這些數(shù)據(jù)庫(kù)文件的位置 還記錄了sql server 的初始化信息。修改model數(shù)據(jù)庫(kù)之后model數(shù)據(jù)庫(kù)用作在實(shí)例上創(chuàng)建所有數(shù)據(jù)庫(kù)的模板。每次啟動(dòng)sql server 時(shí)都會(huì)創(chuàng)建tempdb，所以model數(shù)據(jù)庫(kù)必須始終存在于sql server 系統(tǒng)中。如果修改mode

16、l數(shù)據(jù)庫(kù)，之后創(chuàng)建的所有的數(shù)據(jù)庫(kù)都將繼承這些修改。修改msdb數(shù)據(jù)庫(kù)之后msdb數(shù)據(jù)庫(kù)包含sql server 代理用來(lái)計(jì)劃警報(bào)和作業(yè)的信息。04備份恢復(fù)什么時(shí)候需要備份用戶數(shù)據(jù)庫(kù)？創(chuàng)建數(shù)據(jù)庫(kù)之后創(chuàng)建索引之后清理事務(wù)日志之后執(zhí)行大容量的數(shù)據(jù)操作之后04備份恢復(fù)le）uncate lo志方式）trunca（Bulk-s (bcp agte loglogged ）nd BULK INSERT)數(shù)據(jù)庫(kù)的恢復(fù)模式簡(jiǎn)單模式（Simp Checkpoint with tr 非緊要最徹底的收縮日完整模式（Full Checkpoint without 緊要大容量日志恢復(fù) Bulk load operatio

17、n SELECT INTOCreate/drop/rebuild index04備份恢復(fù)sql server 提供如下四種數(shù)據(jù)備份類(lèi)型完整備份差異備份日志備份文件和文件組備份04備份恢復(fù)sql server 備份加密1、在使用BACKUP 語(yǔ)句時(shí)，添加 PASSWORD 參數(shù)【此方法適用于sql server 2012 以前的版本（不包含2012）】2、對(duì)數(shù)據(jù)庫(kù)啟用透明數(shù)據(jù)加密（TDE）【此方法適用于 sql server 2008 及以后的版本（含2008）】3、直接對(duì)備份進(jìn)行加密【此方法適用于sql server 2014 及以后的版本】04備份恢復(fù)透明數(shù)據(jù)加密（TDE）1、在 maste

18、r 數(shù)據(jù)庫(kù)中，添加數(shù)據(jù)庫(kù)主密鑰：2、在 master 數(shù)據(jù)庫(kù)中，添加加密數(shù)據(jù)庫(kù)用的證書(shū)3、在要加密的數(shù)據(jù)庫(kù)中，設(shè)置證書(shū)以及加密算法：4、對(duì)要加密的數(shù)據(jù)庫(kù)啟用加密：04備份恢復(fù)SQL server 異地備份直接備份到異地備份到本地，再拷貝到異地04備份恢復(fù)sql server 備份選項(xiàng)04備份恢復(fù)sql ser備備備備備ver 完善的備份流程份的合理性 份連續(xù)性驗(yàn)證 份可用性驗(yàn)證 份恢復(fù)驗(yàn)證份歷史和作業(yè)歷史自動(dòng)清理04備份恢復(fù)sql server 備份1、備份類(lèi)型的選擇2、備份方式的選擇3、備份參數(shù)的選擇4、備份目標(biāo)的選擇5、如何自動(dòng)化備份04備份恢復(fù)sql server 備份的一些限制1、無(wú)法

19、備份離線數(shù)據(jù)2、完整備份過(guò)程中的限制在完整備份的過(guò)程中，不允許執(zhí)行下列操作：創(chuàng)建或刪除數(shù)據(jù)庫(kù)文件在收縮數(shù)據(jù)庫(kù)操作過(guò)程中截?cái)辔募?4備份恢復(fù)sql server 還原1、系統(tǒng)數(shù)據(jù)庫(kù)還原2、用戶數(shù)據(jù)庫(kù)還原3、加密數(shù)據(jù)庫(kù)還原04備份恢復(fù)sql server 還原參數(shù)04備份恢復(fù)sql server 遷移1、備份還原系統(tǒng)數(shù)據(jù)庫(kù)用戶數(shù)據(jù)庫(kù)2、高可用3、腳本創(chuàng)建、導(dǎo)入導(dǎo)出4、鏈接服務(wù)器5、分離附加04備份恢復(fù)系統(tǒng)數(shù)據(jù)庫(kù)還原master數(shù)據(jù)庫(kù)損壞時(shí)重新初始化還原master數(shù)據(jù)庫(kù)還原msdb、model數(shù)據(jù)庫(kù)master數(shù)據(jù)庫(kù)沒(méi)損壞還原master數(shù)據(jù)庫(kù)還原msdb、model數(shù)據(jù)庫(kù)將master所有對(duì)象生

20、成腳本、目標(biāo)端執(zhí)行04備份恢復(fù)archar(100)har(max)taBaseName-數(shù)據(jù)庫(kù)多的時(shí)候采用游標(biāo)獲取+dbname+Backup+dbname+_full.bakNOINIT, NAME = +dbname+-完整數(shù)據(jù)庫(kù)備份, OUNLOAD, STATS = 10用戶數(shù)據(jù)庫(kù)還原-1：完整備份declare dbname v declare sql nvarc set dbname = Da set sql = -+dbname+_full BACKUP DATABASE TO DISK = D:DBWITH NOFORMAT, SKIP, NOREWIND, N GOprin

21、t sql04備份恢復(fù)me varchar(100)rchar(max)DataBaseNamebname+_fullSE +dbname+:DBBackup+dbname+_full.bak WITH FILE = 1, ase_Name TO ND:DataBase+dbname+.mdf, ase_Name_log TO ND:DataBase+dbname+_log.ldf,用戶數(shù)據(jù)庫(kù)還原-2：完整備份還原 declare dbna declare sql nva set dbname = set sql = -RESTORE +d RESTORE DATABA FROM DISK =

22、 D MOVE NDataB MOVE NDataBNORECOVERY, NOUNLOAD, REPLACE, STATS = 10 GOprint sql04備份恢復(fù)rchar(max)DataBaseNamediffSE +dbname+DBBackup+dbname+_diff.bakTIAL , NOFORMAT, NOINIT, NAME = N+dbname+-差異數(shù)據(jù)庫(kù)備份,用戶數(shù)據(jù)庫(kù)還原-3：差異備份declare dbname varchar(100)declare sql nva set dbname = set sql = -+dbname+_ BACKUP DATABA TO DISK = ND:WITH DIFFERENSKIP, NOREWIND, NOUNLOAD, STATS = 10 GOprint sql04備份恢復(fù)rchar(max)DataBaseNamebname+_fullSE +dbname+:DBBackup+dbname+_diff.bak WITH FILE = 1,TS = 10