Can I implement replication between MS SQL Server Enterprise Edition and MSDE?

Hello.
One of our customers has requested that he wants to have data on his PC and
synchronize his data as needed.
How can I implement this?
Is it possible to synchronize data between MS SQL Server and MSDE?
Or is there any other solution?
Any advice or hint would be appreciated.AFAIR, yes. But you cannot have the MSDE as replication publisher.
Best regards,
Benny
"Kim Keuk Tae" <seiyanotenshi@.hotmail.com> skrev i en meddelelse
news:OADOIg$sDHA.2004@.TK2MSFTNGP10.phx.gbl...
> Hello.
> One of our customers has requested that he wants to have data on his PC
and
> synchronize his data as needed.
> How can I implement this?
> Is it possible to synchronize data between MS SQL Server and MSDE?
> Or is there any other solution?
> Any advice or hint would be appreciated.
>
>|||"Kim Keuk Tae" <seiyanotenshi@.hotmail.com> wrote in message
news:OADOIg$sDHA.2004@.TK2MSFTNGP10.phx.gbl...
> Hello.
> One of our customers has requested that he wants to have data on his PC
and
> synchronize his data as needed.
> How can I implement this?
> Is it possible to synchronize data between MS SQL Server and MSDE?
Hello, it is possible but not all replication types are available. I suggest
you follow this link:
http://msdn.microsoft.com/vstudio/downloads/addins/msde/examining.aspx
Regards,
Tomislav Kralj
MCSD/.NET, MCDBA
tomislav.kralj1@.zg.tel.hrsql

Can I implement a replication between sql server 2005 and IBM AS400 DB?

Hello,

I want to set up As400 DB as a publisher and the sql server 2005 as a subscriper, so that I can read some data from AS400 DB in sql server 2005 and every evening, I need the replicated data from AS400 to be refreshed.

Can I do it ? If yes, how to do it?

Thanks

You cannot subscribe to an AS400 publication from SQL. SQL would have to be the publisher and push via an OLEDB driver.

You can probibly do it pushing to SQL but this would be a question for an AS400 forum.

Martin

Can I have replication between two completely different schemas?

Hi SQL guys,

I have 2 different databases and an import tool that reads from one and fills the other one and then whenever we run it, it should synch the two databases and its kind of a heavy task. I was wondering if i can replace it with replication between these databases. I defined publisher and subscriber, but i cannot find anywhere that i can say which field in source maps to which field in destination.

Is it possible at all to have a replication between 2 databases with totally different schemas? If so, please let me know how.

Thanks,

Sina

No, the schemas should match. Aleast they should be compatible.|||

yes, you can, but only if the subscriber is not updating the data.

if you are only importing data from the publisher, then you can , what you need is create a transformable subscription, you use dts in that subscription to make all matching or convertions you need between source and destination.

Thank you

Armando Marrero

|||

hi Armando...

can you please brief the steps of making the matches between source and destination for transformable subscription.

Thanks in advance

Can I have replication between two completely different schemas?

Hi SQL guys,

I have 2 different databases and an import tool that reads from one and fills the other one and then whenever we run it, it should synch the two databases and its kind of a heavy task. I was wondering if i can replace it with replication between these databases. I defined publisher and subscriber, but i cannot find anywhere that i can say which field in source maps to which field in destination.

Is it possible at all to have a replication between 2 databases with totally different schemas? If so, please let me know how.

Thanks,

Sina

No, the schemas should match. Aleast they should be compatible.|||

yes, you can, but only if the subscriber is not updating the data.

if you are only importing data from the publisher, then you can , what you need is create a transformable subscription, you use dts in that subscription to make all matching or convertions you need between source and destination.

Thank you

Armando Marrero

|||

hi Armando...

can you please brief the steps of making the matches between source and destination for transformable subscription.

Thanks in advance

Can I have replication between two completely different schemas?

Hi SQL guys,

I have 2 different databases and an import tool that reads from one and fills the other one and then whenever we run it, it should synch the two databases and its kind of a heavy task. I was wondering if i can replace it with replication between these databases. I defined publisher and subscriber, but i cannot find anywhere that i can say which field in source maps to which field in destination.

