Postgres Plus Advanced Server 9.0 Sneak Peek – SQL Server Replication

This post is the third in a series that takes a look at some of the exciting new features of Postgres Plus Advanced Server 9.0, which is currently in beta. Before I came to EnterpriseDB, I was the director of product management for MySQL. During my five or so years at MySQL, I saw a lot of interest in companies wanting to either move to an open source database alternative, or use a hybrid approach to data management where a proprietary database would be used for part of the functionality in an application and an open source database would be utilized for a different set of functionality in the same system. Interestingly, when I polled our customer base year after year and asked what database they were moving from, the number one reply wasn’t Oracle (it was number two). It was Microsoft. For a variety of reasons, people were looking to move away from SQL Server to another database. For those who are looking to either move from SQL Server or use SQL Server in conjunction with a lower-cost database alternative, we’ve just made that easier in our upcoming version of Postgres Plus Advanced Server 9.0. One of the solutions we bundle with Advanced Server is xDB Replication Server, which is a point-and-click replication solution that provides very flexible and powerful options for replicating from PostgreSQL to PostgreSQL and from Oracle to PostgreSQL. New in 9.0 is the ability to replicate straight from Microsoft SQL Server to PostgreSQL. The xDB Replication Server is an asynchronous, master-to-replica replication system enabling the easy creation of a variety of replication topologies. From a global perspective, xDB is architected to manage replication in a publish-subscribe manner. The data to be made available for copying is made public as a publication. A publication is defined as a named set of tables and views within a source database. The database that contains the publication is called the publication database of that publication. To get a copy of that data, a DBA or developer must subscribe to the publication. A subscription is a named association with a publication. Along with its association with a publication, a subscription is associated with a destination database to which the publication is to be copied. This database is called the subscription database. The subscription tables are the tables in the subscription database where each subscription table is created from a corresponding table or view in the publication. New in xDB is the ability to have Microsoft SQL Server act as a publication datasource. A DBA or developer adds a publication database by supplying xDB with the hostname, port number, security credentials, and database on the source Microsoft SQL Server database, and it is added as an xDB publication source. Once the SQL Server database is added as a publication, the DBA or developer then selects the option to create a new publication. The xDB Replication Server queries the SQL Server database data dictionary and lists the available tables in the database. One or more tables are then chosen by the DBA and a new publication is created. The next step is for the DBA or developer to create a PostgreSQL subscription target for the new SQL Server publication. This is accomplished by supplying xDB with the host, database, and login information for the target PostgreSQL server. Once this has been done, a snapshot task is performed to initialize the PostgreSQL server from the SQL Server source, which involves making a copy of the entire SQL Server table(s) and porting it to PostgreSQL. From that point, a schedule can be set up to perform either an entire refresh of the table or to only synchronize the PostgreSQL table with changes that occur to the published table on the SQL Server system.  If you’re interesting in replicating data from SQL Server to PostgreSQL, you can download the beta of Postgres Plus Advanced Server 9.0 now and try the latest version of xDB out for yourself. And please let us know what you think of the new functionality and other enhancements in 9.0. Thanks, as always, for your support of PostgreSQL and EnterpriseDB!