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

From: Jac Timms (jactimms_at_discussions.microsoft.com)
Date: 02/09/05 

Date: Wed, 9 Feb 2005 09:09:06 -0800

Yeah that's right. But perhaps I wasn't clear before... :o)
I want the job to run every night to update a table in the mirrored database.
Now if I just create the job on the principle server and that server goes
down, the watch server will fail over to the mirror server making that the
principle (the server that has no job on it).
When the server with the job on it comes back up, the watch server will make
it the mirror server, however the job will fail because you can not update a
database when it is in mirror mode; it is just being synced with the
principle.
So what I need is to make a job on both the principle and mirror server and
have the job only run if it is being executed on the principle database. This
will make sure that no matter what server is the principle the job will get
run every night.
Hope that makes more sense…
Thanks for the replies…

"Tibor Karaszi" wrote:

> So as you aren't mirroring msdb, and the jobs are stored in msdb, then the only place where the job
> will be executed is the server where you create the job.
>
> --
> 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:8BAB6BD9-4E42-4EBD-B763-359DE2DB085E@microsoft.com...
> > No sorry, we've building one database that is to be mirrored.
> > We are not mirroring any other database.
> >
> > "Tibor Karaszi" wrote:
> >
> >> 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: replication
    ... mode, when a session begins, the mirror server synchronizes the mirror ... database with the principal database as quickly as possible. ... substantial if the principal server is under a heavy work load or the ...
    (comp.databases.ms-sqlserver)
  • Re: replication
    ... Roy Harvey (SQL Server MVP) wrote: ... mode, when a session begins, the mirror server synchronizes the mirror ... database with the principal database as quickly as possible. ... substantial if the principal server is under a heavy work load or the ...
    (comp.databases.ms-sqlserver)
  • Re: Licensing for DB mirroring
    ... Actually you do not need a license for the database server running the ... database snapshot on the mirror database you will need a license. ...
    (microsoft.public.sqlserver.setup)
  • Re: SQL Server 2005, Database Mirroring and SQL Server Agent
    ... Are you saying that you are mirroring the msdb database? ... think you even could mirror msdb... ... > The SQL I put in my original post will check that it is the principle ... >> Tibor Karaszi, SQL Server MVP ...
    (microsoft.public.sqlserver.server)
  • Re: Sql serve clustering
    ... You can't not take a single database and mirror it multiple times. ... >> We are planning to have sql server failover clstering. ...
    (microsoft.public.sqlserver.replication)