Is it possible at all to have a replication between 2 databases with totally different schemas? If so, please let me know how.

Thanks,

Sina

No, the schemas should match. Aleast they should be compatible.|||

yes, you can, but only if the subscriber is not updating the data.

if you are only importing data from the publisher, then you can , what you need is create a transformable subscription, you use dts in that subscription to make all matching or convertions you need between source and destination.

Thank you

Armando Marrero

|||

hi Armando...

can you please brief the steps of making the matches between source and destination for transformable subscription.

Thanks in advance

sql

Can I delete Transaction log on subscriber

Hi,
One of our subscriber server is full due to transaction
log too large. The replication was failed without no
space for the transaction log. My question is can I
delete the transation log? Or is there any way I can free
some space on that subscriber server? Please help me.
p.s. we do full backup on our publisher server every day.
Thanks,
While you can delete it I would urge you not to do this. By shutting down
SQL Server and deleting the actual log physical file is a bad idea, although
in some people have claimed to be able to do it, I have never been able to.
I hope you mean truncating it and perhaps shrinking the log file. This is a
better option.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Ping" <anonymous@.discussions.microsoft.com> wrote in message
news:228501c506fc$0ecd0690$a401280a@.phx.gbl...
> Hi,
> One of our subscriber server is full due to transaction
> log too large. The replication was failed without no
> space for the transaction log. My question is can I
> delete the transation log? Or is there any way I can free
> some space on that subscriber server? Please help me.
> p.s. we do full backup on our publisher server every day.
> Thanks,
|||Hi hilary,
Thanks for your reply.
Regarding truncating and shrinking, which one do you
recommend?
Tks
>--Original Message--
>While you can delete it I would urge you not to do this.
By shutting down
>SQL Server and deleting the actual log physical file is a
bad idea, although
>in some people have claimed to be able to do it, I have
never been able to.
>I hope you mean truncating it and perhaps shrinking the
log file. This is a
>better option.
>--
>Hilary Cotter
>Looking for a SQL Server replication book?
>http://www.nwsu.com/0974973602.html
>"Ping" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:228501c506fc$0ecd0690$a401280a@.phx.gbl...
free[vbcol=seagreen]
day.
>
>.
>

can I change owner of replicated tables?

can I change the owner of tables that are part of a merge replication
without effecting that replication? or will I need to drop all replication,
change owners, then re-setup replication?
any info is appreciated. Thanks.
no you can't and this will cause major problems.
The best thing to do is to drop your publication, drop the tables on the
subscriber and recreate it.
"djc" <noone@.nowhere.com> wrote in message
news:%23UZcu%23OZEHA.1000@.TK2MSFTNGP12.phx.gbl...
> can I change the owner of tables that are part of a merge replication
> without effecting that replication? or will I need to drop all
replication,
> change owners, then re-setup replication?
> any info is appreciated. Thanks.
>
|||ok. thanks.
Hillary,
are you familiar with the merge repl problems mentioned by Kestutis
Adomavicius in post titled: 'cannot remove repl... cannot delete database'?
If so could you offer some more insight into that and the questions I had in
reply to him?
Thanks... your other posts have helped me out a lot.
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:OAIF8ZPZEHA.212@.TK2MSFTNGP11.phx.gbl...
> no you can't and this will cause major problems.
> The best thing to do is to drop your publication, drop the tables on the
> subscriber and recreate it.
> "djc" <noone@.nowhere.com> wrote in message
> news:%23UZcu%23OZEHA.1000@.TK2MSFTNGP12.phx.gbl...
> replication,
>
|||I have been able to repro the problem with the conflict tables that Kestutis
has pointed out. You get this error when dropping replication and it is
simple to fix.
Kestutis (quite brilliantly I might add) has found some problems with the
merge replication stored procedures which may or may not cause problems. The
conflict table dropping is definately related to this problem which Kestutis
has fixed.
I have successfully implemented a central publisher with 60 subscribers
using non dbo table owners. This replication solution was relatively
maintenance free for the two years I was working on it. So, it is not a show
stopping problem, but what problems might occur is unknown. What will happen
in a future service pack is also unknown.
I have forwarded Kestutis's fixed replication stored procedures to a contact
of mine at Microsoft - with credit to Kestutis of course. Hopefully
Microsoft will implement Kestutis's suggestions.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"djc" <noone@.nowhere.com> wrote in message
news:%23ZV2u1PZEHA.2456@.TK2MSFTNGP10.phx.gbl...
> ok. thanks.
> Hillary,
> are you familiar with the merge repl problems mentioned by Kestutis
> Adomavicius in post titled: 'cannot remove repl... cannot delete
database'?
> If so could you offer some more insight into that and the questions I had
in
> reply to him?
> Thanks... your other posts have helped me out a lot.
>
> "Hilary Cotter" <hilaryk@.att.net> wrote in message
> news:OAIF8ZPZEHA.212@.TK2MSFTNGP11.phx.gbl...
>

