SQL Server Replication to Oracle 12c

No version of SQL Server supports replicating to Oracle 12c out of the box.

This took us a little by surprise at work, since things had been replicating just fine in our equally-12c test environment. The difference in Production that arose this week, however, was that somebody decided to re-initialise the SQL Server publish-and-subscribe replication in that environment. This caused SQL Server to drop the replication-controlling table from Oracle and to re-create it from scratch -and only at that point does the lack of 12c support reveal itself.

The symptoms are easy to describe: if you check your replication monitor in the SQL Server Management Studio, you’ll see this error (or variations on it):

sqlserverrepl01

The cause of this is also quite easily found. In the SQL Server msdb database (under Programmability > Stored Procedures > System Stored Procedures, if you’re browsing in the Management Studio), you’ll find a procedure called sys.sp_MSrepl_createdatatypemappings. It contains this code snippet:

IF OBJECT_ID(N'dbo.MSdbms_datatype', 'U') IS NULL
 BEGIN
 print 'Creating table MSdbms_datatype'

create table MSdbms_datatype
 (
 datatype_id int NOT NULL IDENTITY,
 dbms_id int NOT NULL,
 type sysname NOT NULL,
 createparams int NOT NULL DEFAULT 0,
 CONSTRAINT pk_MSdbms_datatype PRIMARY KEY (datatype_id),
 CONSTRAINT fk_MSdbms_datatype_dbms_id FOREIGN KEY (dbms_id) REFERENCES MSdbms (dbms_id)
 )
 
 exec dbo.sp_MS_marksystemobject 'MSdbms_datatype'

--
 -- Define default dbms data types
 --
 exec sys.sp_MSrepl_MSSQLdatatypes 'MSSQLServer'
 exec sys.sp_MSrepl_DB2datatypes 'DB2'
 exec sys.sp_MSrepl_ORAdatatypes 'Oracle'
 exec sys.sp_MSrepl_ORAdatatypes 'Oracle', '8'
 exec sys.sp_MSrepl_ORAdatatypes 'Oracle', '9'
 exec sys.sp_MSrepl_ORAdatatypes 'Oracle', '10'
 exec sys.sp_MSrepl_ORAdatatypes 'Oracle', '11'
 exec sys.sp_MSrepl_SASdatatypes 'SYBASE'
 END

You’ll note that it’s creating a table to govern the ‘data type mappings’ between SQL Server and various other databases -in other words, and as an example, where SQL Server says ‘INT’ or ‘BIGINT’, Oracle will say ‘NUMBER’. SQL Server needs to be able to translate its data types to those used by ‘foreign’ databases, and this code sets that translation mechanism up.

Unfortuately, you’ll also note that whilst the code goes on to populate this data mapping table with translation entries for Oracle versions 8, 9, 10 and 11… there’s nothing at all for Oracle 12! It’s this lack of a description as to which SQL Server data types should become which Oracle 12c ones that explains replication’s inability to create its MSREPL7 table correctly.

If, despite receiving that earlier error when first attempting to create a new subscription to an Oracle database, you describe the MSREPL7 table that will actually have been created in Oracle, you’ll see this sort of thing:

sqlserverrepl02

Notice all those “INTERVAL DAY(2) TO SECOND(6)” columns? They’re actually supposed to be NUMBER(10) and NUMBER(3) columns. Those BINARY FILE LOB columns are all supposed to be RAW(16), too.

MSREPL7 is the ‘governor’ for SQL Server replication: it records what tables are supposed to be replicating and what transaction they’re up to in the destination (Oracle) database. When the SQL Server source database next wants to send transactions to Oracle, it’s this table which tells it which transactions have already been sent and which have not. So when this ‘governor’ table is screwed up, you won’t get very far with any replication!

As an aside, things are slightly more complicated if you upgrade to 12c from a previously-working 11g. In that case, you’d already have had replication working nicely, even after completing the 12c upgrade process, and thus the MSREPL7 table would have all the right data types and there’d be no impediment to replication continuing to work. The other tables you’re replicating will also already exist in your newly-upgraded 12c database and they’ll have all the right columns too. So they will replicate nicely as well. So, you will think that all is well… because it is!

It’s only when someone decides to reinitialize  replication that it will all go pear-shaped, because it’s only at that point that SQL Server will drop the tables being replicated and attempt to re-create them… with all the wrong data types! When you then attempt to push a number from SQL Server into a column in Oracle that’s been freshly-created as an INTERVAL data type, that’s when sparks will fly.

This is why we didn’t realise that upgrading to 12c would be an issue in our various test environments: no-one had attempted to re-initialize the replication; and with many multi-hundred-million rows to be pushed across into Oracle, it was a fair bet that no-one would be daft enough to do that in Production either! Unfortunately, it turned out that my gambling instincts were wrong on this occasion: someone was daft enough to hit the wrong menu options, and thus the trouble began.

