little_birdie


Hi Guys,

We have three servers.

Server A - Primary

Server B - Mirror

Server C - Stand Alone (also used for reporting, but, not using SQL Reporting).

All the three servers are interconnected using linked servers.

Heres the brief overview of my problem:

A certain procedure requires me to get live data from the 'Primary' to Server C on regular basis. This procedure will be scheduled to run once a day on Server C.

Heres the code:

create procedure sp_getDataFromPrimary

as

--First I check if Server A is primary

if exists( select * from [serverA].master.sys.database_mirroring where name = 'database1' and

mirroring_role_desc = 'primary')

begin

insert into [serverC].[database1].[dbo].[table1]

select * from [serverA].[database1].[dbo].[table1]

end

else

begin

insert into [serverC].[database1].[dbo].[table1]

select * from [serverB].[database1].[dbo].[table1]

end

go

When I try to create the above procedure, I get an error message similar to 'cannot access

[server B].[database1].[dbo].[table1] as the database is being mirrored. I tried to use variablesfor the server, database and tables, but, no avail -- I still get the same error.

My qn now is : How can I make this work. If you think my approach is faulty, pls, suggest any other.

Eagerly awaiting for your response.

Thankyou.

p.s: Cannot use SSIS for it will complicate the other additional processing that needs to be done.




Re: How to get data from Primary

Deepak Rangarajan


Hi,
You have 3 servers configured of which Server A - Principal (i.e primary as per you) Server B - Mirror and Server C - seperate stand alone server.
You want to run and get the proc to be run in C, thew 1st condition checks if the server is primary and if its true it will get the data into server C. I the condition is false then it will retrieve the data from B and insert it into C which is NOT possible.
Because the mirror server will always be in Restoring State so that you cannot perform any operations in the mirror server, not even read the data from it.

In both the query you are trying to retrieve data from A or B (mirror server) and put it into C. Since you have configured mirroring between A and B the data in A will be available in B also ! So the 2nd condition is not required !

If you still want to use the same query to retrieve data you have an option !
Create Database snapshot for the mirror server and you can query the data from snapshot !
refer the below link to create snapshot http://sql-articles.com/articles/snapshot/snap-create.htm

Regards
Deepak






Re: How to get data from Primary

Vidhya Sagar

Hi little_birdie,

Is Database mirroring configured in Automatic failover I hope you have not configured automatic failover, since you have not mentioned witness server.

If not then your procedure won't work until you manually failover to server B, else your server B will always in mirrored state, Hence I agree with Deepak's point.

Hi Deepak,

In case if he configured automatic failover, then the procedure should work correctly. Consider the below scenario

If server A goes down and server B acts as principal, if he calls the procedure the condition will fail and it will point to serverB and it should take datas from server B to server C. Right

Lets wait for little_birdie's answer whether mirroring is configured as automatic failover.







Re: How to get data from Primary

little_birdie

Hi guys Deepak / Vidhya Sagar,

Thanks for the prompt response.

Deepak :

I completly agree with your point that server B (or mirror) will always be in restoring state, hence my Insert statement pertaining to Server B will always fail so long as the server is Mirror. And you also mentioned that once the Fail over occurs (i.e., when Server A is the Mirror), the data from Server B is as update and hence data can be inserted from server B -- which, again, I agree.

Now what I need clarification about is, as I am running this procedure on a stand alone server (Server C), which is linked with both Primary and Mirror. So, how will server C know which server to get data from 'coz , server A and Server B have different names (sql4 and sql5). As you have noticed I use for part name [server A].[database 1].[dbo].[table1] or

[server B].[database 1].[dbo].[table1]

Had I been doing this stuff on SSIS, I would have mentioned 'Failover Partner' as ServerB and then, the procedure would have automatically chosen 'Primary'. Because of other complications, I will not be using SSIS.

Vidhya Sagar,

I am sorry that I did not add this point earlier. We also have a witness, so, the failover can be both manual and automatic.

''If server A goes down and server B acts as principal, if he calls the procedure the condition will fail and it will point to serverB and it should take datas from server B to server C. Right ''

Right!

Also, on a different note, when I am executing Insert statement for a linked server, the query execution is extremly slow. Can you tell me a means to make is faster

Thanks Again,

Eagerly awaiting for your response..

little_birdie





Re: How to get data from Primary

Deepak Rangarajan

Since there is a linked server configured in Server C to access the servers A and B and in your query you have clearly stated to check which which one is the primary server, at any point there will be only one primary server. Hence it will check if the Server A is primary if its True it will retrieve data and put it into Server C else it will do the other way and retrieve data from B.

Witness server is required only in case of configuring mirroring in High Availability mode. If you have Witness server the failover will be automatic and in few seconds !

Regards
Deepak







Re: How to get data from Primary

Vidhya Sagar

Hi Little,

We have missed out one small thing. "sys.database_mirroring" catalog view doesn't have a column named "name" so you can use the column "database_id" for this, You can refer @ http://msdn2.microsoft.com/en-us/library/ms178655.aspx

Hence the above query wont work since the query is calling wrong column name. I've modified the code and given below, pls try this & post the result

create procedure sp_getDataFromPrimary
as
--First I check if Server A is primary
if exists( select * from [serverA].master.sys.database_mirroring where database_id= 'your principal database id' and mirroring_role_desc = 'primary')
begin
insert into [serverC].[database1].[dbo].[table1]
select * from [serverA].[database1].[dbo].[table1]
end
else
begin
insert into [serverC].[database1].[dbo].[table1]
select * from [serverB].[database1].[dbo].[table1]
end
go


If possible try to validate the condition in witness server itself (eg. select * from [serverC].master.sys.database_mirroring where database_id= 'your principal database id' and mirroring_role_desc = 'primary'), im not sure about this code), because if the primary goes down and when the procedure runs the condition step will get server A as RTO and hence it will come out of the condition and ends so data is not inserted into serverC.


Check whether the above code helps you







Re: How to get data from Primary

little_birdie

Vidya Sagar,

**'We have missed out one small thing. "sys.database_mirroring" catalog view doesn't have a column named "name" so you can use the column "database_id" **

Sorry, there was a misprint on my side, I did use database_id (not name), so, this is not a syntax issue.

Issue, as it turns out is that SQL Server Primary validation was Failing as it was trying to vaildate the availability of the database on the server SQL5. I had to use 'dynamic SQL ' to work around.

if exists( select * from [serverA].master.sys.database_mirroring where database_id= 'your principal database id' and mirroring_role_desc = 'primary')
begin

insert into [serverC].[database1].[dbo].[table1]
exec('select * from [serverA].[database1].[dbo].[table1]')
end
else
begin
insert into [serverC].[database1].[dbo].[table1]
exec('select * from [serverB].[database1].[dbo].[table1]')
end
go

Nevertheless, your suggestions have been extremly helpful. Thank you.