Part II: Administering a SQL Server
Chapter 6: Distributing Your Data with Replication
Distributing Your Data
with Replication
In This Chapter
䊳Taking advantage of multiple machines
䊳Spreading the load with replication
䊳Knowing when to replicate
䊳Setting up replication
W
ith hardware costs plummeting, network bandwidth charges dropping like a stone, and free software (such as SQL Server 2005 Express) all the rage, chances are that you might have two or more networked computers at your disposal. Even though it’s a free product, SQL Server 2005 Express has many features that can help you leverage this extra horsepower to build database applications that are faster, cheaper, and more reliable.In this chapter, you discover how to take advantage of the SQL Server 2005 Express replication features, which enable you to spread your information and processing load across multiple servers.
Replication is one of those topics that can overwhelm you with its nuances and complexities. Luckily, it doesn’t have to. Simply stated, SQL Server’s replication is an easy-to-implement, automated set of processes for distribut- ing data among multiple machines. These computers can be in the same physical location, or they may be spread across the world and connected via the Internet. Regardless of your unique computer distribution, replication helps keep your SQL Server 2005 Express database in sync with other data- base servers.
Determining When and Why to Replicate
Replication makes a lot of sense in two common types of circumstances.
⻬Reporting/data warehouse:In this scenario, your goal is to distribute information from your primary database servers onto secondary com- puters. These additional processors are then available to serve up reports, business intelligence, or any other query-intensive service. Some of these reports may be interactive, while others may be gener- ated as part of a daily, weekly, or monthly scheduled job.
By using replication, you can automate the potentially cumbersome task of keeping these database servers in sync; you also greatly reduce the chance for errors and other synchronization problems to sneak their way into your environment.
⻬Information archiving/backup:Backing up or otherwise archiving your valuable SQL Server 2005 Express-based information is one way to improve the health and happiness of any database administrator. While replication is not intended to replace standard database backups per se, it’s often used as an adjunct to this vital system administration task. Given these common scenarios, replication offers many benefits for those database administrators who take the time to implement a solid data distrib- ution strategy. Here are some of its most compelling advantages:
⻬Shared workload: Spreading information among multiple computers means that you can let users on those machines work with local copies of their data, rather than forcing a central database server to shoulder the entire workload.
In many cases, these other computers have ample capacity to process just about any kind of database-related task. This helps lighten the load on your primary database server.
⻬Safeguarded data: When combined with a stringent backup strategy, data replication offers additional protection for your important data. Simply put, distributing your information among many different database servers greatly reduces the likelihood of a complete loss of data, even if the core database server is damaged or otherwise made unusable.
⻬Reduced costs:Replication is one way to put more client computers to work, which helps reduce the amount of processor-intensive labor on the part of your central database server. This can often spell the differ- ence between using this principal server as-is, or being forced into an expensive hardware upgrade.
84
Part II: Administering a SQL Server 2005 Express System
Discovering the Types of Replication
Each of the three major flavors of replication is useful for different situations and architectures:
⻬Transactional: For this type of replication, your SQL Server 2005
Express server receives data modifications nearly instantaneously. If you need to know every alteration as soon as it happens, this architecture is probably right for you. On the other hand, if your data doesn’t change that often, or if you have a slow network connection, you’ll likely want to employ one of the following two replication blueprints.
⻬Snapshot: Just as you might surmise from its name, this type of replica- tion copies a full image of your database to the remote servers. It’s a good choice when you don’t need up-to-the minute updates, or when your data doesn’t change that often. The downside is that subscribers may miss out on a data alteration until the next snapshot.
⻬Merge: This replication architecture makes the most sense when you expect multiple database servers to be modifying the same underlying information at the same time. For example, you might distribute por- tions of your master customer database to multiple subscribers. Each subscriber is free to make alterations to this database; alterations can’t happen with the two other scenarios. Merge replication, as its name sug- gests, is SQL Server’s way of blending this data to preserve accuracy and information cohesion.
Understanding Replication Limitations
By now, you’re probably wondering if all this great functionality comes with- out any strings attached. Alas, replication has some restrictions, at least when dealing with SQL Server 2005 Express. The good news is that replica- tion works just fine for the two major scenarios (reporting/data warehouse and information archiving/backup) that I describe earlier in the section “Determining When and Why to Replicate”; the bad news is that all the following are factors to consider with this entry-level database product:
⻬No publications; subscription only: This limitation means that any data that you create or modify on your SQL Server 2005 Express server can’t in turn be published for other database servers to consume. However, this restriction doesn’t have a major impact on the two primary replica- tion cases I describe earlier because both examples describe situations where SQL Server 2005 Express is the final destination for replicated information.
85
Chapter 6: Distributing Your Data with Replication
⻬Server-based, push-only subscriptions:Because there is no agent pre- sent in SQL Server 2005 Express, you may not initiate a subscription request from a computer running this edition. Instead, you must first configure subscriptions on the remote server; only then can you request access to this information from the computer running SQL Server 2005 Express.
⻬Ease-of-use: Until the release of SQL Server Management Studio Express, setting up subscriptions on your SQL Server 2005 Express database server meant rolling up your sleeves and diving into either Transact-SQL or programming to the Replication Management Objects (RMO) guide- lines. Fortunately, this graphical tool makes replication an easily obtain- able reality in your environment.
⻬SQL Server-only subscriptions: Although SQL Server is a solid, indus- trial-strength database server, other types of information repositories are out there. Unfortunately, although the more expensive editions of SQL Server support directly subscribing to alternate sources of informa- tion such as Oracle, you don’t have this option with SQL Server 2005 Express. However, you could use one of these higher-level SQL Server editions as a proxy for a different brand of database server; it could manage a subscription, and then publish the results to which you can subscribe.
Now that the fine print is out of the way, it’s time to look at all the replication components and architecture.
Replication Concepts
At its heart, SQL Server replication consists of interaction among several components, and each has an important role to play. To make things clearer, I segregate them by whether they play a role in publishing or subscribing. I also list them from the smallest objects to the largest.
Components for publishing
⻬Article: This unit of information is the smallest that you can replicate. It typically consists of data from a table, stored procedure, view, and so on.
⻬Publication: This grouping consists of at least one, and possibly many more articles. An identifiable relationship is usually among all the arti- cles in a given publication.
86
Part II: Administering a SQL Server 2005 Express System
⻬Publisher: This database server offers up publications (which in turn consist of articles) for other database servers to subscribe.
⻬Distributor: Much like the middleman who facilitates transactions in the real world, the distributor is an instance of SQL Server that is responsi- ble for managing all the replication details of one or more publishers. In fact, the distributor and publisher commonly work together on one machine.
Components for subscribing
⻬Subscription: Just as you can subscribe to your favorite magazine, you can set up a subscription to a publication. Unlike magazine subscrip- tions, however, you can configure when it should arrive, and how it should be sent to the subscriber.
⻬Subscriber: This database instance serves as the destination for sub- scriptions. It’s important to note that publishers and subscribers can reside on the same machine, although they’re typically found on sepa- rate computers.
Setting Up Replication
If you’ve read through the chapter to this point, you’re probably itching to get started with replication. If so, this section is for you. Here’s what you need to do to make replication a reality in your environment. To begin, I use the transactional style of replication; you can easily choose one of the other flavors if it’s more appropriate for your environment.
When setting up publications and subscriptions, you can elect to use a com- bination of Transact-SQL and stored procedures, or you can put your effort into SQL Server Management Studio. The graphical tools are so much easier to use that for this example, I place all the focus on them.
Your first task is to set up the publication and articles on the publisher. SQL Server 2005 Express lacks the capabilities to publish information: It can only serve as a subscriber. This means that you need to run the next few steps on a version of SQL Server 2005 (such as Workgroup, Standard, or Enterprise) that supports information publication.
87
Chapter 6: Distributing Your Data with Replication
Here’s how to publish data:
1. Launch SQL Server Management Studio.
2. Connect to the database server that will publish the data. 3. Expand the Replication folder.
4. Right-click the Local Publications folder, and choose the New Publication option.
The New Publication Wizard launches, which walks you through all nec- essary steps to get your data published.
5. Fill in all the relevant details in each dialog box.
Pay particular attention to the Articles dialog box (shown in Figure 6-1), which is where you select those objects for publication.
After you identify the objects to publish, you can also specify any filter- ing criteria that you want applied to help reduce the number of items sent for replication.
6. Decide if you want to create an immediate snapshot.
If you’re replicating to an empty database, choosing this option is a good idea.
7. After you finish, review the publication’s properties, and click Finish (as shown in Figure 6-2) to complete your work.
Figure 6-1:
Choosing objects to be published.
88
Part II: Administering a SQL Server 2005 Express System
That’s the publisher’s perspective on things. Here’s what to do on the sub- scriber side:
1. Launch SQL Server Management Studio or SQL Server Management Studio Express.
2. Connect to the database server that will receive the data. 3. Expand the Replication folder.
4. Right-click the Local Subscriptions folder, and choose the New Subscription option.
The New Subscription Wizard launches, which walks you through all necessary steps to subscribe to an existing publication.
5. Fill in all relevant details in each dialog box.
Pay particular attention to the database that you want to receive the subscription. You don’t even need to have created a table; the initial syn- chronization takes care of that if you want.
6. When you finish, review the subscription’s properties and click Finish to get things going.
Figure 6-3 shows a summary of a new subscription. Note that for this example, the publisher and subscriber are on the same machine. Obviously, they can also be on separate machines (and usually are).
Figure 6-2: Reviewing details about the publication.
89
Chapter 6: Distributing Your Data with Replication
That’s all you need to do. Depending on how you configured your publica- tions and subscriptions, information should begin flowing around your net- work. And the best part is that SQL Server is doing all the work. If you want to observe the data movement, you can launch the replication monitor by right-clicking the Replication folder in SQL Server Management Studio, and choosing the Launch Replication Monitor option.
Figure 6-3:
Summariz- ing a new subscription.
90
Part II: Administering a SQL Server 2005 Express System
Part III
Adding and
Accessing a SQL
Server 2005
Express Database
12_599275 pt03.qxp 6/1/06 8:42 PM Page 91In this part . . .
A
fter downloading, installing, and configuring your SQL Server 2005 Express database server, you’re ready to start putting it to the test: storing, retrieving, and managing information. For that reason, in this part I dis- cuss important design concepts that you should keep in mind as you plan your relational database.From there, I move on to showing you how to create impor- tant objects, such as databases and tables, and explaining the SQL Server 2005 Express database access language (Transact-SQL). Finally, I demonstrate a few nifty advanced techniques for pushing Transact-SQL to the limits.