0

We are trying to automate the mirroring set up for multiple databases whose mirror is already in RESTORING state as the set up was part of log shipping

So when we switch from log shipping to mirror I am unable to find a way using TSQL or Powershell to set this mirroring between Primary and Secondary as most of the scripts on the internet are taking full backups initially and restoring. But we don't need to implement that as DB is already restoring.

We can set this up using GUI but it's time consuming when doing so for 20 DBs during migration. Please help with any mirroring automation that will set up for multiple databases having their mirror already set up with databases in restoring mode.

closed as too broad by Shawn Melton, LowlyDBA, mustaccio, Erik Darling, Mr.Brownstone Feb 8 '18 at 3:45

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • see this ps script. First test it to ensure you know what the script does or this one – Kin Shah Feb 2 '18 at 18:24
  • @Kin, thanks. But the problem with these scripts are all of them are taking full backups and then restoring on mirror and i already have that database set up on mirror after the switch from Log shipping – BeginnerDBA Feb 2 '18 at 19:22
  • 1
    You have to modify as per your needs .. It is said ... You can take the horse to the pond .. but the horse has to decide what to do next :-) – Kin Shah Feb 2 '18 at 19:25
  • haha @Kin... I totally agree.. let me see how can i as this powershell.. thanks though. – BeginnerDBA Feb 2 '18 at 19:32
  • This script will help as well stuart-moore.com/… – Kin Shah Mar 2 '18 at 15:44
0

Run the Alter Database command to add database to mirroring session

--Run on Mirror
ALTER DATABASE Test_DB 
    SET PARTNER = 'TCP://Mirror_Me:5022'

Run on both mirror and principal. Remember to change the port numbers appropriately

--Run on Principal                                                                                     
ALTER DATABASE Test_DB
    SET PARTNER = 'TCP://Mirror_Me:5023'

Read this for more info.

Adding Additional script for a complete configuration minus the backup and restore process:

--This script allows db mirroring to be setup iwth a witness. 
--Edited to skip the backup and restore process to the mirror
--Make right configuraton changes accordingly

--On A server
    CREATE ENDPOINT [Endpoint_Mirroring] --change endpoint name as required
    STATE=STARTED
    AS TCP (LISTENER_PORT = 5022)  --change port number as required. default is 5022
    FOR DATA_MIRRORING
    (ROLE = ALL);

    GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [domain\login_name] --change to required domain account

--On B server
    CREATE ENDPOINT [Endpoint_Mirroring] --change endpoint name as required (good to keep the same name)
    STATE=STARTED
    AS TCP (LISTENER_PORT = 5022) --change port number as required. 
    FOR DATA_MIRRORING
    (ROLE = ALL);

    GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [domain\login_name] --change to required domain account


--Change for current db
    ALTER DATABASE TEST_DB SET PARTNER = 'TCP://servername.domainname.net:5022'  --change the database/server name
    GO

--On B server
    ALTER DATABASE TEST_DB SET PARTNER = 'TCP://servername.domainname.net:5022' --change the database/server name
    GO

--To setup Witness on Server C
    CREATE ENDPOINT [Endpoint_Mirroring] --change endpoint name as required (good to keep the same name)
    STATE=STARTED
    AS TCP (LISTENER_PORT = 5022) --change port number as required. 
    FOR DATA_MIRRORING
    (ROLE = ALL);

    GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [domain\login_name]


--On Principal Server
    ALTER DATABASE Test_DB SET WITNESS = 'TCP://servername.domainname.net:5022' --change the database/server name
    GO

NOTE: This does not contain setting up certificates for encrypted connections on the endpoints/mirroring. It does not hand transfer of logins from principal to mirror. This is outside the scope of this question.

DISCLAIMER: This script is AS-IS and should be thoroughly tested before implementing into a production environment.

  • This will not achieve what i am looking for.. When i run this it will still look for database mirroring configuration which is not configured yet – BeginnerDBA Feb 2 '18 at 17:37
  • Your question was under the presumption that databases were in restoring and configured and needed to establish the mirroring session. What do you mean by database mirroring configuration? Are you referring to endpoints and/or witness? – samosql Feb 2 '18 at 17:59
  • @smaosql--> database at mirror is in restoring but mirroring is not set up – BeginnerDBA Feb 2 '18 at 18:05
  • @BeginnerDBA -- I have edited the answer and provided you with a script that can perform the configurations needed for mirroring. Make the necessary changes to the script to fit your environment. Always remember to test first – samosql Feb 2 '18 at 20:01
  • @thanks, does above script needs SQLCMD? – BeginnerDBA Feb 2 '18 at 22:40

Not the answer you're looking for? Browse other questions tagged or ask your own question.