Configure Replication In Sql Server 2005

Configure Replication in SQL Server 2005 The easiest way to create a publication is to use the Publication Wizard in SQL Server. The wizard provides a graphical user interface that lets you select the Publisher, the publication type, and the articles to publish. It also lets you configure filtering for the publication. When you create a publication for SQL Server Compact Edition Subscribers by using the Publication Wizard, you must select the following options: • On the Publication Type page, select Merge publication. • In SQL Server 2005, on the Subscriber Type page, select SQL Server Compact Edition. By enabling Windows CE subscribers, character mode snapshots are automatically configured for the publication. In SQL Server 2005, Web synchronization is also enabled. If this is the first publication created for this server, the Publication Wizard prompts you to configure the default snapshot folder location. The snapshot folder holds the snapshot files that contain the schema and data for published tables. The IIS user must be able to read these snapshot files so that it can download the files to the smart device. Note: The snapshot folder default location is a local path in the installation folder for SQL Server. You must change this path to a shared folder so that the IIS user can access the snapshot from over the network. When you use the Publication Wizard to create a publication, you select the articles you want to publish by selecting the check box next to each article. Because you have previously indicated in the Publication Wizard that this publication will have Microsoft SQL Server 2005 Compact Edition (SQL Server Compact Edition) Subscribers, the Publication Wizard will configure the articles appropriately. When you select an article, you can add filtering. Both column and row filtering are configured at the same time in the wizard. This lets you define and maintain subsets of data that can be published to different devices. You can use row filtering to publish a subset of rows in a published table. For example, in a service organization, each service representative might have to receive only the customer records for the accounts they service. You can use column filtering to publish a subset of columns in a published table. For example, you can use a column filter to eliminate large text or image columns that you might not want to publish to a smart device. Row-level and column-level tracking determines how data is transferred and how conflicts are detected during synchronization. Column-level tracking reduces the amount of information that must be sent to the Publisher during synchronization, but row-level tracking requires less tracking overhead because more storage is required to track the changes. Column-level tracking can also reduce the number of conflicts when different users are changing the same data. For example, suppose one change is made to the address column of a Publisher row, and a

second change is made to the phone number column in the same table of the corresponding Subscriber row. With row-level tracking, a conflict is detected, because changes were made to both rows. With column-level tracking, no conflict is detected, because changes were made to different columns in the rows. When you use the Publication Wizard to create a publication, you enable tracking when you add the articles. Both row-level and column-level tracking are configured at the same time. Column-level tracking is the default tracking for all articles unless otherwise specified. Microsoft SQL Server 2005 Compact Edition subscribers can subscribe to both SQL Server 2000 and SQL Server 2005 publications. A SQL Server Compact Edition Subscriber is always a client subscriber that connects to the Publisher by using the HTTP protocol. From within SQL Server Management Studio, connect to an instance of a SQL Server Compact Edition database. This database can be running on a connected device, or it can be a file on the desktop computer. In Object Explorer, expand the SQL Server Compact Edition database node, expand Replication, right-click Subscriptions, and then choose New Subscriptions. On the Introduction page, click Next. On the Choose Publication page, in the Publisher drop-down list, choose . Choose the Publisher and then click Connect. In the Publications list, select the publication, and then click Next. On the Identify Subscription page, type a name for the subscription. If the publication to which you are subscribing uses dynamic filtering, provide a HOST_NAME value for this database, and then click Next. On the Web Server Authentication page, type the URL to the virtual directory that contains the SQL Server Compact Edition Server tools, provide the authentication information, and then click Next. For more information about configuring a virtual directory, see How to: Configure Connectivity with the Configure Web Synchronization Wizard. On the SQL Server Authentication page, select the type of SQL Server Authentication required to connect to SQL Server, provide the user name and password, if it is required, and then click Next. On the Complete the Wizard page, you can select and copy the sample code to use in a C# or Visual Basic application. Click Finish to create the subscription. When the process is completed, click Close.