Can I avoid slammer attack without install sp3 for sql server 2k

Hi,

I guess there is some problem setting up transactional replication
after i install sp3 for sql server 2k. I get primary key violation in sp_MSget_repl_commands. So I need to revert back to sp2. But what should i do to avoid slammer attack with sp2 installed on my server

Thanks
Nikhil.This is a known issue. You should contact PSS and ask them to look up
SRX030113606317.

Can i apply any conditon on Replication ,before publishing the data?

i want to use Replication in sql server 2000 at specified condtion ,mean if any condition is satisfied then the data is publish to subscriber database?Extracted from BOL:


Filtering Published Data

Filtering data during replication allows you to publish only the data or partitions of data that are needed at the Subscriber. You can filter data to create partitions that include only the columns and/or only the rows that you specify for replication.
With all types of replication, you can choose to copy and distribute complete tables, or data filtered horizontally or vertically with static filters. Merge replication (mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL% 20Server\80\Tools\Books\replsql.chm::/replintro_0odv.htm#sql:merge_replication) is especially strong in filtering options, and you can use dynamic filters to customize the filter based on a property of the Subscriber receiving the data.
Filtering data horizontally allows you to publish only the data that is needed, partition data to different sites, avoid conflicts (because Subscribers will be viewing and updating different subsets of data), and manage publications based on user needs or applications.
Additionally, you have the option of employing user-defined functions in your static and dynamic filters and leveraging the power of customized functions.|||Thanks Tomh,i got the idea what you are trying to say, this can be done from filtering horizontally or vertically option when we Select Yes, I will define data filters, enable anonymous subscriptions, or customize other properties.

i am explaining what i want to do,i want to apply condition let suppose:

if users.usersId between 10 and 20
begin

-- replication perform

end

and u said in ur comments we can also use or apply user defined function can u give me example of it and where should i call this UDF.

thanks in advance :)|||I've never had to use UDFs to filter, so I don't have an example to give you.

I would suggest you do two things:

1. Experiment ... set up a test replication and work thru simple horizontal (your userid example) and vertical filtering (column subset), then when you have that working, try incorporating functions into the filtering.

2. Search the microsoft.public.sqlserver groups for more information, as well as use GOOGLE to search for user written articles to help lead you to your final result.

You will notice that most of the answers on this forum come from members working thru real life situations that have been overcome by testing and finding the many different ways things don't work before finding the one that does.

Join us and be part of the solution!|||I think a horizontally filtered replication (either merge or transactional) would work just fine for you in this situation. It seems that it would be a bit simpler since it's not dynamic (or it does not appear to be from your description).

As tomh suggested, set up a test environment and practice with it. Replication can be tedious and very involved. Learn all you can, test variouse scenarios, document the test conditions and outcomes and develop your plans accordingly.

Regards,

hmscott|||if users.usersId between 10 and 20
begin

-- replication perform

end

if users.usersId between 10 and 20
begin

exec msdb..sp_start_job 'replicationJobName'
end|||Hy guys it works when i did sample example to test data for (merge replication), from row filter and dynamic row filter we can apply conditions (like where clause)

Thnx to all u guys ur guidance very valueable for me thnx to all once again

one thing which would i want to know is that like cfr want to say to write UDF in a specific condition ,i am unable to do like this Guys do u have any idea? where i should call UDF ?

