Re: SQL Server 2005, Database Mirroring and SQL Server Agent

From: Tibor Karaszi (tibor_please.no.email_karaszi_at_hotmail.nomail.com)
Date: 02/09/05 

Date: Wed, 9 Feb 2005 12:04:00 +0100

Are you saying that you are mirroring the msdb database? This is where the jobs are stored. I didn't
think you even could mirror msdb... 

-- 
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Jac Timms" <jactimms@discussions.microsoft.com> wrote in message 
news:6728BA3A-1EC4-4101-8ADF-607D425B61D3@microsoft.com...
> Do you know of a way to do that?
> I can't see anything in the new job window that allows me to do that...
> The watch server can failover the princible server so that the mirror
> becomes the princible at anytime if the princible fails to respond. So then
> I'd need to make sure the job runs on what was the mirror and is now the
> princible.
> The SQL I put in my original post will check that it is the principle
> database and works fine, but I thought there must be a way for agent to check
> if its the princible database without doing anything if its not. The way I've
> come up with just seems like a workaround way of doing it, that's all.
> Thanks...
>
>
> "Tibor Karaszi" wrote:
>
>> Perhaps I'm missing something, but can't you just make sure that this job only executes on the
>> principle machine?
>>
>> -- 
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> http://www.sqlug.se/
>>
>>
>> "Jac Timms" <Jac Timms@discussions.microsoft.com> wrote in message
>> news:E42E0E5E-4DED-4B8D-9E59-7A272FF64469@microsoft.com...
>> > Hi,
>> >
>> > We are building an application using SQL Server 2005 and database mirroring.
>> > I need to use SQL Server Agent to update a table every night, however I need
>> > a way of making sure it only runs on the principle server.
>> >
>> > I have come up with the following solution that I can just put in my job,
>> > which seems to work fine, but I want to know if there is a 'proper' way to do
>> > this?
>> >
>> > IF (SELECT mirroring_role FROM sys.databases WHERE name = 'BubbleDB') = 1
>> > BEGIN
>> > -- this is the principle, do the update
>> > END
>> > ELSE
>> > BEGIN
>> > -- This is the mirror server, do nothing
>> > END
>> >
>> > Many thanks,
>> > -Jac Timms
>>
>>
>>

Relevant Pages

  • Re: Licensing for DB mirroring
    ... As long as you don't use it for anything other than a pure mirror it does not require a license similar to the way clustering works. ... Actually you do not need a license for the database server running the ... SQL Server Pricing and Licensing ...
    (microsoft.public.sqlserver.setup)
  • Re: Licensing for DB mirroring
    ... than a pure mirror it does not require a license similar to the way clustering works. ... Actually you do not need a license for the database server running the ... SQL Server Pricing and Licensing ...
    (microsoft.public.sqlserver.setup)
  • Re: Cant start mirroring
    ... Not setting mirror database' s recovery state as norecovery is one of the reasons of this problem. ... Are your Logins identical on your Principal and Mirror SQL Server Instances? ...
    (microsoft.public.sqlserver.setup)
  • Re: SQL Server 2005, Database Mirroring and SQL Server Agent
    ... Tibor Karaszi, SQL Server MVP ... > I want the job to run every night to update a table in the mirrored database. ... the watch server will fail over to the mirror server making that the ... > principle. ...
    (microsoft.public.sqlserver.server)
  • Re: SQL Server 2005, Database Mirroring and SQL Server Agent
    ... we've building one database that is to be mirrored. ... > think you even could mirror msdb... ... > Tibor Karaszi, SQL Server MVP ... >> The SQL I put in my original post will check that it is the principle ...
    (microsoft.public.sqlserver.server)