Question on multiserver environment in SQL Server 2005

I am creating a backup strategy for our non-production SQL Server 2005 data. We wish to utiliza the Master/Target Server scenario avaible in SQL Server 2005 with events directed to the Master Server. The question I have is what is the symptoms from the Target Servers if the Master Server fails and is unavailable for whatever reason there might be? I can change the event notification back to local server, but what other impact is there and are there any recommendations as to handle a Master Server outage so Maintenance plans can continue to execute as scheduled on Target Servers.

Thank you!

Here's a response, but one that you won't like. Virtually nobody I know uses the Master/Target option. I am sure there must be some people who do, but it is rare. It is not used because the feature is not very robust or flexible.

Most people use a third-party monitoring program to accomplish what are want to do. There are many available. In addition, there are various third-party backup programs that include backup monitoring built-in.

Question on multiserver environment in SQL Server 2005

I am creating a backup strategy for our non-production SQL Server 2005 data. We wish to utiliza the Master/Target Server scenario avaible in SQL Server 2005 with events directed to the Master Server. The question I have is what is the symptoms from the Target Servers if the Master Server fails and is unavailable for whatever reason there might be? I can change the event notification back to local server, but what other impact is there and are there any recommendations as to handle a Master Server outage so Maintenance plans can continue to execute as scheduled on Target Servers.

Thank you!

Here's a response, but one that you won't like. Virtually nobody I know uses the Master/Target option. I am sure there must be some people who do, but it is rare. It is not used because the feature is not very robust or flexible.

Most people use a third-party monitoring program to accomplish what are want to do. There are many available. In addition, there are various third-party backup programs that include backup monitoring built-in.

Question on 'load/replace" in SQL Server

I have to periodically overwrite selected tables in one environment
(Prod) with the contents of the same named tables from another
environment (Staging).
There are about 200 tables involved.
In SQL Server what would be the best way to do this and minimize
logging?
Thanks in advance.
Gerry"DataPro" <datapro01@.yahoo.com> wrote in message
news:1166795421.720680.18940@.42g2000cwt.googlegroups.com...
>I have to periodically overwrite selected tables in one environment
> (Prod) with the contents of the same named tables from another
> environment (Staging).
> There are about 200 tables involved.
> In SQL Server what would be the best way to do this and minimize
> logging?
"Best" is relative.
I'd suggest creating a DTS package and on the receiving server either put it
into simple mode (but break up your backup chain) or bulk-logged during the
load.
Having said that, I do a quarterly update with 3 very large tables and still
tweak it, but I use BCP on one and and bulk insert on the other and a batch
file. And also do log-backups between the 3 tables.
> Thanks in advance.
> Gerry
>|||Thanks much
Greg D. Moore (Strider) wrote:
> "DataPro" <datapro01@.yahoo.com> wrote in message
> news:1166795421.720680.18940@.42g2000cwt.googlegroups.com...
> >I have to periodically overwrite selected tables in one environment
> > (Prod) with the contents of the same named tables from another
> > environment (Staging).
> >
> > There are about 200 tables involved.
> >
> > In SQL Server what would be the best way to do this and minimize
> > logging?
> "Best" is relative.
> I'd suggest creating a DTS package and on the receiving server either put it
> into simple mode (but break up your backup chain) or bulk-logged during the
> load.
> Having said that, I do a quarterly update with 3 very large tables and still
> tweak it, but I use BCP on one and and bulk insert on the other and a batch
> file. And also do log-backups between the 3 tables.
>
> >
> > Thanks in advance.
> > Gerry
> >

Question on load/replace" in SQL Server

I have to periodically overwrite selected tables in one environment (Prod) with the contents of the same named tables from another environment (Staging).

There are about 200 tables involved.

In SQL Server what would be the best way to do this and minimize logging?

Thanks in advance.
GerryI usually do this:

0. bcp out all data from source
1. disable FKs as appropriate in dest
2. truncate/delete from tables in dest
3. bcp in all data to dest
4. re-enable FKs in dest

You could probably also use DTS/SSIS for this, but I prefer bcp because I like the cmd line.

EDIT: one thing I forgot: step 2.1 should be to drop all indexes in dest, then at 4.1 recreate indexes. with indexes dropped the data will import much faster.

Question on Deployment

hello everyone,

I have a question on deployment of SSIS.

My team plans to develop a lot of SSIS packages.

I want my deployment environment to be as centralized as possible, but the processing environment to be distributed.

Here is what I had in mind. I have one server running SQL Server DB and SQL Server Agent. I have all the SSIS packages deployed on this server and schedule them as jobs in SQL Server Agent.

Now I have two other machines just running SSIS service (no other component). When the agent executes the packages, he should use either of the two machines. (Load balancing would be good, or I can manually assign the packages to the machines too).

is this possible? recommended?

Another approach would be that each SSIS Server, also has SQL Server and Agent installed. And respective packages are installed and scheduled on their respective servers. But here I have to maintain the packages and schedules on 2 machines.

regards,
Abhishek.

MSDN Student wrote:

hello everyone,

I have a question on deployment of SSIS.

My team plans to develop a lot of SSIS packages.

I want my deployment environment to be as centralized as possible, but the processing environment to be distributed.

Here is what I had in mind. I have one server running SQL Server DB and SQL Server Agent. I have all the SSIS packages deployed on this server and schedule them as jobs in SQL Server Agent.

Now I have two other machines just running SSIS service (no other component). When the agent executes the packages, he should use either of the two machines. (Load balancing would be good, or I can manually assign the packages to the machines too).

is this possible? recommended?

Another approach would be that each SSIS Server, also has SQL Server and Agent installed. And respective packages are installed and scheduled on their respective servers. But here I have to maintain the packages and schedules on 2 machines.

regards,
Abhishek.

You cannot execute packages remotely so the first scenario you outlined is not possible.

-Jamie

|||

OK. How about this one

On one machine I have SQL Server installed where I deploy all my SSIS packages. On the other two machines I have SQL Agent and SSIS Service installed.

The two machines will read the common SQL Server repository to read SSIS packages and execute them on the respective machines.

I will still be able to keep all the packages in one central SQL Server location, but I will have to configure the SQL Jobs on both the SSIS servers.

Will this work?

regards,
Abhishek.

|||

Yep, that will work fine.

I have to ask why do you want to do this? Unless you are re-using exactly the same (version) package across several machines, it seems like the central dependency doesn't offer that much.