Link Table

Ronia,
this could be achieved using replication (and triggers,
linked servers, DTS...). It largely depends on latency -
the delay between the change made on A and it appearing
on B, and whether the data in B is updatable. If you can
give me a bit more info on these matters, then we'll be
in a better position to advise.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Hi Paul,
Data won't be update by table B. Update will only done via Table A.
so it is only a read only .
There's a replication mode which fit with this scen.
But i am not familiar with replication. and i will ask me not to use system
account with it, I don't know how to do it.
Thanks.
Ronia.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> ?
news:8aae01c4d17a$1828d140$a601280a@.phx.gbl ?...
> Ronia,
> this could be achieved using replication (and triggers,
> linked servers, DTS...). It largely depends on latency -
> the delay between the change made on A and it appearing
> on B, and whether the data in B is updatable. If you can
> give me a bit more info on these matters, then we'll be
> in a better position to advise.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Transactional replication would be best for your
scenario. Replication uses agents which are essentially
jobs run by the sql server agent. For the security
requirements have a look in books on line
for 'replication, security', 'Role Requirements'.
To run replication you won't need the sysadmin account
but to set it up, configure it and monitor it, you will.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Hi Ronia,
Why dont you create a view in your database "B"?
Use B
Go
Create view T
As
Select * From A.dbo.T
Go
This will let you read the data from the database A, table T.
Sriram
"Ronia" <Ronia@.mail.com> wrote in message news:<OaGb5tj0EHA.480@.TK2MSFTNGP10.phx.gbl>...
> Hi Paul,
> Data won't be update by table B. Update will only done via Table A.
> so it is only a read only .
> There's a replication mode which fit with this scen.
> But i am not familiar with replication. and i will ask me not to use system
> account with it, I don't know how to do it.
> Thanks.
> Ronia.
|||hi Sriram,
Will there be any preformance issue.. when doing this? However is they
are located within the same server is that performance will not be an
issue?...
compare with Transactional replication which will be better?
Thanks
Ronia
"Sriram" <srivish@.gmail.com> ?
news:25274ca3.0411241354.5e914fc2@.posting.google.c om ?...
> Hi Ronia,
> Why dont you create a view in your database "B"?
> Use B
> Go
> Create view T
> As
> Select * From A.dbo.T
> Go
> This will let you read the data from the database A, table T.
> Sriram
>
> "Ronia" <Ronia@.mail.com> wrote in message
news:<OaGb5tj0EHA.480@.TK2MSFTNGP10.phx.gbl>...[vbcol=seagreen]
system[vbcol=seagreen]
|||It depends...
If you require a copy of the data, so eg you can do
reports on it, while not affecting - blocking - users who
are making updates on the main table, then replication
would be suitable. It can also be used to effectively
load balance database access if the databases exist on
different drives. Also, so people use snapshot
replication to have a copy of a table that users can play
around with and modify the data indiscriminantly, knowing
that all the changes will later be overwritten.
So, what is your requirement for a copy of the data? If
it doesn't fit into one of these categories, then a cross-
database view may indeed be the solution.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Limiting log growth during DTS Package

SQL Server 2000 SP4. I built a large DTS package that grabs a number
of tables from an Oracle DB, does some scrubbing and date verification
and loads to a SQL Server DB. Most of the tables are full refresh and
a few are incremental.

Main DW: DwSQL
Staging Area: DwLoadAreaSQL

The DW is about 60 Gigs. The Staging Area is about 80 Gigs. This is
all good.

However, the log file for the staging area is 50 Gigs and I'm trying
to find ways to not require such a large log file. I tried adding a
few "BACKUP LOG DwLoadAreaSQL WITH TRUNCATE_ONLY" statements in the
DTS package but figured out that because it's 1 DTS package it's all 1
transaction. I've thought about breaking it up into multiple DTS
packages and truncating the log between running them but was hoping to
avoid this. To be clear, I know how to shrink DB's and Log
Files...that's not the issue.

Any Ideas? Thanks.On Jul 24, 3:49 pm, davisutt <davis...@.aol.comwrote:

Quote:

Originally Posted by

SQL Server 2000 SP4. I built a large DTS package that grabs a number
of tables from an Oracle DB, does some scrubbing and date verification
and loads to a SQL Server DB. Most of the tables are full refresh and
a few are incremental.
>
Main DW: DwSQL
Staging Area: DwLoadAreaSQL
>
The DW is about 60 Gigs. The Staging Area is about 80 Gigs. This is
all good.
>
However, the log file for the staging area is 50 Gigs and I'm trying
to find ways to not require such a large log file. I tried adding a
few "BACKUP LOG DwLoadAreaSQL WITH TRUNCATE_ONLY" statements in the
DTS package but figured out that because it's 1 DTS package it's all 1
transaction. I've thought about breaking it up into multiple DTS
packages and truncating the log between running them but was hoping to
avoid this. To be clear, I know how to shrink DB's and Log
Files...that's not the issue.
>
Any Ideas? Thanks.

Make sure both databases are in bulked log recovery mode. You should
have a step at the end of your DTS to run CHECKPOINT, backup truncate
the log. Also, manaually shrink the log file to your desired log size.

Hope it helps...

MNDBA|||On Jul 28, 4:55 pm, kmounkh...@.gmail.com wrote:

Quote:

Originally Posted by

On Jul 24, 3:49 pm, davisutt <davis...@.aol.comwrote:
>
>
>
>
>

Quote:

Originally Posted by

SQL Server 2000 SP4. I built a large DTS package that grabs a number
of tables from an Oracle DB, does some scrubbing and date verification
and loads to a SQL Server DB. Most of the tables are full refresh and
a few are incremental.

>

Quote:

Originally Posted by

Main DW: DwSQL
Staging Area: DwLoadAreaSQL

>

Quote:

Originally Posted by

The DW is about 60 Gigs. The Staging Area is about 80 Gigs. This is
all good.

>

Quote:

Originally Posted by

However, the log file for the staging area is 50 Gigs and I'm trying
to find ways to not require such a large log file. I tried adding a
few "BACKUP LOG DwLoadAreaSQL WITH TRUNCATE_ONLY" statements in the
DTS package but figured out that because it's 1 DTS package it's all 1
transaction. I've thought about breaking it up into multiple DTS
packages and truncating the log between running them but was hoping to
avoid this. To be clear, I know how to shrink DB's and Log
Files...that's not the issue.

>

Quote:

Originally Posted by

Any Ideas? Thanks.

>
Make sure both databases are in bulked log recovery mode. You should
have a step at the end of your DTS to run CHECKPOINT, backup truncate
the log. Also, manaually shrink the log file to your desired log size.
>
Hope it helps...
>
MNDBA- Hide quoted text -
>
- Show quoted text -

Thanks. I think that's what I was looking for. I changed the
recovery mode and shrunk the log size to about 20% of what it was.
I'll run the process and see what kind of growth occurs.