Mirroring SQL Server 2008 R2 Enterprise to Standard

If you attempt to mirror SQL Server 2008 R2 Enterprise to Standard edition, using SSMS, you will receive the following error message;

This mirroring configuration is not supported. Because the principal server instance, , is not Standard Edition, the mirror server instance cannot be Standard Edition.

You should never mirror between different editions, of SQL Server, in a live situation but it’s possible to configure this using TSQL. I wanted to test mirroring over a WAN link, the SQL Server we had over there was Standard Edition, and I didn’t want to go through the rigmarole of setting up another SQL box.

So here’s how you setup mirroring between Enterprise and Standard Editions. A simple mirroring setup is assumed with no witness box. I’ve kept the details down to an absolute minimum. See here for some more detailed documentation on database mirroring.

First create the endpoint on the principal server.

CREATE ENDPOINT Endpoint_Mirroring
    STATE=STARTED 
    AS TCP (LISTENER_PORT=5022) 
    FOR DATABASE_MIRRORING (ROLE=PARTNER)
GO

Grant access to the endpoint to the mirror SQL Server domain account. I’ve assumed here that you’re using domain accounts to run SQL under and both servers are in the same domain.

USE master ;
GO
CREATE LOGIN [domain\mirror] FROM WINDOWS ;
GO
 
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [domain\mirror];
GO

Now on your mirror create the endpoint;

CREATE ENDPOINT Endpoint_Mirroring
    STATE=STARTED 
    AS TCP (LISTENER_PORT=5022) 
    FOR DATABASE_MIRRORING (ROLE=PARTNER)
GO

Configure security;

USE master ;
GO
CREATE LOGIN [domain\principal] FROM WINDOWS ;
GO
 
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [domain\principal];
GO

Now we can mirror our databases. Before doing this you need to set the database you want to mirror to FULL recovery model, back it up and restore it on the mirror server with NORECOVERY.

ALTER DATABASE db
    SET PARTNER = 'TCP://principal.domain.co.uk:5022'
GO

Then back on the principal;

ALTER DATABASE db
    SET PARTNER = 'TCP://mirror.domain.co.uk:5022'
GO

Then fingers crossed mirroring for your database will successfully initialize!


Leave a Reply