asked on

SQL 2008 Code to setup Mirror with Witness

For a project I am on, I need to automate as much as possible to keep installers from having to use any GUI stuff (which just slows down installs and configurations).

I am using SQL 2008 SP2 on Server 2008 R2.

I have three SQL servers:

SQL1 - Principal
SQL2 - Mirror
SQLWitness - Witness

I can't believe the Mirror Configure Security wizard offers no way to save the results to a script. When I walk through the wizard, here is what it says it will do:

Click Finish to perform the following actions:

On the principal server instance, sql1
•      Modify the following properties of the mirroring endpoint:
•      Name: endpoint_mirroring
•      Listener Port: 7022
•      Encryption: Yes
•      Role: Partner
•      Add the service account for the principal server instance, 'WEBSTERSLAB\SYS_EVOSQCX1' as a login and grant it CONNECT permission on the mirroring endpoint.
•      Add the service account for the principal server instance, 'WEBSTERSLAB\SYS_EVOSQCX1' as a login and grant it CONNECT permission on the mirroring endpoint.

On the mirror server instance, sql2
•      Modify the following properties of the mirroring endpoint:
•      Name: endpoint_mirroring
•      Listener Port: 7022
•      Encryption: Yes
•      Role: Partner
•      Add the service account for the mirror server instance, 'WEBSTERSLAB\SYS_EVOSQCX1' as a login and grant it CONNECT permission on the mirroring endpoint.
•      Add the service account for the mirror server instance, 'WEBSTERSLAB\SYS_EVOSQCX1' as a login and grant it CONNECT permission on the mirroring endpoint.

On the witness server instance, sqlwitness
•      Modify the following properties of the mirroring endpoint:
•      Name: endpoint_mirroring
•      Listener Port: 7022
•      Encryption: Yes
•      Role: Witness
•      Add the service account for the witness server instance, 'WEBSTERSLAB\SYS_EVOSQCX1' as a login and grant it CONNECT permission on the mirroring endpoint.
•      Add the service account for the witness server instance, 'WEBSTERSLAB\SYS_EVOSQCX1' as a login and grant it CONNECT permission on the mirroring endpoint.

When I Finish the wizard this is what is on the screen once the mirror is complete.

Principal: TCP://SQL1.websterslab.com:7022
Mirror: TCP://SQL2.websterslab.com:7022
Witness: TCP://SQLWITNESS.websterslab.com:7022
High safety with automatic failover

What I am looking for is the code to do what the wizard is going to do.

I have looked at the following links but since I can barely spell SQL, I am having trouble understanding what all I need to put in my code.

http://technet.microsoft.com/en-us/library/ms190941.aspx
http://technet.microsoft.com/en-us/library/ms190456.aspx
http://technet.microsoft.com/en-us/library/ms175883.aspx
http://technet.microsoft.com/en-us/library/ms190430.aspx

I already have the code to:

create my database
update the security
backup database
backup transaction logs
restore database on Mirror
restore transaction logs on Mirror

Now I need to create the mirror for the database. If it matters, the database name will be something like "data_store".

All three SQL servers have the exact same disk configuration:

C - OS and programs
D - Databases
E - Logs
F - Backups

Let me know what other information you need.

Thanks

Carl Webster

ASKER

WOW!!! I ran a SQL Profiler trace while I went through the Mirror Configure Security wizard and got over 2100 lines of SQL stuff! MAN there is no way I would have figured out all that stuff.

Is all this really necessary?
mirror.txt

SOLUTION

EugeneZ

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Carl Webster

ASKER

I was never able to get this to work. I can barely spell SQL much less spend hours trying to debug something I really don't understand when the GUI does what I need.

I found this:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=165368

But wasn't able to make it work. I kept getting an error that my Mirror server is not configured for DATA ACCESS.

I found this:

http://msdn.microsoft.com/en-us/library/ms178532.aspx

But couldn't get past this error.

Really don't understand why Microsoft doesn't allow the ability to capture the script for this process.

If you are interested, here is the output from the sqlcmd:

(1 rows affected)

(1 rows affected)
Changed database context to 'master'.
SELECT * FROM OPENQUERY([SQL2], 'ALTER DATABASE [XA65_NA_USEH_P_DS_Non] SET PARTNER = ''TCP://SQL1.websterslab.com:7022''; select @@error')
Msg 7411, Level 16, State 1, Server SQL1, Line 1
Server 'SQL2' is not configured for DATA ACCESS.
ALTER DATABASE [XA65_NA_USEH_P_DS_Non] SET PARTNER = 'TCP://SQL2.websterslab.com:7022'
Msg 1418, Level 16, State 1, Server SQL1, Line 1
The server network address "TCP://SQL2.websterslab.com:7022" can not be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational.
ALTER DATABASE [XA65_NA_USEH_P_DS_Non] SET WITNESS = 'TCP://SQLWitness.websterslab.com:7022'
Msg 1416, Level 16, State 1, Server SQL1, Line 1
Database "XA65_NA_USEH_P_DS_Non" is not configured for database mirroring.

There is a windows firewall rule that does allow inbound port 7022 from any ipaddress.

I can successfully telnet from the principal server (SQL1) to the mirror server (SQL2) on port 7022.

I can successfully ping sql2.websterslab.com from SQL1.

Carl Webster

ASKER

Here are my current batch, .sql and output log files.
CreateMirror.bat.txt
CreateMirror.sql.txt
CreateMirrorLog.txt

SOLUTION

EugeneZ

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Carl Webster

ASKER

I added this line at the top of the script:

EXEC sp_serveroption @server = '$(varMirror)', @optname = 'DATA ACCESS', @optvalue = 'TRUE'
GO

And that specific error went away. The logfile now has:

(1 rows affected)

(1 rows affected)
Changed database context to 'master'.
SELECT * FROM OPENQUERY([sql2], 'ALTER DATABASE [XA65_NA_USEH_P_DS_Non] SET PARTNER = ''TCP://sql1.websterslab.com:7022''; select @@error')
Msg 226, Level 16, State 6, Server SQL2, Line 1
ALTER DATABASE statement not allowed within multi-statement transaction.
ALTER DATABASE [XA65_NA_USEH_P_DS_Non] SET PARTNER = 'TCP://sql2.websterslab.com:7022'
Msg 1418, Level 16, State 1, Server SQL1, Line 1
The server network address "TCP://sql2.websterslab.com:7022" can not be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational.
ALTER DATABASE [XA65_NA_USEH_P_DS_Non] SET WITNESS = 'TCP://sqlwitness.websterslab.com:7022'
Msg 1416, Level 16, State 1, Server SQL1, Line 1
Database "XA65_NA_USEH_P_DS_Non" is not configured for database mirroring.

I think the error is this section of code:

declare @sql varchar(1024)
declare @dbname sysname
set            @dbname = ''
while 1=1
begin
      select @dbname = min(name) from #databases where name > @dbname
      if @dbname is null
            break

      set @sql = 'SELECT * FROM OPENQUERY([$(varMirror)], ''ALTER DATABASE ['+@dbname+'] SET PARTNER = '''''+@partner+'''''; select @@error'')'
      print @sql
      exec (@sql)
end
go

I can successfully telnet from SQL1 to SQL2 on port 7022. There is a windows firewall rule that allows inbound port 7022 from any IP.

ASKER CERTIFIED SOLUTION

EugeneZ

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Carl Webster

ASKER

This is beyond my level of understand and frustration. I will end this question now and come back to it later when I have non project time to dedicate to it. I feel like I am ' ' this close but since I can barely spell SQL this is getting frustrating.

Thanks for your time. I will award you the points for your efforts in helping me learn.

I have asked a follow-up question here:

https://www.experts-exchange.com/questions/27981095/T-SQL-for-SQL-Server-2008-to-detemine-who-is-Principal-and-who-is-Mirror.html

Thanks

Carl Webster

ASKER

Thanks for the help and patience.