I couldn’t find much about this problem on Google, which was rather surprising (if one major database has trouble replicating to another major database, I would have expected to hear a lot about it by now!)

I did find enough to make me look at the column types in MSREPL7, and notice that all sorts of odd data types were in use. That got me hunting down any references to how SQL Server knows what data type maps to what in Oracle. And finally, that got me to the stored procedure the code from which appears above.

Looking at that code, it’s just begging for an extra line to be added to it, no?! I mean, if it read:

exec sys.sp_MSrepl_ORAdatatypes 'Oracle'
 exec sys.sp_MSrepl_ORAdatatypes 'Oracle', '8'
 exec sys.sp_MSrepl_ORAdatatypes 'Oracle', '9'
 exec sys.sp_MSrepl_ORAdatatypes 'Oracle', '10'
 exec sys.sp_MSrepl_ORAdatatypes 'Oracle', '11'
 exec sys.sp_MSrepl_ORAdatatypes 'Oracle', '12'

…then maybe all the troubles would go away, right?

But you can’t just run that sort of thing in the standard SQL Server Management Studio. For this, you need to run it in DAC (or ‘dedicated administrative connection’) mode. To do that, fire up SSMS as normal and connect to your SQL Server instance as normal. Click New Query to start a new query window. Finally, click the ‘change connection’ button, just to the left of the database selector:

sqlserverrepl03

That will pop-up a new dialog box to allow you to make a fresh connection to your SQL Server instance:

sqlserverrepl04

But notice this time that I’ve stuck ADMIN: in front of my usual ‘server name’ connection string. That will connect you in the right ‘god mode’. You can’t really tell anything is different from a non-DAC connection, apart from the ‘admin:’ identifiers scattered all round the SSMS interface if you look in all the right places!

Anyway, once you’ve got a DAC connection, you can type in this lot of SQL and run it:

USE master

GO

exec sys.sp_MSrepl_ORAdatatypes 'Oracle', '12'

GO

exec sys.sp_MSrepl_MSSQL_ORA_datatypemappings @source_dbms = N'MSSQLSERVER', @destination_dbms = N'ORACLE', @destination_version = '12'

GO

If all goes according to plan, you should see this sort of response:

sqlserverrepl05

Once the data types have been created for Oracle 12, drop the MSREPL7 table from your Oracle database and then try re-initializing your replication subscription once again. This time, it should just work:

sqlserverrepl06

That shows you me dropping MSREPL7 in a SQL*Plus window in blue; then I went and re-initialized the replication in SQL Server (which you can’t see me doing). You then see me describing the structure of MSREPL7 in the blue window: notice (a) the table has been re-created despite just being dropped; and (b) it’s been created with NUMBER and RAW data types for its columns, not INTERVALS and BLOBs. The Synchronization Status progress window finally shows that replication transactions were successfully delivered to the Oracle database.

You can also issue the required fixup SQL in the command-line sqlcmd tool. Connect to your database in DAC mode with the “-A” switch:

sqlcmd -S winsql\winsqldb -E -A

The -S takes the form it does here because it describes the server AND the instance to connect to (I use named instances). Hence “winsql” is the server name and “winsqldb” is the instance to connect to running on that server. The -E switch means “use Windows authentication”, because that’s what I do! If you use named users, you’d use the -U switch and specify the username (and the -P switch if you fancied typing his password in clear text on the command line!). The -A switch means, as I’ve mentioned, connect in DAC mode.

Note that DAC mode is only available locally: that is, you have to be working on the actual server that’s hosting the SQL Server instance. If you insist on running the fixup SQL remotely, you can enable remote DAC access by right-clicking the instance in SSMS, clicking Facets, selecting Surface Area Configuration from the drop-down menu and then setting RemoteDacEnabled to True. You also need the SQL Browser service to be running (which you can switch on from the normal services utility, or by running the SQL Server Configuration Manager, clicking the SQL Server Services item and then the SQL Browser item in the right-hand pane (right-click it and click Start).

Incidentally: you may have thought (as I innocently did) that upgrading your SQL Server from (say) 2008 R2 to (say) 2016 would help. All I can tell you is that the code snippet which appears at the top of this piece is as identically 12c-deficient in SQL Server 2016 as it is in any previous version. I found this surprising and completely inexplicable: I’m prepared to forgive SQL Server vintage 2008 for not knowing about Oracle 12c; I find it much harder to understand why SQL Server 2016 is silent on the subject!

So, as far as I know, this trick of manually adding a reference to 12c in DAC mode is the only way to get any version of SQL Server replicating to Oracle at all.

Whether bolting on a ’12’ to the end of a bit of code and running it is actually supported by Microsoft, I can’t say: I’ve looked for any indication it’s a properly-supported technique in all the usual places and turned up nothing either way.

But if you’re replicating SQL Server to Oracle 11g and thinking of upgrading to 12c any time soon, it might be useful to bear in mind that you’re going to need this technique should you ever need to re-initialize your subscriptions 🙂