sql server 快照
In the last articles, we have learned Configuring Snapshot and Transactional SQL Server replication and Configuring Peer to Peer and Merge SQL replication. Now, once we configured SQL Server replication, there will be some instances where we need to modify the SQL Server replication configuration as per the project updates. In this article, we will learn a few modifications in the existing SQL Replication such as Add new article, drop an article and change the Snapshot folder path and data filter in the current SQL Server replication.
在上一篇文章中，我們學(xué)習(xí)了配置快照和事務(wù)型SQL Server復(fù)制以及配置點(diǎn)對(duì)點(diǎn)和合并SQL復(fù)制 。 現(xiàn)在，一旦我們配置了SQL Server復(fù)制，就會(huì)有一些實(shí)例需要根據(jù)項(xiàng)目更新來(lái)修改SQL Server復(fù)制配置。 在本文中，我們將學(xué)習(xí)對(duì)現(xiàn)有SQL復(fù)制的一些修改，例如添加新文章，刪除文章并更改當(dāng)前SQL Server復(fù)制中的Snapshot文件夾路徑和數(shù)據(jù)過(guò)濾器。
![]()
將新文章添加到現(xiàn)有已發(fā)布的數(shù)據(jù)庫(kù)中 (Add new Articles into the existing published database)
In this section, we will learn, Add a new article into the existing published database. Once we created the publication, the publication contains few sets of database objects like tables, views and stored procedures. As per the project requirement sometimes we need to create new tables or views or stored procedures into the existing published database for some calculations or storing specific limited data. If we also wish to replicate those tables or views or stored procedures into the current SQL replication, then we need to add new database objects into the published database. This section will help to insert new articles into the existing published database of the SQL Server replication.
在本節(jié)中，我們將學(xué)習(xí)將新文章添加到現(xiàn)有已發(fā)布的數(shù)據(jù)庫(kù)中。 創(chuàng)建發(fā)布后，發(fā)布包含幾組數(shù)據(jù)庫(kù)對(duì)象，例如表，視圖和存儲(chǔ)過(guò)程。 根據(jù)項(xiàng)目要求，有時(shí)我們需要在現(xiàn)有的已發(fā)布數(shù)據(jù)庫(kù)中創(chuàng)建新表或視圖或存儲(chǔ)過(guò)程，以進(jìn)行一些計(jì)算或存儲(chǔ)特定的有限數(shù)據(jù)。 如果我們還希望將這些表或視圖或存儲(chǔ)過(guò)程復(fù)制到當(dāng)前SQL復(fù)制中，則需要將新的數(shù)據(jù)庫(kù)對(duì)象添加到已發(fā)布的數(shù)據(jù)庫(kù)中。 本節(jié)將幫助將新文章插入到SQL Server復(fù)制的現(xiàn)有已發(fā)布數(shù)據(jù)庫(kù)中。
I have already configured the transactional SQL replication on the node SQL1 for the database AdventuresWorksLT2012 as shown in the following fig.
我已經(jīng)在數(shù)據(jù)庫(kù)AdventuresWorksLT2012的節(jié)點(diǎn)SQL1上配置了事務(wù)SQL復(fù)制，如下圖所示。
Before adding a new article, first, we need to check, which are the database objects are already part of the SQL Server replication. Right-click the configured publication of the database and Go to Publication properties where we will get a list of articles which are part of the SQL replication.
在添加新文章之前，首先，我們需要檢查哪些數(shù)據(jù)庫(kù)對(duì)象已經(jīng)是SQL Server復(fù)制的一部分。 右鍵單擊已配置的數(shù)據(jù)庫(kù)發(fā)布，然后轉(zhuǎn)到發(fā)布屬性 ，我們將在其中獲得屬于SQL復(fù)制的文章列表。
In the following fig. articles which ticked are already part of SQL Server replication.
在下圖。 勾選的文章已經(jīng)是SQL Server復(fù)制的一部分。
The new articles which need to add into publication database, First, un-check Show only checked items in the list option as shown in the following fig. we will get a list of all the objects from the database. We will select new tables ProductCategory and ProductDescription from the following list and click OK.
需要添加到發(fā)布數(shù)據(jù)庫(kù)中的新文章，首先，取消選中l(wèi)is t選項(xiàng)中的“僅顯示選中的項(xiàng)目”，如下圖所示。 我們將從數(shù)據(jù)庫(kù)中獲取所有對(duì)象的列表。 我們將從下面的列表中選擇新表ProductCategory和ProductDescription ，然后單擊OK。
Once we added the new list of articles in the current publication, right-click Configured publication and select Reinitialize all Subscription as the following.
一旦我們?cè)诋?dāng)前出版物中添加了新的文章列表，請(qǐng)右鍵單擊“配置的出版物”，然后選擇“ 重新初始化所有訂閱 ”，如下所示。
Click on the Use a new snapshot; this option will able to create a new snapshot till time and click on Mark for Reinitialization.
單擊“ 使用新快照” ； 該選項(xiàng)將能夠創(chuàng)建一個(gè)新的快照，直到出現(xiàn)時(shí)間，然后單擊“ 標(biāo)記為重新初始化” 。
Right-click configured publication and Select Launch Replication Monitor.
右鍵單擊配置的發(fā)布，然后選擇啟動(dòng)復(fù)制監(jiān)視器 。
Go to Agent tab as shown in the following.
轉(zhuǎn)到代理選項(xiàng)卡 ，如下所示。
Right-click on snapshot section and click start agent, this process will create a new snapshot after adding the new articles in the current publication and will be used for the further SQL replication process.
右鍵單擊快照部分，然后單擊啟動(dòng)代理 ，此過(guò)程將在當(dāng)前出版物中添加新文章后創(chuàng)建一個(gè)新快照，并將用于進(jìn)一步SQL復(fù)制過(guò)程。
After running agent, we can check Last start time and last action and duration of snapshot run as shown in the following window.
運(yùn)行代理后，我們可以檢查快照的 上次啟動(dòng)時(shí)間 ， 上次操作和快照的持續(xù)時(shí)間，如下窗口所示。
After creating a new snapshot, we will verify the status of the snapshot agent. Right-click the publication and select View Snapshot Agent Status as the following.
創(chuàng)建新快照后，我們將驗(yàn)證快照代理的狀態(tài)。 右鍵單擊發(fā)布，然后選擇“ 查看快照代理狀態(tài) ”，如下所示。
Snapshot agent status is in a healthy state. A snapshot of 9 articles generated as shown on the following fig.
快照代理狀態(tài)為健康狀態(tài)。 生成的9個(gè)文章的快照，如下圖所示。
After adding new articles in the current publication, we can verify that new articles have added to the subscriber server SQL2 as shown in the following fig.
在當(dāng)前出版物中添加新文章之后，我們可以驗(yàn)證新文章已添加到訂戶(hù)服務(wù)器SQL2中，如下圖所示。
![]()
從出版物中刪除表 (Drop Table from the publication)
In this section, we will learn about to drop a table from the publication.
在本節(jié)中，我們將學(xué)習(xí)從出版物中刪除表格。
The table Customer of the database AdventureWorksLT_2012 is part of the SQL Server replication. We will try to drop it as the following; however, it will not allow us to drop it because a table configured as an article in the SQL replication and It will throw an error as shown in the following.
數(shù)據(jù)庫(kù)AdventureWorksLT_2012的 客戶(hù)表是SQL Server復(fù)制的一部分。 我們將嘗試如下刪除它； 但是，它不允許我們刪除它，因?yàn)樵赟QL復(fù)制中配置為項(xiàng)目的表會(huì)引發(fā)錯(cuò)誤，如下所示。
Before dropping a table, first, we need to remove a table from the publication. Right-click publication and Go to Publication Properties. So, we want to drop a table Customer, so Let’s uncheck it from the list of objects to publish section. Once we uncheck it, we will get a pop-up warning click Yes.
在刪除表之前，首先，我們需要從發(fā)布中刪除表。 右鍵單擊發(fā)布，然后轉(zhuǎn)到發(fā)布屬性 。 因此，我們要?jiǎng)h除一個(gè)表Customer ，因此讓我們從要發(fā)布的對(duì)象列表中取消選中它。 取消選中它后，我們將彈出警告，單擊是 。
Once clicked on Yes, we will get a pop-up to create a new snapshot. Select Use a new snapshot and click Mark for Reinitialization.
單擊“是”后，我們將彈出一個(gè)窗口以創(chuàng)建一個(gè)新快照。 選擇“ 使用新快照” ， 然后單擊“ 標(biāo)記為重新初始化” 。
Right-click publication and Go to Replication monitor. Right-click view details.
右鍵單擊發(fā)布，然后轉(zhuǎn)到“復(fù)制”監(jiān)視器 。 右鍵單擊查看詳細(xì)信息 。
The following section gives us the history of the snapshot agent. The next part shows nine articles have generated after removing one article from the publication.
下一節(jié)為我們提供了快照代理的歷史記錄。 下一部分顯示從出版物中刪除一篇文章后生成的九篇文章。
Right-click publication and click Reinitialize
用鼠標(biāo)右鍵單擊發(fā)布，然后單擊重新初始化
Click Use the current snapshot to reflect the results on the subscriber.
單擊“ 使用當(dāng)前快照以將結(jié)果反映到訂閱服務(wù)器上”。
Drop the table on the publisher SQL1 as the following
將表放在發(fā)布服務(wù)器SQL1上，如下所示
On the subscriber SQL2, we can verify the table removed from the subscriber.
在訂戶(hù)SQL2上，我們可以驗(yàn)證從訂戶(hù)中刪除的表。
![]()
更改快照文件夾路徑 (Change Snapshot folder Path)
While configuring SQL Server Distribution database, we had given the following snapshot folder path. In this section, we will learn to change the snapshot folder path in the from the current distribution.
在配置SQL Server Distribution數(shù)據(jù)庫(kù)時(shí)，我們給出了以下快照文件夾路徑。 在本節(jié)中，我們將學(xué)習(xí)從當(dāng)前發(fā)行版更改的快照文件夾路徑。
Right-click publication and Go to properties.
右鍵單擊發(fā)布，然后轉(zhuǎn)到屬性 。
Go to Snapshot page on the left side and check current snapshot files path.
轉(zhuǎn)到左側(cè)的“ 快照”頁(yè)面 ，并檢查當(dāng)前快照文件的路徑。
To change the path, Right-click Replication folder and click Distribution Properties.
要更改路徑，請(qǐng)右鍵單擊“復(fù)制”文件夾 ，然后單擊“ 分發(fā)屬性” 。
In the Publisher page section, click on the icon in the following fig.
在“發(fā)布者”頁(yè)面部分中，單擊下圖中的圖標(biāo)。
Provide the new correct path and click OK
提供新的正確路徑，然后單擊“ 確定”。
![]()
資料篩選器 (Data Filter)
SQL Server replication provides an excellent feature to filter the data using the Column data filter. In the data filter, we will able to show the column which we want to display for the reposting purpose. We can remove the column which contains sensitive information like customer name, customer email, credit card, etc.
SQL Server復(fù)制提供了一項(xiàng)出色的功能，可以使用列數(shù)據(jù)篩選器篩選數(shù)據(jù)。 在數(shù)據(jù)過(guò)濾器中，我們將能夠顯示要重新發(fā)布的列。 我們可以刪除包含敏感信息的列，例如客戶(hù)名稱(chēng)，客戶(hù)電子郵件，信用卡等。
In the project environment, we build a dashboard in the tableau framework. Client vendors used to access those tableau dashboards for data analysis purposes. In that, we want to show data information of the one particular vendor to that specific vendor only. We do not want to show one vendor information to another vendor. So to limit data visibility, we will set-up SQL Server replication with data filter option.
在項(xiàng)目環(huán)境中，我們?cè)趖ableau框架中構(gòu)建一個(gè)儀表板。 客戶(hù)端供應(yīng)商過(guò)去曾訪(fǎng)問(wèn)這些Tableau儀表板以進(jìn)行數(shù)據(jù)分析。 這樣，我們只想向該特定賣(mài)方顯示一個(gè)特定賣(mài)方的數(shù)據(jù)信息。 我們不想將一個(gè)供應(yīng)商信息顯示給另一供應(yīng)商。 因此，為了限制數(shù)據(jù)可見(jiàn)性，我們將使用數(shù)據(jù)過(guò)濾器選項(xiàng)設(shè)置SQL Server復(fù)制。
Once the distribution database configured, we will proceed to set the publication.
配置分發(fā)數(shù)據(jù)庫(kù)后，我們將繼續(xù)設(shè)置發(fā)布。
Right-click Replication folder and select New Publication. Select the database AdventureWorksLT2012 as the following and click Next
右鍵單擊復(fù)制文件夾，然后選擇新建發(fā)布 。 選擇數(shù)據(jù)庫(kù)AdventureWorksLT2012 ，如下所示，然后單擊“下一步”。
Select the required database objects and click Next
選擇所需的數(shù)據(jù)庫(kù)對(duì)象，然后單擊“ 下一步”。
In the Filter Table Rows window, if we do not want to filter data, we can skip this step. Now we need to filter data so that we will configure this section window. Click on Add as shown in the following.
在“ 過(guò)濾表行”窗口中，如果不想過(guò)濾數(shù)據(jù)，則可以跳過(guò)此步驟。 現(xiàn)在我們需要過(guò)濾數(shù)據(jù)，以便我們配置此部分窗口。 單擊添加 ，如下所示。
Select the table we need to use for the data filter. We selected the table Customer from the SalesLT schema. Next, we need to add the column which contains sensitive information on the right-side filter statement. We will apply the condition for the specified column, and it will show the results only for the conditional statement.
選擇我們需要用于數(shù)據(jù)過(guò)濾器的表。 我們從SalesLT模式中選擇了表Customer 。 接下來(lái)，我們需要在右側(cè)的filter語(yǔ)句上添加包含敏感信息的列。 我們將條件應(yīng)用于指定的列，并且它將僅顯示條件語(yǔ)句的結(jié)果。
In this case, we added the column CompanyName for the condition; company name starts with A
在這種情況下，我們?yōu)闂l件添加了CompanyName列； 公司名稱(chēng)以A開(kāi)頭
Once we added the table Customer in the filter section as shown in the following fig.
一旦我們?cè)谶^(guò)濾器部分中添加了表Customer ，如下圖所示。
In the Snapshot section, select a checkbox for Create a snapshot and click Next.
在“ 快照”部分中 ，選中“ 創(chuàng)建快照”復(fù)選框，然后單擊“ 下一步” 。
In the Agent Security, configure the service account for the snapshot agent and the log reader agent as the following and click Next.
在“ 代理安全性”中 ，如下配置快照代理和日志讀取器代理的服務(wù)帳戶(hù)，然后單擊“下一步”。
In the complete wizard window, provide publication name as the following and click Finish.
在完整的向?qū)Т翱谥?/span> ，提供以下發(fā)布名稱(chēng)，然后單擊“ 完成” 。
Publication is configured successfully as the following.
發(fā)布成功配置如下。
![]()
訂戶(hù)上的數(shù)據(jù)驗(yàn)證 (Data Verification on the Subscriber)
In this step, we will verify the data; On the Publisher server SQL1, we will retrieve records from the table Customer. We can see, the column CompanyName shows the values for all the company name.
在這一步中，我們將驗(yàn)證數(shù)據(jù)； 在發(fā)布服務(wù)器SQL1上，我們將從表Customer中檢索記錄。 我們可以看到， CompanyName列顯示了所有公司名稱(chēng)的值。
In this step, we will verify the data on the Subscriber server SQL2. When we retrieved records for the same table on the subscriber server SQL2, we will get filter data for the condition which we have specified in the filter section. We got the company name which starts with only character A as shown in the following fig.
在此步驟中，我們將驗(yàn)證訂閱服務(wù)器SQL2上的數(shù)據(jù)。 當(dāng)我們?cè)谟嗛喎?wù)器SQL2上檢索同一表的記錄時(shí)，我們將獲得在filter部分中指定的條件的過(guò)濾器數(shù)據(jù)。 我們得到的公司名稱(chēng)僅以字符A開(kāi)頭，如下圖所示。
?
轉(zhuǎn)載：https://blog.csdn.net/culuo4781/article/details/107614018?ops_request_misc=%257B%2522request%255Fid%2522%253A%2522b1dc5807be9187305213cae8153eb1a3%2522%252C%2522scm%2522%253A%252220140713.130102334.pc%255Fall.%2522%257D&request_id=b1dc5807be9187305213cae8153eb1a3&biz_id=0&utm_medium=distribute.pc_search_result.none-task-blog-2~all~first_rank_ecpm_v1~rank_v31_ecpm-2-107614018-null-null.142
該文章在 2025/9/3 17:17:37 編輯過(guò)
400 186 1886
