Apress - Pro SQL Server 2008 Replication (2009)02

Apress - Pro SQL Server 2008 Replication (2009)02
Nội dung trích xuất từ tài liệu:
Apress - Pro SQL Server 2008 Replication (2009)02 CHAPTER 1 ■ INTRODUCTION 15SQL Server Configuration ManagerThe SQL Server Configuration Manager is a management tool that acts as a one-stop interfacethat allows administrators to configure and manage the services of SQL Server, SQL ServerAgent, SQL Server Analysis Services, and MS DTC. It can be integrated with other MicrosoftManagement Console (MMC) applications. The SQL Server Configuration Manager is installedwith SQL Server 2008.■Note The SAC tool does not exist in SQL Server 2008 anymore.SQL Server Management StudioThe SQL Server Management Studio allows for the administration of services like ReportingServices, Integration Services, Notification Services, and Replication. The Object Explorer is acomponent of the SSMS, and it allows you to view lists of the objects for a particular instanceof SQL Server like a Database Engine, Analysis Service, Notification, and Integration Services.It lists the System and user databases, the linked servers, Replication, and SQL Server Agent. If you want to execute a query, the Object Explorer will also allow you to open the QueryEditor. The alternative way is to open a new Database Engine Query and connect to the server.■Tip If you want to check the veracity of the T-SQL syntax in the Query Editor, you can highlight the state-ment and press Shift+F1. It will take you directly to the online help. SQL Server 2008 has IntelliSense installedin SQL Server Management Studio. You need to have IntelliSense enabled in order to use its features.Database Engine Tuning AdvisorThe Database Engine Tuning Advisor helps you to optimize the performance of the databases byrecommending the optimal set of indexes and types of physical design structures. This advisor isalso integrated with the SSMS.Replication MonitorThe Replication Monitor lists the status of the publications and subscriptions. The interface forthe Replication Monitor has changed from what it was in SQL Server 2005. You can now launch theDatabase Mirroring Monitor from the Replication Monitor. It is also possible to check replicationagent profiles, such as those for the Distribution, Snapshot, and Merge Agents, in the Replica-tion Monitor. While a number of warnings are issued by default, it is possible to enable warningsfor other conditions. Alerts can be created and thresholds set to trigger those alerts. The Replication Monitor can also monitor the performance of transactional and mergereplication by allowing you to set warnings and thresholds, view detailed synchronizationstatistics of merge replication, and view transactions and delivery times for transactionalreplication.16 CHAPTER 1 ■ INTRODUCTION Summary This chapter introduced replication and the different tools available in SQL Server for configuring, administering, monitoring, and troubleshooting replication. • Databases that are logically interrelated and connected over the network are called distributed databases. • There are two methods of distributing data: distributed transactions and replication. • Distributed transactions are coordinated by the MS DTC. A transaction manager coordi- nates the distribution of the transaction with resource managers and the MS DTC log. • The two-phase commit protocol is employed by the MS DTC to successfully execute distributed transactions. • Replication is the process by which copies of distributed data can be sent to remote sites. • There are two kinds of replication: synchronous and asynchronous replication. • SQL Server supports asynchronous replication. • The benefits of using replication in a distributed data environment are scalability, performance, and autonomy of the sites. • SQL Server uses OLE DB to communicate with heterogeneous data sources like Oracle by using the linked server. • Replication has a higher autonomy and latency than distributed transactions. • The Replication Monitor allows the monitoring of publications and subscriptions. It can also be used to monitor the performance of snapshot, merge, and transactional replication. In Chapter 2, I will introduce the Publisher-Subscriber model. We will look at articles, publications, subscriptions, distribution, and agents, which will help you better understand the fundamentals of replication. I will also show you how to set up replication in SQL Server. Quick Tips • Distributed processing involves sharing resources among the members of the network. • The Microsoft OLE DB provider for SQL Server is installed automatically with SQL Server. • The MS DTC log file is a binary file. It is needed for the transaction manager to start. • In order to use IntelliSense in SSMS, you need to have IntelliSense enabled in SSMS.CHAPTER 2■■■Replication BasicsIn the previous chapter, I introduced replication as a method of distributing data. I describedwhat asynchronous replication is and outlined the replication types available in SQL Server.We are now ready to look at the details of replication. In this chapter, I will explain the Publisher-Subscriber model that is used to represent the several components involved in replication: theDistributor, Publisher, Subscriber, publications, articles, subscriptions, and agents. In addition,you will also learn how different agents are used in transferring the data. On completing this chapter, you will be able to do the following: • Describe the Publisher-Subscriber model. • Identify replication components. • Apply agent types to different kinds of replication. • Compare physical repl ...