October 22, 2014 at 12:46 pm
I need to configure mirroring on a TDE database. I found out since it's TDE (encrypted db) can't use Wizard to configure mirroring.
Unfortunately I can't script out mirroring set up like Log shipping.
I am using domain account (my login) to setup mirroring but I want to use different service account to run mirroring. just say ABCD\JohnSmith my account name but I want to use ABCD\MirrorSVC domain account to run mirroring. Can someone help me with the Script ?
October 23, 2014 at 5:15 am
smtzac (10/22/2014)
I need to configure mirroring on a TDE database. I found out since it's TDE (encrypted db) can't use Wizard to configure mirroring.
You can still use the wizard if you ensure you install the certificate from the principal to the mirror. Have you restored the cert yet??
smtzac (10/22/2014)
Unfortunately I can't script out mirroring set up like Log shipping.I am using domain account (my login) to setup mirroring but I want to use different service account to run mirroring. just say ABCD\JohnSmith my account name but I want to use ABCD\MirrorSVC domain account to run mirroring. Can someone help me with the Script ?
Are you manually restoring the database or letting the wizard complete this for you?
Restore the cert and retry the wizard.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 23, 2014 at 9:27 am
Yes I did. And got error. see below.
Where do I put service account name and how?
CREATE ENDPOINT <endpoint_mirroring>
STATE = STARTED
AS TCP ( LISTENER_PORT = 7023 )
FOR DATABASE_MIRRORING (ROLE=PARTNER)
October 23, 2014 at 9:30 am
Here is another one:
October 23, 2014 at 9:34 am
what command did you use to backup and restore the cert?
smtzac (10/23/2014)
Where do I put service account name and how?CREATE ENDPOINT <endpoint_mirroring>
STATE = STARTED
AS TCP ( LISTENER_PORT = 7023 )
FOR DATABASE_MIRRORING (ROLE=PARTNER)
You don't!!
The link you posted above has some good info but is missing critical TSQL steps.
Once you create the endpoint using the script you posted above, you then create a login for the svc account of the partner instance(s). Once the login is created you grant the login(s) CONNECT on the Endpoint. Do you understand this?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 23, 2014 at 12:40 pm
I already have a service account created in Logins: SQL\SVC.
---CREATE ENDPOINT <endpoint_mirroring>
STATE = STARTED
AS TCP ( LISTENER_PORT = 7023 )
FOR DATABASE_MIRRORING (ROLE=PARTNER)-----
If I run above script, I think, my window authentication will be used.
So how to write query so SQL\SVC can run on both principle and mirror server?
Should I run below after the above script?
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [SQL\SVC];
GO
October 23, 2014 at 2:19 pm
smtzac (10/23/2014)
I already have a service account created in Logins: SQL\SVC.---CREATE ENDPOINT <endpoint_mirroring>
STATE = STARTED
AS TCP ( LISTENER_PORT = 7023 )
FOR DATABASE_MIRRORING (ROLE=PARTNER)-----
If I run above script, I think, my window authentication will be used.
So how to write query so SQL\SVC can run on both principle and mirror server?
Should I run below after the above script?
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [SQL\SVC];
GO
Ok, let me explain it in a little more detail (i'll assume you are not using a witness, since you haven't mentioned it)
I have server A with Instance A and the SQL Server service runs as account MyDomain\InstA
I have server B with Instance B and the SQL Server service runs as account MyDomain\InstB
My endpoint creation statements would look thus
On the Principal which is Instance A, I execute
CREATE ENDPOINT [db_mirroring]
STATE = STARTED
AS TCP ( LISTENER_PORT = 7023 )
FOR DATABASE_MIRRORING (ROLE=PARTNER)
--create the mirror instance login
CREATE LOGIN [MyDomain\InstB] FROM WINDOWS
GO
--Grant connect on the endpoint
GRANT CONNECT ON Endpoint::db_mirroring TO [Mydomain\InstB]
GO
On the mirror which is Instance B, I execute
CREATE ENDPOINT [db_mirroring]
STATE = STARTED
AS TCP ( LISTENER_PORT = 7023 )
FOR DATABASE_MIRRORING (ROLE=PARTNER)
--create the mirror instance login
CREATE LOGIN [MyDomain\InstA] FROM WINDOWS
GO
--Grant connect on the endpoint
GRANT CONNECT ON Endpoint::db_mirroring TO [Mydomain\InstA]
GO
Does this make sense?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 24, 2014 at 5:19 am
Thank you so much for your time. Lastly, Just wanted to confirm with you couple things.
1. Since I already have service account [SQL\SVC] created I DO NOT need this below step right??
CREATE LOGIN [MyDomain\InstA] FROM WINDOWS
GO
2. If I DON'T DO below step:
GRANT CONNECT ON Endpoint::db_mirroring TO [Mydomain\InstA]
GO
Will SQL connect 'Endpoint' using my Window Authentication for ex: [Mydomain\JohnSmith] ??
October 24, 2014 at 5:23 am
smtzac (10/24/2014)
Since I already have service account [SQL\SVC] created I DO NOT need this below step right??
is the same svc account being used for the sql server services on the principal and the mirror or are you using separate accounts for each?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 24, 2014 at 7:54 am
Yes. [SQL\SVC] is running both sql services on principle and mirror server. So should I 'Grant' using below script?
GRANT CONNECT ON Endpoint::db_mirroring TO [SQL\SVC] OR Sql automatically assigns [SQL\SVC] for mirroring?
If so how do I query which account is being used for mirroring?
Thanks a million.
October 24, 2014 at 8:48 am
if the services for both instances use the same account you shouldnt need to do anything, as the acct will be a sysadmin anyway
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply