MASTERING SQL SERVER 2000- P21

Chia sẻ: Thanh Cong | Ngày: | Loại File: PDF | Số trang:50

0
38
lượt xem
4
download

MASTERING SQL SERVER 2000- P21

Mô tả tài liệu
  Download Vui lòng tải xuống để xem tài liệu đầy đủ

Tham khảo tài liệu 'mastering sql server 2000- p21', công nghệ thông tin, cơ sở dữ liệu phục vụ nhu cầu học tập, nghiên cứu và làm việc hiệu quả

Chủ đề:
Lưu

Nội dung Text: MASTERING SQL SERVER 2000- P21

  1. 1020 CHAPTER 27 • REPLICATION company with different data-processing rules. In this instance, select No and click Next. 10. The next screen asks whether there are any subscribers that are not running SQL Server 2000. This is because other database systems may not understand proper- ties that are proprietary to SQL Server 2000. Also the snapshot can be stored in a format that only SQL Servers will understand (a binary format), which will make replication faster. If there are third-party servers involved, the snapshot must be stored in a format that they can read (character mode). Accept the defaults here and click Next. 11. On the next screen, you need to choose what you will publish as an article. Under Object Type, leave the default of Tables checked (this just limits the dis- play to tables only). Then on the right side of the dialog box, check the box next to Employees to enable it for publication. 12. Click the ellipsis button next to the Employees table to bring up the properties for the article (as tables are called in replication). 13. On the General tab of the Table Article Properties dialog box, change the Desti- nation Table Name to Repl_Employees and click OK.
  2. CREATING AND SUBSCRIBING TO A SNAPSHOT PUBLICATION 1021 14. Click OK to continue to the next screen, where you are warned about the iden- tity replication issue and click Next. 15. On the next screen, you are asked to select a publication name and description. In the Publication Name box, enter Northwind Employees and leave the description that is typed in for you. Click Next. PA R T VI Advanced Topics
  3. 1022 CHAPTER 27 • REPLICATION 16. You are now asked whether you would like to customize the publication further by allowing anonymous subscribers or adding partitioning. Select Yes and click Next. 17. On the next screen, you are asked whether you would like to vertically or hori- zontally partition the data in some of the articles. Leave both boxes unchecked and click Next. 18. You are now asked whether you would like to allow anonymous subscribers to access your publication. If you select No, all subscribers must be registered in your copy of Enterprise Manager (meaning that you can see them in Enterprise Manager). If you select Yes, any server can subscribe to your data. Choose Yes if you intend to use pull subscriptions. You will choose Yes here and click Next. 19. On the next screen, you can change the schedule at which a snapshot is cre- ated to refresh the subscribers. This is done to make sure the subscriber is always up-to-date. You are going to use the default schedule and click Next. 20. On the final screen, click Finish to create your publication. 21. You will now see a list of tasks that SQL Server must complete to create your publication, after which you are presented with a dialog box informing you of success. Click OK. Now you should look back at the Create and Manage Publications dialog box, where you should see a pink-book icon. This is the icon used for snapshot replication (other types are different colors). If you click the Properties button, you will see a dia- log box that allows you to change any of the properties of the publication. This time you will pull the subscription so that you can see the difference in the process between pushing and pulling. In this next series, you will pull the subscrip- tion to the SECOND server: 1. Please close any open dialog boxes and return to Enterprise Manager. Once there, click the SECOND server to select it. 2. On the Tools menu, select Replication and click Pull Subscription to Server\second. 3. On the Pull Subscription dialog box, select Northwind and click the Pull New Subscription button. 4. On the welcome screen, check the box next to Show Advanced Options and click Next to get started.
  4. CREATING AND SUBSCRIBING TO A SNAPSHOT PUBLICATION 1023 5. On the next screen, you can opt to find a publication by browsing through the available SQL Servers or searching Active Directory (assuming that you listed the publication in Active Directory). Choose the option to Look at Publications from Registered Servers and click Next. PA R T VI Advanced Topics
  5. 1024 CHAPTER 27 • REPLICATION 6. On the Choose Publication dialog box that comes up next, expand the pri- mary server (not SECOND), select the Northwind Employees subscription, and click Next. 7. You are now asked for the destination database; this is where the replicated data will reside. Because you changed the name of the destination table, you can use Northwind as the target database and click Next.
  6. CREATING AND SUBSCRIBING TO A SNAPSHOT PUBLICATION 1025 8. The next screen asks whether this should be an anonymous subscription. Anonymous subscriptions are not registered at the publisher and are very useful for Internet subscriptions where FTP is used, because passwords are sent in clear text and therefore are unsecured. Leave the default of No, This Is a Named Sub- scription and click Next. 9. You are now informed that the schema will be updated. There is no choice in the matter here, because on a pull subscription, SQL Server can detect whether the schema exists. Click Next to continue. PA R T VI Advanced Topics
  7. 1026 CHAPTER 27 • REPLICATION 10. On this screen, you are asked from where to get the snapshot files. This can be the default (which is the distribution working folder) or a CD-ROM, FTP server, or Zip drive or some other removable media. In this instance, select the default location and click Next. 11. On the next screen, you are asked for an update schedule. Continuously means that the server will check for updates and pull them over whenever there is a change in the data (this is a bad choice for snapshot replication, because it copies the entire publication every time). The Schedule option allows you to pick a specific time for updating, and the On Demand Only option will instruct SQL Server not to replicate changes automatically—you will need to start repli- cation yourself using the Replication Monitor. In this case, leave the default schedule and click Next.
  8. CREATING AND SUBSCRIBING TO A SNAPSHOT PUBLICATION 1027 12. The next screen ensures that the SQLServerAgent service is running on the pub- lisher. If the service is not running, replication will fail. Click Next to continue. 13. On the last screen, click Finish to pull the subscription. 14. You will see a list of tasks that SQL Server must perform to pull the subscription, after which a dialog box will inform you of success. Click OK. With a snapshot publication in place and a pull subscription running, you can test the replication. In the next series of steps, you will open two copies of Query Ana- lyzer, one connected to each instance of SQL Server, and test the replication of data: 1. Open the first copy of Query Analyzer by selecting it from the SQL Server 2000 program group under Programs on the Start menu and log in to the primary server (called first from here on out). PA R T 2. Open another copy of Query Analyzer and log in to the SECOND server (called sec- VI ond from here on out) by typing server_name\SECOND in the Server Name box. 3. In the second copy of Query Analyzer, enter and execute the following code to verify that replication worked: USE Northwind Advanced Topics SELECT * FROM Repl_Employees 4. Now you will add a record to the original table; switch to the first copy of Query Analyzer, and enter and execute the following code: USE Northwind
  9. 1028 CHAPTER 27 • REPLICATION INSERT Employees (LastName, FirstName, Title) VALUES (‘Frost’, ‘Jasmine’, ‘Developer’) 5. Wait for about 5 minutes (to give the server time to replicate), switch to the sec- ond copy of Query Analyzer, and enter and execute the following code to see whether the change replicated: USE Northwind SELECT * FROM Repl_Employees WHERE LastName = ‘Frost’ 6. Close both copies of Query Analyzer once you are able to see the record in the second copy. With that, you have successfully created and pulled a snapshot subscription. We can now move on to merge replication. Creating and Subscribing to a Merge Publication Merge replication is used when the publisher and all subscribers need to be able to make changes to their local copy of the data and have those changes replicated to all other subscribers in the replication topology. To demonstrate how this works, you will configure a merge publication on the Northwind database; then you will have the
  10. CREATING AND SUBSCRIBING TO A MERGE PUBLICATION 1029 SECOND server subscribe to the publication; finally you will modify the same record in both databases and see how to deal with the subsequent conflict: 1. If you are not in Enterprise Manager, open it by selecting it from the SQL Server 2000 group under Programs on the Start menu. 2. Click your default server (not SECOND) in the contents pane and from the Tools pull-down menu, select Replication and then click Create and Manage Publications. 3. From the Create and Manage Publications dialog box, select Northwind and click Create Publication to start the Create Publication Wizard. 4. On the first screen of the Wizard, check the box next to Show Advanced Options and click Next. 5. On the second screen, you will select Northwind as the database to publish from and click Next. 6. On the next screen, you are asked whether you would like to use the existing publication as a template. This is handy if you are creating another, similar transactional subscription. Because you are not, click No, I Will Define and then click Next. 7. On the next screen, you are asked what type of replication this is to be; choose Merge and click Next. PA R T VI Advanced Topics
  11. 1030 CHAPTER 27 • REPLICATION 8. The next screen asks whether all subscribers will be running SQL Server 2000. If so, the snapshot can be stored in a format that only SQL Servers will under- stand (a binary format), which will make replication faster. If there are third- party servers involved, the snapshot must be stored in a format that they can read (character mode). Not only that, but other database systems may not understand all of the proprietary properties in SQL Server 2000. Leave the default of servers running SQL Server 2000 and click Next. 9. On the next screen, you need to choose what you will publish as an article. Under Object Type, leave the default of Tables checked (this just limits the dis- play to tables only). Then on the right side of the dialog box, check the box next to Customers to enable it for publication. 10. Click the ellipsis button next to the Customers table to bring up the properties for the article (as tables are called in replication). 11. At the bottom of the General tab, you will see two choices; select the bottom choice (changes to different columns in the same row will be merged). Here is what they do: Treat Changes to the Same Row as a Conflict: If users at different subscribers make changes to the same row of a table, even if they change data in different columns, it will be regarded as a conflict.
  12. CREATING AND SUBSCRIBING TO A MERGE PUBLICATION 1031 Treat Changes to the Same Column as a Conflict: If users at differ- ent subscribers make changes to the same row of a table, but different columns, no conflict will occur. If they make changes to the same column of the same row, a conflict will arise. 12. Click the Snapshot tab. 13. You are presented with several choices if the Customers table exists already in the subscription database. You can keep it unchanged; drop it and re-create it; keep it but delete all of the data that matches your row filter statement; or keep it and delete all of the data. You cannot simply use a different table name on PA R T the subscriber, because merge replication needs the same table name on all sub- VI scribers. In this case, you will use the default of dropping and re-creating the existing table. 14. Also on the Snapshot tab, there are several choices of objects to be transferred. Leave all of the defaults (transferring all but extended properties and collation) Advanced Topics and select the Resolver tab.
  13. 1032 CHAPTER 27 • REPLICATION 15. The Resolver tab allows you to change the program used to resolve conflicts that occur when multiple users make changes to the same data. You will use the default resolver here and click the Merging Changes tab.
  14. CREATING AND SUBSCRIBING TO A MERGE PUBLICATION 1033 16. The Merging Changes tab is used to verify that the login used by the merge agent has permission to perform INSERT, UPDATE, and DELETE actions. Click OK, then click Next. 17. The next screen to pop up warns you that SQL Server must add a column with a datatype of Uniqueidentifier to the Customers table. Merge replication uses this special datatype to ensure that each record is unique. Click Next to create the new column. PA R T VI Advanced Topics
  15. 1034 CHAPTER 27 • REPLICATION 18. Click Next to continue to the next screen, where you are asked to select a publi- cation name and description. In the Publication Name box, enter Northwind Customers and leave the description that is typed in for you. Click Next. 19. You are now asked whether you would like to customize the publication further by allowing anonymous subscribers or adding partitioning. Select Yes and click Next. 20. On the next screen, you are asked whether you would like to vertically or hori- zontally partition the data in some of the articles. Leave both of these options unchecked and click Next. 21. You are now asked whether you would like to allow anonymous subscribers to access your publication. If you select No, all subscribers must be registered in your copy of Enterprise Manager (meaning that you can see them in Enterprise Manager). If you select Yes, any server can subscribe to your data. Choose Yes if you intend to use pull subscriptions. You will choose Yes here and click Next. 22. On the next screen, you can change the schedule at which a snapshot is cre- ated to refresh the subscribers. This is done to make sure the subscriber is always up-to-date. Use the default schedule and click Next. 23. On the final screen, click Finish to create your publication. 24. You will now see a list of tasks that SQL Server must complete to create your publication, after which you are presented with a dialog box informing you of an error. This error is expected in this exercise, because you are not replicating any of the tables to which Customers is related via a foreign key. Click Close to dismiss the error. Now you should be back at the Create and Manage Publications dialog box, where you should see a yellow-book icon. This is the icon used for merge replication (other types are different colors). If you click the Properties button, you will see a dialog box that allows you to change any of the properties of the publication. Let’s now push the publication to the SECOND server so that you can test merge replication: 1. If you have opened the Properties dialog box, please close it and make sure you are in the Create and Manage Publications dialog box. 2. Select the Northwind Customers icon under Northwind and click the Push New Subscription button. 3. On the welcome screen of the Push Subscription Wizard, check the box next to Show Advanced Options and click Next. 4. On the second screen, you are asked to select a subscriber. Select SECOND and click Next.
  16. CREATING AND SUBSCRIBING TO A MERGE PUBLICATION 1035 5. You are now asked for the target database; this is where the replicated data will reside. Because the Customers table cannot be dropped in the Northwind data- base on the subscriber, you need to replicate this to the pubs database. There- fore, enter pubs in the textbox and click Next. 6. On the next screen, you are asked to decide where the agent will run. Choose Subscriber if there are a lot of subscribers; choose Distributor if there are only a few subscribers (it is easier to manage that way). Here you will choose to run the agent on the distributor and click Next. 7. Now you can decide when to replicate changes to the subscribers. Continu- ously will replicate changes whenever one occurs. Select Continuously and click Next. 8. You are asked whether you would like to initialize the schema at the sub- scribers. You should initialize the schema if you are replicating to a new data- base or have not yet created the tables on the subscriber. If you have already created the schema to hold the data on the subscriber, select No. In this case, select Yes and click Next. 9. On the next screen, you are asked to set the subscription priority. This is used to resolve any conflicts that may arise when multiple users modify the same data. The server with the higher priority is given precedence. The first choice allows you to use the publisher’s priority setting to resolve conflicts, essentially allowing the publisher to win every time. The second choice allows you to set a number between 0.00 and 99.99 as the priority. In this case, select the second choice and leave the setting as 75.00. Then click Next. PA R T VI Advanced Topics
  17. 1036 CHAPTER 27 • REPLICATION 10. The next screen ensures that the SQLServerAgent service is running on the pub- lisher. If the service is not running, replication will fail. Click Next to continue. 11. On the last screen, click Finish to push the subscription. 12. You will see a list of tasks that SQL Server must perform to push the subscrip- tion, after which a dialog box will inform you of success. Click OK. With a merge publication in place and a push subscription running, you can test the replication. In the next series of steps, you will open two copies of Query Ana- lyzer, one connected to each instance of SQL Server, and test the replication of data: 1. Open the first copy of Query Analyzer by selecting it from the SQL Server 2000 program group under Programs on the Start menu and log in to the primary server (called first from here on). 2. Open another copy of Query Analyzer and log in to the SECOND server (called second from here on) by typing server_name\SECOND in the Server Name box. 3. In the second copy of Query Analyzer, enter and execute the following code to verify that replication worked: USE Pubs SELECT * FROM Customers 4. Now to test merge replication, you will make a change to the same record on both servers at the same time and see which change applies. Enter the follow- ing code in the first copy of Query Analyzer, but do not execute it yet: USE Northwind UPDATE Customers SET ContactName = ‘Maria Andrews’ WHERE CustomerID = ‘ALFKI’ 5. Now in the SECOND copy of Query Analyzer, enter the following code, but do not execute it yet. This will change the exact same data on the subscriber as was changed on the publisher: USE Pubs UPDATE Customers SET ContactName = ‘Mary Anders’ WHERE CustomerID = ‘ALFKI’ 6. Execute the query in the SECOND copy of Query Analyzer. 7. Switch to the first copy of Query Analyzer and execute the query.
  18. CREATING AND SUBSCRIBING TO A MERGE PUBLICATION 1037 8. Wait for about 5 minutes (to give the server time to replicate), switch to the second copy of Query Analyzer, and enter and execute the following code to see whether the change replicated: USE Pubs SELECT * FROM Customers WHERE CustomerID = ‘ALFKI’ 9. When you see the value of Maria Andrews in the second copy of Query Analyzer, close both copies of Query Analyzer—replication was successful. Now you have a small problem; notice that the subscriber now contains the value PA R T that was entered at the publisher (in the first copy of Query Analyzer) rather than the value that was entered at the subscriber. This is referred to as a conflict and can be rec- VI tified in Enterprise Manager. 1. In Enterprise Manager, expand your default server and select Northwind under databases. Advanced Topics 2. Right-click Northwind, move to All Tasks, and click View Replication Conflicts. 3. The Microsoft Replication Conflict Viewer will come up and display a conflict.
  19. 1038 CHAPTER 27 • REPLICATION 4. Click the View button. 5. In the Replication Conflict Viewer, you can see the rows that have conflicts. The winner is the server with the highest priority in this instance. You have a few choices here to resolve the conflict: • Keep the Winning Change will make the change from the winning server permanent. • Resolve with This Data will resolve the conflict with the data displayed in the right column. • Postpone Resolution simply postpones the resolution of a conflict until a later time. • The Log Details checkbox will log the conflict for future reference. 6. Make sure that Conflict Loser is displayed in the right column and click the Resolve with This Data button.
  20. CREATING AND SUBSCRIBING TO A MERGE PUBLICATION 1039 7. Once complete, close the Replication Conflict Viewer and switch to the second copy of Query Analyzer. 8. Enter and execute the following query to make sure the second update (Mary Anders) is still there: USE Pubs SELECT * FROM Customers WHERE CustomerID = ‘ALFKI’ 9. Now switch to the first copy of Query Analyzer and run the following query to PA R T make sure that the database was updated with the data from the second update: VI USE Northwind SELECT * FROM Customers WHERE CustomerID = ‘ALFKI’ Advanced Topics
Đồng bộ tài khoản