can i add a column to an article in transactionalreplication

hi
how can i add a column to the table at some specified location after publishing the article in transactional replication.is there any procedure or method.please explain
thanks®ards
reddy
Reddy,
sp_repladdcolumn is the standard way to add a column to replication. However
this will not permit you to place the column at a specific position in the
table. This is not normally an issue, as TSQL is not usually written in a
way that makes it order dependant - columns are referred to by name and not
by position. If this is a requirement for you, you'll need to drop the
publication, add the column then recreate the publication.
HTH,
Paul Ibison
|||paul,
thank you.
in transactional replication on subscriber side we have to set autoid property to no.now i am setting this manually.
we have script to set the autoid 'not for replication',like that any script you have to set the autoid property NO for all tables in a replication.please help.
thanks®ards
reddy
|||Reddy,
I haven't used this myself, but Hilary has posted up a
reference to a script he uses:
http://communities2.microsoft.com/co.../newsgroups/en
-us/default.aspx?
dg=microsoft.public.sqlserver.replication&mid=2239 a0b8-
d572-4d86-ac92-5109662bef6b
This updates to Yes - Not for Replication. However, you
could modify this script for your use. You'll still need
to alter the script for each table though.
Also I'd like to know a little more about why the
subscriber columns were originally set to have an identity
attribute and now it's being removed - (it's normally the
opposite :-)).
Regards,
Paul Ibison
|||paul,
first i will explain the way how i am doing transactional replication.first i will change the autoid column of publisher to not for replication.then i will restore the same in subscriber and set the auto id property to no.why means in transactional publis
her it self handles the autoid property so no need in subscriber.
is there any mistake in the above process please reply
thanks & regards
reddy
|||Reddy,
this is correct for a nosync initialization for standard
transactional replication (RO subscribers).
Regards,
Paul Ibison
|||paul
thanks
regards
reddy

Can columns be aliased in Transactional Replication?

I have finally got all the bugs worked out of my TransRep publisher ->
Distributor/Subscriber set up when I got hit with a new question that I can't
answer. I've been asked to alias a few of the columns so that in the
replicated DB they show up with a different header than the columns in the
publisher DB.
IE, Server 1 has column name that says Enroller_ID. Boss wants Server 2 to
say User_ID so that the people reading the data won't get confused. Data in
the column will remain the same and has already been filtered on a row
(horizontal) level. I've also filtered on columns, only sending what is
necessary so the users don't see private information.
But no where I look can I find an "alias" box. BOL mentioned something
about using DTS in replication, but I can't find how to link the two. I know
how to change a column name in DTS (I've become quite the expert in using the
Import / Export Wizard to create packages). But, again, I can't figure out
how to link the two or if this is even necessary.
Help is appreciated. Thank you all in advance. Even if you could tell me
what references to search for in BOL would be a great help.
But doesn't replicating a vew (indexed or otherwise) require the base tables
be published with it?
The replication is getting sent to a customer's server, these people have
sysadmin rights on their machines, and if we send over both the table and the
view, they're probably not even going to look at the view.
"Paul Ibison" wrote:

> I wouldn't use transformable publications for this -
> there are easier ways with less overhead. You could
> replicate an indexed view, or alternatively use an
> @.creation_script.
> HTH,
> Paul Ibison, SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||No - if you use an indexed view, it will appear in a
separate section when you are selecting the articles, and
it doesn't require the underlying table(s). What you are
saying applies to standard views, which of course is
another posibility - you could replicate the table to
another named table and have a view on the subscriber
with the same name as the original table. This would
avoid the overhead on the publisher of maintaining the
indexed view behind the scenes.
HTH,
Paul Ibison, SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||you can modify the replication stored procedures to reflect the new names on
the subscriber.
To do this you will also need to use a pre-creation script containing the
schema of these tables with the new names.
Then you will need to use the delete existing data in the table option in
the snapshot tab of your table article properties dialog box.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Catadmin" <Catadmin@.discussions.microsoft.com> wrote in message
news:D16A826B-586C-4FD6-AFFE-A22AE222A06F@.microsoft.com...
> I have finally got all the bugs worked out of my TransRep publisher ->
> Distributor/Subscriber set up when I got hit with a new question that I
can't
> answer. I've been asked to alias a few of the columns so that in the
> replicated DB they show up with a different header than the columns in the
> publisher DB.
> IE, Server 1 has column name that says Enroller_ID. Boss wants Server 2
to
> say User_ID so that the people reading the data won't get confused. Data
in
> the column will remain the same and has already been filtered on a row
> (horizontal) level. I've also filtered on columns, only sending what is
> necessary so the users don't see private information.
> But no where I look can I find an "alias" box. BOL mentioned something
> about using DTS in replication, but I can't find how to link the two. I
know
> how to change a column name in DTS (I've become quite the expert in using
the
> Import / Export Wizard to create packages). But, again, I can't figure
out
> how to link the two or if this is even necessary.
> Help is appreciated. Thank you all in advance. Even if you could tell me
> what references to search for in BOL would be a great help.
>
|||Well, the system is not letting me say both of you answered my question.
However, you both did (with different ideas) and I greatly appreciate all
your assistance. You just made my life a LOT easier. @.=)
Thank you very much for your time.

can avoid this rollback ??

Hi,
We're testing Transactional replication with updatable subscriber. We have
kept a machine @.branch office which is the publisher and the subscriber is at
the head office. Publisher is win 2003 server with win 2000 sp4 and
subscriber is a cluster with win 2003 and sql 2000 sp3. For small commands
replication is quick but for some specific processes invloving many
transactions, the replication backlog is huge.
We used profiler and found that there is a table whci is deleted completely
during the process and populated again with current data. The table has
almost 80 thousand rows. The profiler shows that sp_MSdel is run for every
row and then sp_MSins is run to insert data. this process takes lot of time
and creates backlog.
After the table got repopulated at subscriber and replication procs started
inserting data in another table, the publisher machine got switched off
accidently by a person at branch. When we started the machine next time, the
second tables at subscriber was empty and the profiler showed that again the
sp_MSdel was being executed at subscriber.
Does this mean that , during the process, if ne of the participating machine
is not available, the whole process will be restarted?
Why it strts with the table it had already replicated?can I stop this?
Ne suggestions?
regards,
have a look at the transaction on the publisher which caused this process.
It is probably a large update statement which is not being done as an update
in place on the subscriber, but rather being decomposed into a series of
delete and then update statements. See if you can identify if this action
was initiated by a stored procedure on the publisher and if so consider
replicating the execution of this stored procedure. You might also want to
look at using the trace flag mentioned in the following kb article which
will force an update in place.
http://support.microsoft.com/kb/302341/EN-US/
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"k_s" <ks@.discussions.microsoft.com> wrote in message
news:40CEB69C-0E43-4A93-A89D-60A9BBFD08DE@.microsoft.com...
> Hi,
> We're testing Transactional replication with updatable subscriber. We have
> kept a machine @.branch office which is the publisher and the subscriber is
> at
> the head office. Publisher is win 2003 server with win 2000 sp4 and
> subscriber is a cluster with win 2003 and sql 2000 sp3. For small commands
> replication is quick but for some specific processes invloving many
> transactions, the replication backlog is huge.
> We used profiler and found that there is a table whci is deleted
> completely
> during the process and populated again with current data. The table has
> almost 80 thousand rows. The profiler shows that sp_MSdel is run for every
> row and then sp_MSins is run to insert data. this process takes lot of
> time
> and creates backlog.
> After the table got repopulated at subscriber and replication procs
> started
> inserting data in another table, the publisher machine got switched off
> accidently by a person at branch. When we started the machine next time,
> the
> second tables at subscriber was empty and the profiler showed that again
> the
> sp_MSdel was being executed at subscriber.
> Does this mean that , during the process, if ne of the participating
> machine
> is not available, the whole process will be restarted?
> Why it strts with the table it had already replicated?can I stop this?
> Ne suggestions?
> regards,
|||Thanks for the prompt reply Hilary.
The process is actually deleting a table completely and then inserting rows
into it. This is done by a stored procedure.
Could u give me ne link to get more idea about the 'replicatin execution of
sp'.
We told the application maintenance guys this issue, anmd asked them to
change the logic to update instead of competely emptying the table.
"Hilary Cotter" wrote:

