Help on transactional replication

Hi,
I am a newbie dba and relatively new to replication area.
I am in a process of setting up a transactional replication on sql 2000
for a reporting purpose to offload this activity on the production
server.
My basic goal is to enhance the performance on the production server. I
thought replicating to a reporting server would be an ideal choice
using transactional relication scheduled on an hourly basis.
following is the setup i am trying to achieve...
I am using Production server which is more like an OLTP server as a
publisher, Distributor is going to be on the same server as of
subscriber because of less resources. Moerever I was suggested that
running the Distributor & Pulling the subscription would result in
better performance for both the servers.
Now my questions are...
1. Am i going in a write direction to achieve my task?
2. what are the precautions one should take before making the
"production server" as publisher is transactional relication?
3. Do i need to increase the size of db's and logs on production
server?
4. How should we know that replication is causing stress on production
server?
5. what is the ideal size of database and the transaction log that is
going to hold the replicated data?
6. How to remove replcation from a database completely in case if it
fails and causing issues on productions?
Thanks very much all. I will really appretiate your tips and
suggestions.
AK
Firstly, the method you are following is fairly commonly used. The size of
the logs and databases depends entirely on your initial data size and
transaction volume, so as long as you have the logs set to a large size or
autogrow, you'll just have to monitor them for your circumstances. The
impact of replication on your system can be monitored using the normal
counters in windows system monitor (processor usage, ram usage and disk
usage - if you are not familiar with these I can dig out some links). To
prevent the use of the replication overhead in the event of a slowdown, you
could initially stop the log-reader agent then remove the publication.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Thanks for the reply paul,
I have left the database/log size to autogrow and will keep an eye on
their size.
also, you haven't answered clearly whether am i doing a right thing by
keeping the distributor on a subscriber server instead of keeping it
with publisher in production server?
Could you also please indicate what sort of implication the production
server will have with this kind of setup? I have to make it clear to my
manager before i setup this thing on live environment.
And the basic question, I would think the initial snapshot should be
done out of business hours? If yes, how often i need to take the
snapshot or just reading the transaction logs would be enough as long
as there are no schema changes on the published tables?
Will the snapshot agent gets the entire shcema and data from the
published tables every time it runs or only at once in the begining in
transactional replication?
and the last thing, If you please don't mind can you show me some links
on how to read and understand processor and memory usage things.
Thanks very much
AK.
Paul Ibison wrote:
> Firstly, the method you are following is fairly commonly used. The size of
> the logs and databases depends entirely on your initial data size and
> transaction volume, so as long as you have the logs set to a large size or
> autogrow, you'll just have to monitor them for your circumstances. The
> impact of replication on your system can be monitored using the normal
> counters in windows system monitor (processor usage, ram usage and disk
> usage - if you are not familiar with these I can dig out some links). To
> prevent the use of the replication overhead in the event of a slowdown, you
> could initially stop the log-reader agent then remove the publication.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||For a 1:1 system, putting the distributor on the subscriber box is a valid
choice. I prefer to have the distributor on the production box so i have
control over the backups centrally, and will therefore take the extra hit
there. Pull distribution agents or push will run on the subscriber in your
case. You could have the distributor on the production box and use a pull
subscriber to have the agent still run on the subscriber's box. However
these are not equivalent, and there will be a lot of disk read/write access
to the distribution database which generally contends with the production
database as it resides on the same disk array - hence this is why your
choice makes sense.
For the implications of the log-reader reading the transaction log and
writing the transactions to the distribution database - only you can
determine this for your setup. Best practices are to set up this in a lab
environment mimicing the production site.
Only run the snapshot agent ONCE and then disable it. You'll need to run it
manually for adding new articles and in the case of reinitialization
(hopefully never ).
For links, I'll dig some out for you...
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Paul,
Thanks very much for your valuable tips. I really appreciate your time
on this.
Now one last thing i want to ask, once i have disabled the snapshot
agents, then how the log reader agent will read whenever there are any
transactions happend on publisher?
I want toschedule the job to read the transactions every hour. Shall i
schedule the log reader agent or the distribution agent to achieve this
task?
Many Thanks.
AK
Paul Ibison wrote:
> For a 1:1 system, putting the distributor on the subscriber box is a valid
> choice. I prefer to have the distributor on the production box so i have
> control over the backups centrally, and will therefore take the extra hit
> there. Pull distribution agents or push will run on the subscriber in your
> case. You could have the distributor on the production box and use a pull
> subscriber to have the agent still run on the subscriber's box. However
> these are not equivalent, and there will be a lot of disk read/write access
> to the distribution database which generally contends with the production
> database as it resides on the same disk array - hence this is why your
> choice makes sense.
> For the implications of the log-reader reading the transaction log and
> writing the transactions to the distribution database - only you can
> determine this for your setup. Best practices are to set up this in a lab
> environment mimicing the production site.
> Only run the snapshot agent ONCE and then disable it. You'll need to run it
> manually for adding new articles and in the case of reinitialization
> (hopefully never ).
> For links, I'll dig some out for you...
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||The log reader should run continuously and the distribution agent can be set
to run hourly.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Hello Paul,
Thanks very much for your help. I have successfully tested this setup
in test environment.It was spot on. I am hoping to implement this on
live very soon.
Thanks,
AK
Paul Ibison wrote:

> The log reader should run continuously and the distribution agent can be set
> to run hourly.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .

Help newbie with quering XML

Hello,
I have XML data which I receive from a vendor and store in a table
with the XML datatype in this arrangement:
<MYXML>
<ITEMS>
<ITEM name="item1" value="widget" />
<ITEM name="item2" value="dongle" />
<ITEM name="item3" value="thingy" />
</ITEMS>
</MYXML>
How do I query for item1 and return just, widget ?
TIA,
RichHi,
How about something like this
DECLARE @.x XML
SET @.x =
'<MYXML>
<ITEMS>
<ITEM name="item1" value="widget" />
<ITEM name="item2" value="dongle" />
<ITEM name="item3" value="thingy" />
</ITEMS>
</MYXML>'
SELECT @.x.value('(//ITEM[@.name="item1"]/@.value)[1]','nvarchar(MAX)')
If you want to parameterize the value for the name attribute, you can
use sql:variable() and do this
DECLARE @.x XML
SET @.x =
'<MYXML>
<ITEMS>
<ITEM name="item1" value="widget" />
<ITEM name="item2" value="dongle" />
<ITEM name="item3" value="thingy" />
</ITEMS>
</MYXML>'
DECLARE @.n nvarchar(MAX)
SET @.n = 'item1'
SELECT
@.x.value('(//ITEM[@.name=sql:variable("@.n")]/@.value)[1]','nvarchar(MAX)')
I hope this helps
Denis Ruckebusch
XML datatype test team
--
This posting is provided "AS IS" with no warranties, and confers no
rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"sk8man31" <me@.aol.com> wrote in message
news:rc71825rqgo28vr6dr18beb0bn1jv148qe@.
4ax.com...
> Hello,
> I have XML data which I receive from a vendor and store in a table
> with the XML datatype in this arrangement:
> <MYXML>
> <ITEMS>
> <ITEM name="item1" value="widget" />
> <ITEM name="item2" value="dongle" />
> <ITEM name="item3" value="thingy" />
> </ITEMS>
> </MYXML>
> How do I query for item1 and return just, widget ?
> TIA,
> Rich|||On Fri, 2 Jun 2006 18:18:53 -0700, "Denis Ruckebusch [MSFT]"
<denisruc@.online.microsoft.com> wrote:

>Hi,
> How about something like this
>DECLARE @.x XML
>SET @.x =
>'<MYXML>
><ITEMS>
><ITEM name="item1" value="widget" />
><ITEM name="item2" value="dongle" />
><ITEM name="item3" value="thingy" />
></ITEMS>
></MYXML>'
>SELECT @.x.value('(//ITEM[@.name="item1"]/@.value)[1]','nvarchar(MAX)')
>
>If you want to parameterize the value for the name attribute, you can
>use sql:variable() and do this
>DECLARE @.x XML
>SET @.x =
>'<MYXML>
><ITEMS>
><ITEM name="item1" value="widget" />
><ITEM name="item2" value="dongle" />
><ITEM name="item3" value="thingy" />
></ITEMS>
></MYXML>'
>DECLARE @.n nvarchar(MAX)
>SET @.n = 'item1'
>SELECT
>@.x.value('(//ITEM[@.name=sql:variable("@.n")]/@.value)[1]','nvarchar(MAX)')
>
>I hope this helps
>Denis Ruckebusch
>XML datatype test team
Denis,
Thanks for your help, that worked great!
~Rich

Help needed?

Hi

I'm a newbie to SQL server..... l'm building a website where companies can save important data. I have a SQL server available but I'm not sure how to store the data. Should I create a new database for every user or should I store everything in the same database and then use a UserId to recognize the data and the user?

The data stored for each user are stored in tables which are exactly the same so all tables could be gathered into one table and then a UserId could tell which records belong to whom.

Hope my english isn't too bad..otherwise just ask me questions and I'll get back A.S.A.P.

Regards

Joachim

1 database will support many users. As you suggested, you would create a user table and each user would have an entry there. The primary key of that table (e.g. : UserId) would be used as a Foreign Key in other tables to identify which user owns the data. e.g. : an orders table would have a userId column to specify which user the order belonged to.

Hope this helps.

|||

hi joachim,

thats not a bad english at all.

regarding your question i suggest you take your time

studying the basic database concept such

normalization, entity integrity, domain integrity, relational integrity and bussiness integrity.

its not easy at first but it will help very much in the long run of your database career

regards

joey

Help needed about databases!

Hi

I'm a newbie to SQL server.... l'm building a website where companies can save important data. I have a SQL server available but I'm not sure how to store the data. Should I create a new database for every user or should I store everything in the same database and then use a UserId to recognize the data and the user? What about the case where I reaches let's say 1000 users in the one user per database case, it would be extremly difficult to have an overview of the databases or what?

The data stored for each user are stored in tables which are exactly the same so all tables could be gathered into one table and then a UserId could tell which records belong to whom.

Hope my english isn't too bad..otherwise just ask me questions and I'll get back A.S.A.P.

Regards

Joachim

You probably should just use a single database. Unless there are overriding issues like you distribute the database to the company. Then you wouldn't want one company to get another companies data. I'm assuming there is a single application where all the companies log in to the same site. If each company has separate sites, then you might actually need separate apps (and databases) for each.

Single app / db is easier to maintain but realize that changes for one then apply to all.

HTH.

Help Moving a Database, Associated Logins and Objects

Hello. Newbie here....
I am attempting to move a database, logins, objects from 7 to 2000.
I've tried the copy database wizard, but it gives me a "failed to
create the share OMWWIZE" error.
I've search the MSDN, but the only solution says there are problems
with permissions. I've logged in as SA on both boxes...
Please help, I'm new to this and confused.A likely cause of the error is that the MSSQLServer service account does not
have permissions to the share. However, it's a fairly simple task to do
this manually using the following steps:
1) make note of the existing file locations:
EXEC sp_helpdb 'MyDatabase'
2) detach database
EXEC sp_detach_db 'MyDatabase'
3) copy database files to new location
4) attach database files from new location:
EXEC sp_attach_db 'MyDatabase',
'E:\MyDbDataFiles\MyDatabase.mdf',
'F:\MyDbLogFiles\MyDatabase_Log.ldf'
See http://support.microsoft.com/support/kb/articles/Q224/0/71.ASP for more
information.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"JD" <whatchoogot@.hotmail.com> wrote in message
news:b809817a.0311260547.44aeaefe@.posting.google.com...
> Hello. Newbie here....
> I am attempting to move a database, logins, objects from 7 to 2000.
> I've tried the copy database wizard, but it gives me a "failed to
> create the share OMWWIZE" error.
> I've search the MSDN, but the only solution says there are problems
> with permissions. I've logged in as SA on both boxes...
> Please help, I'm new to this and confused.