Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Script for mirroring and log shipping Expand / Collapse
Author
Message
Posted Tuesday, June 25, 2013 9:54 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, September 12, 2014 11:26 AM
Points: 72, Visits: 332
Hi,

I have been asked to setup a script to make setting up mirroring a little quicker. The main problem is one must run the ALTER commands and create endpoint command on two different servers. Can this be done within the script with something like Connect to:: or linked servers, or something I don't know about?

CREATE ENDPOINT EndPointName
STATE=STARTED AS TCP(LISTENER_PORT = PortNumber, LISTENER_IP = ALL)
FOR DATA_MIRRORING(ROLE = PARTNER, AUTHENTICATION = WINDOWS NEGOTIATE, ENCRYPTION = REQUIRED ALGORITHM RC4)

-- On Mirror
-- How can I connect?
CREATE ENDPOINT EndPointName
STATE=STARTED AS TCP(LISTENER_PORT = PortNumber, LISTENER_IP = ALL)
FOR DATA_MIRRORING(ROLE = PARTNER, AUTHENTICATION = WINDOWS NEGOTIATE, ENCRYPTION = REQUIRED ALGORITHM RC4)

/*Set partner and setup job on mirror server*/

ALTER DATABASE DatabaseName SET PARTNER = N'TCP://PrincipalServer:PortNumber'
EXEC sys.sp_dbmmonitoraddmonitoring -- default is 1 minute



Post #1467255
Posted Tuesday, June 25, 2013 11:37 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:48 AM
Points: 12,915, Visits: 32,075
For Linked servers, i know you can do DDL commands, like CREATE TABLE, via EXECUTE AT;

I'd bet any other commands like the ALTER and CREATE ENDPOINT would work just as well ,
EXECUTE ( 'CREATE TABLE AdventureWorks2008R2.dbo.SalesTbl 
(SalesID int, SalesName varchar(10)) ; ' ) AT SeattleSales;




Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1467302
Posted Tuesday, June 25, 2013 12:20 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, September 12, 2014 11:26 AM
Points: 72, Visits: 332
Yes but those DDL commands often specify a from clause which is where I can put in the linked server. These do not.
Post #1467315
Posted Tuesday, June 25, 2013 12:25 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:48 AM
Points: 12,915, Visits: 32,075
lmacdonald (6/25/2013)
Yes but those DDL commands often specify a from clause which is where I can put in the linked server. These do not.


ahh, you missed the point.

I created a linked server named SeattleSales.


the EXECUTE('some command') AT SeattleSales performs the action on the remote linked server, and not locally; you only need a four-part name for DML commands; for DDL, you use the AT <MyLinkedServer> format.


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1467316
Posted Tuesday, June 25, 2013 12:34 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, September 12, 2014 11:26 AM
Points: 72, Visits: 332
Oh nice, that may just work )

However my linked server is actually on the same machine, but another instance. There is a syntax error, it does not like the \ between servername\instance so I tried it like servername.instance and it did not like the slash or the period.
Post #1467317
Posted Tuesday, June 25, 2013 12:39 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:48 AM
Points: 12,915, Visits: 32,075
lmacdonald (6/25/2013)
Oh nice, that may just work )

However my linked server is actually on the same machine, but another instance. There is a syntax error, it does not like the \ between servername\instance so I tried it like servername.instance and it did not like the slash or the period.


oh that's easy to fix too; you just need to put it in brackets:

EXECUTE AT('some command') AT [macdonald\SQL2008]


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1467318
Posted Tuesday, June 25, 2013 1:44 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, September 12, 2014 11:26 AM
Points: 72, Visits: 332
Thanks, that helped so much!
Post #1467361
Posted Wednesday, June 26, 2013 8:21 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, September 12, 2014 11:26 AM
Points: 72, Visits: 332
To bad variables get lost when you do an execute at another server. I thought everything was good but now I have that problem.
Post #1467693
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse