Is replication the answer?

Hi, I've never done replication before, so I don't know if this is what I
need. We have a server here (S1) and another server out of state (S2).
We have our ERP system/database in S2. We have database in S1 which has
views linked to the ERP database in S2. We use Access to generate reports an
d
the source of the reports are Access queries that query the views of the
database in S1 which linked to database in S2.
Before, we had both server at the same location so generating reports are
not a problem. But, since we moved S2 to out of state, users always get
time-out when running the report. I think it's because we need to run Access
queries based on the views in S1 which pull the data based on the views in
S2.
So, I was thinking, how about making a replication of the S2 database and
place it in S1? The database size is about 45 GB. Does anybody know if this
will solve the time-out problem? what steps should I take? How do I update i
t
if I decide to make the replication?
Thanks in advance for any suggestion. Sorry if my explanation is confusing.
LisaHi
Your solution may depend on how up-to-date you require the second database
to be. If a 24 hour latency is ok, then you could do an automated
backup/restore process that will update the database overnight or during a
offline period, very similar to log shipping. Replication is also an option
especially if you only need a subset of the data from the primary database.
With SQL 2005 you also have the option of database mirroring combined with a
database snapshot.
HTH
John
"lwidjaya" wrote:

> Hi, I've never done replication before, so I don't know if this is what I
> need. We have a server here (S1) and another server out of state (S2).
> We have our ERP system/database in S2. We have database in S1 which has
> views linked to the ERP database in S2. We use Access to generate reports
and
> the source of the reports are Access queries that query the views of the
> database in S1 which linked to database in S2.
> Before, we had both server at the same location so generating reports are
> not a problem. But, since we moved S2 to out of state, users always get
> time-out when running the report. I think it's because we need to run Acce
ss
> queries based on the views in S1 which pull the data based on the views in
> S2.
> So, I was thinking, how about making a replication of the S2 database and
> place it in S1? The database size is about 45 GB. Does anybody know if thi
s
> will solve the time-out problem? what steps should I take? How do I update
it
> if I decide to make the replication?
> Thanks in advance for any suggestion. Sorry if my explanation is confusing
.
> Lisa|||Hi John, thanks for your reply.
We have SQL 2000 on both servers. We have more than 2000 tables in the
remote server. I think there are only around 500 tables that are used.
If we chose backup/restore, how does it work? do we backup the remote server
database and restore it in the local server? how about the existing databse
in the local server? Should we delete it first?
Lisa
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> Your solution may depend on how up-to-date you require the second database
> to be. If a 24 hour latency is ok, then you could do an automated
> backup/restore process that will update the database overnight or during a
> offline period, very similar to log shipping. Replication is also an optio
n
> especially if you only need a subset of the data from the primary database
.
> With SQL 2005 you also have the option of database mirroring combined with
a
> database snapshot.
> HTH
> John
> "lwidjaya" wrote:
>|||Hi Lisa
The back/restore process would backup the database on the remote server
(which you may already be doing!) and then the restore will be over the
existing database on the second server. You do not need to drop the database
on the second server first. You can backup/restore to a network drive (UNC
path), but you may find it better to copy the backup to the second server an
d
restore from there.
Depending on the size of your database you may want to keep it simple and
always do a full backup, although you could use differential backups and onl
y
periodically restore a full backup.
After restoring the backup you may also want to add additional indexes to
the restored database to make the queries faster, you may also want to make
this database read-only.
HTH
John
"lwidjaya" wrote:
[vbcol=seagreen]
> Hi John, thanks for your reply.
> We have SQL 2000 on both servers. We have more than 2000 tables in the
> remote server. I think there are only around 500 tables that are used.
> If we chose backup/restore, how does it work? do we backup the remote serv
er
> database and restore it in the local server? how about the existing databs
e
> in the local server? Should we delete it first?
> Lisa
> "John Bell" wrote:
>|||Hi John,
thanks for your reply.
When you talked about backup/restore process, is that the process from the
SQL server or can I use the veritas backup exec?
The database size is approx. 45 GB, do you think it's gonna be a problem?
Thanks,
Lisa
"John Bell" wrote:
[vbcol=seagreen]
> Hi Lisa
> The back/restore process would backup the database on the remote server
> (which you may already be doing!) and then the restore will be over the
> existing database on the second server. You do not need to drop the databa
se
> on the second server first. You can backup/restore to a network drive (UNC
> path), but you may find it better to copy the backup to the second server
and
> restore from there.
> Depending on the size of your database you may want to keep it simple and
> always do a full backup, although you could use differential backups and o
nly
> periodically restore a full backup.
> After restoring the backup you may also want to add additional indexes to
> the restored database to make the queries faster, you may also want to mak
e
> this database read-only.
> HTH
> John
>
> "lwidjaya" wrote:
>|||Hi Lisa
45GB is a bit too large for doing a full backup every night and restoring
it. How are you connected to the remote database and have you investigate th
e
performance of the connection and also the remote database (in general)?
The backup would be to disc using the T-SQL command BACKUP.
John
"lwidjaya" wrote:
[vbcol=seagreen]
> Hi John,
> thanks for your reply.
> When you talked about backup/restore process, is that the process from the
> SQL server or can I use the veritas backup exec?
> The database size is approx. 45 GB, do you think it's gonna be a problem?
> Thanks,
> Lisa
> "John Bell" wrote:
>|||Hi John,
we just moved the database to out of state 2 weeks ago. After the move, I
linked the database/server to our local server here. Since then almost all
users here complain about the speed and the time out. When the database was
here, the database performance was fine.
Thanks,
Lisa
"John Bell" wrote:
[vbcol=seagreen]
> Hi Lisa
> 45GB is a bit too large for doing a full backup every night and restoring
> it. How are you connected to the remote database and have you investigate
the
> performance of the connection and also the remote database (in general)?
> The backup would be to disc using the T-SQL command BACKUP.
> John
>
> "lwidjaya" wrote:
>|||Hi Lisa
You should see the network guys and ask then what the performance should be,
i.e. if you have a very thin pipe between the sites or if there is some type
of throttling. Also make sure that the network is configured correctly
(tracetrt) and that you are not going around all the houses, you may need to
get a decicated vlan between the servers or some other network improvement.
If you can remote desktop into the other server it may give you some
indication of how the network is performing.
John
"lwidjaya" wrote:
[vbcol=seagreen]
> Hi John,
> we just moved the database to out of state 2 weeks ago. After the move, I
> linked the database/server to our local server here. Since then almost all
> users here complain about the speed and the time out. When the database wa
s
> here, the database performance was fine.
> Thanks,
> Lisa
> "John Bell" wrote:
>|||Hi Lisa
Database mirroring is available in SQL 2005, it is sometimes descripted as
real time log shipping and can be used as a failover copy of your database.
You can use a database snapshot of the mirror to get a read-only copy of the
database.
John
"lwidjaya" wrote:
[vbcol=seagreen]
> Hi John,
> I did a research on replication, and I found mirroring. Is it the same thi
ng?
> Lisa
> "John Bell" wrote:
>|||Hi John,
I did a research on replication, and I found mirroring. Is it the same thing
?
Lisa
"John Bell" wrote:
[vbcol=seagreen]
> Hi Lisa
> You should see the network guys and ask then what the performance should b
e,
> i.e. if you have a very thin pipe between the sites or if there is some ty
pe
> of throttling. Also make sure that the network is configured correctly
> (tracetrt) and that you are not going around all the houses, you may need
to
> get a decicated vlan between the servers or some other network improvement
.
> If you can remote desktop into the other server it may give you some
> indication of how the network is performing.
> John
> "lwidjaya" wrote:
>