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 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 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 it
if I decide to make the replication?
Thanks in advance for any suggestion. Sorry if my explanation is confusing.
Lisa
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 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 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 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 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 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 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 only
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 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:
|||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 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 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 only
> 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:
|||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:
[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 was
> 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 thing?
> 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 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:

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:
>

Is possible?

I have an vb4 application (i know it's old, but it's not my fault), and i
need to change the used database (sybase) to sql (sql 2000). They are using
VBSQL.VBX to access the sybase database, it's possible to use the sql 2000
database with this .vbx i can't log in.
Hi
I am not familiar with sybase and this vbx! But with SQL 2000 you audit the
logins with profiler so you can see how it is trying to connect. I would
assume that you have the configured/changed the servername and also any
other setting that are require such as a DSN.
John
"Logan" <Logan@.discussions.microsoft.com> wrote in message
news:55932181-6449-4520-8EBA-8C2D213A4206@.microsoft.com...
>I have an vb4 application (i know it's old, but it's not my fault), and i
> need to change the used database (sybase) to sql (sql 2000). They are
> using
> VBSQL.VBX to access the sybase database, it's possible to use the sql 2000
> database with this .vbx i can't log in.
|||This vbx receive a Server, BD, User and Pass it's supposed to return a value
different from 0 when log in, but it's returning a 0.
In advance, thank you for your help.
"John Bell" wrote:

> Hi
> I am not familiar with sybase and this vbx! But with SQL 2000 you audit the
> logins with profiler so you can see how it is trying to connect. I would
> assume that you have the configured/changed the servername and also any
> other setting that are require such as a DSN.
> John
> "Logan" <Logan@.discussions.microsoft.com> wrote in message
> news:55932181-6449-4520-8EBA-8C2D213A4206@.microsoft.com...
>
>
|||VBSQL.VBX is soo old. It uses the old DbLibrary API... Anyhow, does the SQL Server allow SQL Server
logins? Can you login from QA on the same machine using the same credentials?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Logan" <Logan@.discussions.microsoft.com> wrote in message
news:653CEA89-C956-4A43-A252-A96AA9339E3F@.microsoft.com...[vbcol=seagreen]
> This vbx receive a Server, BD, User and Pass it's supposed to return a value
> different from 0 when log in, but it's returning a 0.
> In advance, thank you for your help.
> "John Bell" wrote:
|||Yes, using the credentials, i can log in the QA.
"Tibor Karaszi" wrote:

> VBSQL.VBX is soo old. It uses the old DbLibrary API... Anyhow, does the SQL Server allow SQL Server
> logins? Can you login from QA on the same machine using the same credentials?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Logan" <Logan@.discussions.microsoft.com> wrote in message
> news:653CEA89-C956-4A43-A252-A96AA9339E3F@.microsoft.com...
>
|||It is not a named SQL Server instance, I hope?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Logan" <Logan@.discussions.microsoft.com> wrote in message
news:38232FBF-885E-41DF-9B34-BFC5C87C3C46@.microsoft.com...[vbcol=seagreen]
> Yes, using the credentials, i can log in the QA.
> "Tibor Karaszi" wrote:
|||No, i'm using the IP
"Tibor Karaszi" wrote:

> It is not a named SQL Server instance, I hope?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Logan" <Logan@.discussions.microsoft.com> wrote in message
> news:38232FBF-885E-41DF-9B34-BFC5C87C3C46@.microsoft.com...
>
|||Hi
Does this mean that profiler says the login is successful? In which case the
issue is not with SQL Server!
John
"Logan" <Logan@.discussions.microsoft.com> wrote in message
news:653CEA89-C956-4A43-A252-A96AA9339E3F@.microsoft.com...[vbcol=seagreen]
> This vbx receive a Server, BD, User and Pass it's supposed to return a
> value
> different from 0 when log in, but it's returning a 0.
> In advance, thank you for your help.
> "John Bell" wrote:
|||Thank you for your help
"Logan" wrote:

> I have an vb4 application (i know it's old, but it's not my fault), and i
> need to change the used database (sybase) to sql (sql 2000). They are using
> VBSQL.VBX to access the sybase database, it's possible to use the sql 2000
> database with this .vbx i can't log in.

Is possible?

I have an vb4 application (i know it's old, but it's not my fault), and i
need to change the used database (sybase) to sql (sql 2000). They are using
VBSQL.VBX to access the sybase database, it's possible to use the sql 2000
database with this .vbx i can't log in.Hi
I am not familiar with sybase and this vbx! But with SQL 2000 you audit the
logins with profiler so you can see how it is trying to connect. I would
assume that you have the configured/changed the servername and also any
other setting that are require such as a DSN.
John
"Logan" <Logan@.discussions.microsoft.com> wrote in message
news:55932181-6449-4520-8EBA-8C2D213A4206@.microsoft.com...
>I have an vb4 application (i know it's old, but it's not my fault), and i
> need to change the used database (sybase) to sql (sql 2000). They are
> using
> VBSQL.VBX to access the sybase database, it's possible to use the sql 2000
> database with this .vbx i can't log in.|||This vbx receive a Server, BD, User and Pass it's supposed to return a value
different from 0 when log in, but it's returning a 0.
In advance, thank you for your help.
"John Bell" wrote:

> Hi
> I am not familiar with sybase and this vbx! But with SQL 2000 you audit th
e
> logins with profiler so you can see how it is trying to connect. I would
> assume that you have the configured/changed the servername and also any
> other setting that are require such as a DSN.
> John
> "Logan" <Logan@.discussions.microsoft.com> wrote in message
> news:55932181-6449-4520-8EBA-8C2D213A4206@.microsoft.com...
>
>|||VBSQL.VBX is soo old. It uses the old DbLibrary API... Anyhow, does the SQL
Server allow SQL Server
logins? Can you login from QA on the same machine using the same credentials
?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Logan" <Logan@.discussions.microsoft.com> wrote in message
news:653CEA89-C956-4A43-A252-A96AA9339E3F@.microsoft.com...[vbcol=seagreen]
> This vbx receive a Server, BD, User and Pass it's supposed to return a val
ue
> different from 0 when log in, but it's returning a 0.
> In advance, thank you for your help.
> "John Bell" wrote:
>|||Yes, using the credentials, i can log in the QA.
"Tibor Karaszi" wrote:

> VBSQL.VBX is soo old. It uses the old DbLibrary API... Anyhow, does the SQ
L Server allow SQL Server
> logins? Can you login from QA on the same machine using the same credentia
ls?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Logan" <Logan@.discussions.microsoft.com> wrote in message
> news:653CEA89-C956-4A43-A252-A96AA9339E3F@.microsoft.com...
>|||It is not a named SQL Server instance, I hope?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Logan" <Logan@.discussions.microsoft.com> wrote in message
news:38232FBF-885E-41DF-9B34-BFC5C87C3C46@.microsoft.com...[vbcol=seagreen]
> Yes, using the credentials, i can log in the QA.
> "Tibor Karaszi" wrote:
>|||No, i'm using the IP
"Tibor Karaszi" wrote:

> It is not a named SQL Server instance, I hope?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Logan" <Logan@.discussions.microsoft.com> wrote in message
> news:38232FBF-885E-41DF-9B34-BFC5C87C3C46@.microsoft.com...
>|||Hi
Does this mean that profiler says the login is successful? In which case the
issue is not with SQL Server!
John
"Logan" <Logan@.discussions.microsoft.com> wrote in message
news:653CEA89-C956-4A43-A252-A96AA9339E3F@.microsoft.com...[vbcol=seagreen]
> This vbx receive a Server, BD, User and Pass it's supposed to return a
> value
> different from 0 when log in, but it's returning a 0.
> In advance, thank you for your help.
> "John Bell" wrote:
>|||Thank you for your help
"Logan" wrote:

> I have an vb4 application (i know it's old, but it's not my fault), and i
> need to change the used database (sybase) to sql (sql 2000). They are usin
g
> VBSQL.VBX to access the sybase database, it's possible to use the sql 2000
> database with this .vbx i can't log in.