> have a look at the transaction on the publisher which caused this process.
> It is probably a large update statement which is not being done as an update
> in place on the subscriber, but rather being decomposed into a series of
> delete and then update statements. See if you can identify if this action
> was initiated by a stored procedure on the publisher and if so consider
> replicating the execution of this stored procedure. You might also want to
> look at using the trace flag mentioned in the following kb article which
> will force an update in place.
> http://support.microsoft.com/kb/302341/EN-US/
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "k_s" <ks@.discussions.microsoft.com> wrote in message
> news:40CEB69C-0E43-4A93-A89D-60A9BBFD08DE@.microsoft.com...
>
>
|||Is it possible that you are doing a cascading update of the entire table or
a large portion of it?
Regarding replicating the execution of stored procedures have a look at
Publishing Stored Procedure Execution in BOL.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"k_s" <ks@.discussions.microsoft.com> wrote in message
news:35203F89-5688-497D-B46B-F8D5B85728A1@.microsoft.com...[vbcol=seagreen]
> Thanks for the prompt reply Hilary.
> The process is actually deleting a table completely and then inserting
> rows
> into it. This is done by a stored procedure.
> Could u give me ne link to get more idea about the 'replicatin execution
> of
> sp'.
> We told the application maintenance guys this issue, anmd asked them to
> change the logic to update instead of competely emptying the table.
> "Hilary Cotter" wrote:
|||Couldn't get u. Presently it's not update. It's a history table which
maintens one day history. So everyday, it is cleared and working table's data
is shifted ti it
I saw and tried the sp execution , works well. But in my case it won't fit,
as I have my procedure calling another procedure and BOL says that won't b
supported.
: (
"Hilary Cotter" wrote:

> Is it possible that you are doing a cascading update of the entire table or
> a large portion of it?
> Regarding replicating the execution of stored procedures have a look at
> Publishing Stored Procedure Execution in BOL.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "k_s" <ks@.discussions.microsoft.com> wrote in message
> news:35203F89-5688-497D-B46B-F8D5B85728A1@.microsoft.com...
>
>

Can 2005 mirroring be temporarily 'turned of' programatically?

In 2005 there is a new'ish' kind of replication called Mirroring which
is only available in SQL 2005 sp1. When you create a mirrored database
on another server, the 'Principal Database' stays online, while the
'mirrored database' goes into a constant state of restoring. My
question is can 2005 mirroring be temporarily 'turned of'
programmatically? I have a situation where the developers do a BCP of
a large amount of data to a 'staging server' from their 'online
processor' box. For various reasons, they tell me it is to difficult
to do the bcp to both the staging and reporting server. The reporting
server is for Business Objects reporting which affords very little
control over the size and scope of the queries submitted. As a result,
Disk I/O on the reporting server during the day can be extreme. I was
considering creating a mirror between the Staging and Reporting server
database and just allow the BCP to continue on the Staging Server.
Then, after the mirror is completely synchronized, I would turn off the
'mirroring' and use the mirror for reporting. Then, near the end of
the day, I would re-create the link between the Principal and the
Mirror and let the BCP process start again.
How feasible is this?I don't think that you want to create a mirror between staging and
reporting.
As you mentioned, when you are using Mirroring the mirror is unavailable.
How are you going to report from your reporting server if Business Objects
cannot connect to the Mirror server.
You might want to look at log shipping, replication, DTS (SSIS), or a simple
backup and restore. These might be more suitable for your purposes.
--
Keith Kratochvil
"togbabe" <togbabe@.yahoo.co.uk> wrote in message
news:1154931847.145022.63740@.p79g2000cwp.googlegroups.com...
> In 2005 there is a new'ish' kind of replication called Mirroring which
> is only available in SQL 2005 sp1. When you create a mirrored database
> on another server, the 'Principal Database' stays online, while the
> 'mirrored database' goes into a constant state of restoring. My
> question is can 2005 mirroring be temporarily 'turned of'
> programmatically? I have a situation where the developers do a BCP of
> a large amount of data to a 'staging server' from their 'online
> processor' box. For various reasons, they tell me it is to difficult
> to do the bcp to both the staging and reporting server. The reporting
> server is for Business Objects reporting which affords very little
> control over the size and scope of the queries submitted. As a result,
> Disk I/O on the reporting server during the day can be extreme. I was
> considering creating a mirror between the Staging and Reporting server
> database and just allow the BCP to continue on the Staging Server.
> Then, after the mirror is completely synchronized, I would turn off the
> 'mirroring' and use the mirror for reporting. Then, near the end of
> the day, I would re-create the link between the Principal and the
> Mirror and let the BCP process start again.
> How feasible is this?
>|||Hi
There is a section on pausing and resuming database mirroring in Books
Online also see http://msdn2.microsoft.com/en-us/library/ms175539.aspx You
should also
John
"togbabe" wrote:
> In 2005 there is a new'ish' kind of replication called Mirroring which
> is only available in SQL 2005 sp1. When you create a mirrored database
> on another server, the 'Principal Database' stays online, while the
> 'mirrored database' goes into a constant state of restoring. My
> question is can 2005 mirroring be temporarily 'turned of'
> programmatically? I have a situation where the developers do a BCP of
> a large amount of data to a 'staging server' from their 'online
> processor' box. For various reasons, they tell me it is to difficult
> to do the bcp to both the staging and reporting server. The reporting
> server is for Business Objects reporting which affords very little
> control over the size and scope of the queries submitted. As a result,
> Disk I/O on the reporting server during the day can be extreme. I was
> considering creating a mirror between the Staging and Reporting server
> database and just allow the BCP to continue on the Staging Server.
> Then, after the mirror is completely synchronized, I would turn off the
> 'mirroring' and use the mirror for reporting. Then, near the end of
> the day, I would re-create the link between the Principal and the
> Mirror and let the BCP process start again.
> How feasible is this?
>|||"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:uR%237WbiuGHA.4336@.TK2MSFTNGP06.phx.gbl...
> I don't think that you want to create a mirror between staging and
> reporting.
> As you mentioned, when you are using Mirroring the mirror is unavailable.
> How are you going to report from your reporting server if Business Objects
> cannot connect to the Mirror server.
> You might want to look at log shipping, replication, DTS (SSIS), or a
simple
> backup and restore. These might be more suitable for your purposes.
I second Keith's recommendation of a simple backup & restore. It works well
and it is too basic to break often.
Jonathan|||I might have found a solution to this guys. What I did was set up
'2005 mirroring' between the two servers. Then I can programmatically
take a 'snapshot' of the 'perpetually restoring mirror' and report from
the snapshot. At the end of the 'reporting day,' I can delete the
snapshot. This appears to work really well.
Jonathan Roberts wrote:
> "Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
> news:uR%237WbiuGHA.4336@.TK2MSFTNGP06.phx.gbl...
> > I don't think that you want to create a mirror between staging and
> > reporting.
> > As you mentioned, when you are using Mirroring the mirror is unavailable.
> > How are you going to report from your reporting server if Business Objects
> > cannot connect to the Mirror server.
> >
> > You might want to look at log shipping, replication, DTS (SSIS), or a
> simple
> > backup and restore. These might be more suitable for your purposes.
> I second Keith's recommendation of a simple backup & restore. It works well
> and it is too basic to break often.
> Jonathan

Can 2005 mirroring be temporarily 'turned of' programatically?

In 2005 there is a new'ish' kind of replication called Mirroring which
is only available in SQL 2005 sp1. When you create a mirrored database
on another server, the 'Principal Database' stays online, while the
'mirrored database' goes into a constant state of restoring. My
question is can 2005 mirroring be temporarily 'turned of'
programmatically? I have a situation where the developers do a BCP of
a large amount of data to a 'staging server' from their 'online
processor' box. For various reasons, they tell me it is to difficult
to do the bcp to both the staging and reporting server. The reporting
server is for Business Objects reporting which affords very little
control over the size and scope of the queries submitted. As a result,
Disk I/O on the reporting server during the day can be extreme. I was
considering creating a mirror between the Staging and Reporting server
database and just allow the BCP to continue on the Staging Server.
Then, after the mirror is completely synchronized, I would turn off the
'mirroring' and use the mirror for reporting. Then, near the end of
the day, I would re-create the link between the Principal and the
Mirror and let the BCP process start again.
How feasible is this?I don't think that you want to create a mirror between staging and
reporting.
As you mentioned, when you are using Mirroring the mirror is unavailable.
How are you going to report from your reporting server if Business Objects
cannot connect to the Mirror server.
You might want to look at log shipping, replication, DTS (SSIS), or a simple
backup and restore. These might be more suitable for your purposes.
Keith Kratochvil
"togbabe" <togbabe@.yahoo.co.uk> wrote in message
news:1154931847.145022.63740@.p79g2000cwp.googlegroups.com...
> In 2005 there is a new'ish' kind of replication called Mirroring which
> is only available in SQL 2005 sp1. When you create a mirrored database
> on another server, the 'Principal Database' stays online, while the
> 'mirrored database' goes into a constant state of restoring. My
> question is can 2005 mirroring be temporarily 'turned of'
> programmatically? I have a situation where the developers do a BCP of
> a large amount of data to a 'staging server' from their 'online
> processor' box. For various reasons, they tell me it is to difficult
> to do the bcp to both the staging and reporting server. The reporting
> server is for Business Objects reporting which affords very little
> control over the size and scope of the queries submitted. As a result,
> Disk I/O on the reporting server during the day can be extreme. I was
> considering creating a mirror between the Staging and Reporting server
> database and just allow the BCP to continue on the Staging Server.
> Then, after the mirror is completely synchronized, I would turn off the
> 'mirroring' and use the mirror for reporting. Then, near the end of
> the day, I would re-create the link between the Principal and the
> Mirror and let the BCP process start again.
> How feasible is this?
>|||Hi
There is a section on pausing and resuming database mirroring in Books
Online also see http://msdn2.microsoft.com/en-us/library/ms175539.aspx You
should also
John
"togbabe" wrote:

> In 2005 there is a new'ish' kind of replication called Mirroring which
> is only available in SQL 2005 sp1. When you create a mirrored database
> on another server, the 'Principal Database' stays online, while the
> 'mirrored database' goes into a constant state of restoring. My
> question is can 2005 mirroring be temporarily 'turned of'
> programmatically? I have a situation where the developers do a BCP of
> a large amount of data to a 'staging server' from their 'online
> processor' box. For various reasons, they tell me it is to difficult
> to do the bcp to both the staging and reporting server. The reporting
> server is for Business Objects reporting which affords very little
> control over the size and scope of the queries submitted. As a result,
> Disk I/O on the reporting server during the day can be extreme. I was
> considering creating a mirror between the Staging and Reporting server
> database and just allow the BCP to continue on the Staging Server.
> Then, after the mirror is completely synchronized, I would turn off the
> 'mirroring' and use the mirror for reporting. Then, near the end of
> the day, I would re-create the link between the Principal and the
> Mirror and let the BCP process start again.
> How feasible is this?
>|||"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:uR%237WbiuGHA.4336@.TK2MSFTNGP06.phx.gbl...
> I don't think that you want to create a mirror between staging and
> reporting.
> As you mentioned, when you are using Mirroring the mirror is unavailable.
> How are you going to report from your reporting server if Business Objects
> cannot connect to the Mirror server.
> You might want to look at log shipping, replication, DTS (SSIS), or a
simple
> backup and restore. These might be more suitable for your purposes.
I second Keith's recommendation of a simple backup & restore. It works well
and it is too basic to break often.
Jonathan|||I might have found a solution to this guys. What I did was set up
'2005 mirroring' between the two servers. Then I can programmatically
take a 'snapshot' of the 'perpetually restoring mirror' and report from
the snapshot. At the end of the 'reporting day,' I can delete the
snapshot. This appears to work really well.
Jonathan Roberts wrote:
> "Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
> news:uR%237WbiuGHA.4336@.TK2MSFTNGP06.phx.gbl...
> simple
> I second Keith's recommendation of a simple backup & restore. It works we
ll
> and it is too basic to break often.
> Jonathan