I have a branch office I would like to connect to our database, which is
fronted by an Access ADP project. I'm having problems with security (the old
runtime 17), but in other small tests it seems the system is very slow. I
guess I'm not really that surprised.
I am considering using replication to set up a second server. Our workflow
is fairly simple. There are many tables that are effectively read-only, and
could be replicated using any method. There is one table that will see
updates from the remote site, as well as a trigger that fires when that table
is updated (keeping an audit log).
Can anyone suggest the best method for this case? It would seem that Merge
replication would be fine, but the documentation suggests I will have
problems with ACID on merge, and little else. Transactional sounds fine,
there's only about 5 tables that see updates on any sort of daily basis, and
only the one orders table that really gets hit.
However that table uses a auto-incrementing pkey. Will that even work?
MauryFor uni-directional data flow transactional replication will work best.
Identity values will be assigned on the publisher/data source. If you are
replicating to a table which has the identity property on this column you
will need to use the not for replication switch. By default Transactional
replication will not put the identity property on columns on the
subscriber/data destination.
Merge is intended for clients which are frequently offline and when you need
bi-directional replication. With careful partitioning you will avoid the
collisions which will break the d part of ACID.
If you need bi-directional replication you can use bi=-directional
transactional replication.
--
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
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:6016EF86-8F9A-4D0D-9C83-CF08AB06107A@.microsoft.com...
> I have a branch office I would like to connect to our database, which is
> fronted by an Access ADP project. I'm having problems with security (the
old
> runtime 17), but in other small tests it seems the system is very slow. I
> guess I'm not really that surprised.
> I am considering using replication to set up a second server. Our workflow
> is fairly simple. There are many tables that are effectively read-only,
and
> could be replicated using any method. There is one table that will see
> updates from the remote site, as well as a trigger that fires when that
table
> is updated (keeping an audit log).
> Can anyone suggest the best method for this case? It would seem that Merge
> replication would be fine, but the documentation suggests I will have
> problems with ACID on merge, and little else. Transactional sounds fine,
> there's only about 5 tables that see updates on any sort of daily basis,
and
> only the one orders table that really gets hit.
> However that table uses a auto-incrementing pkey. Will that even work?
> Maury|||"Hilary Cotter" wrote:
> For uni-directional data flow transactional replication will work best.
No good here, one of the tables WILL be bi-directional.
> Identity values will be assigned on the publisher/data source.
This is a bit confusing. In my case I am sharing one updating table. If I
turn this off, does this mean that a new row will always be assigned a
"local" identity, one that might be different on the remote machine?
Maurysql
Showing posts with label transaction. Show all posts
Showing posts with label transaction. Show all posts
Friday, March 30, 2012
Merge or Transaction Replication?
I have a branch office I would like to connect to our database, which is
fronted by an Access ADP project. I'm having problems with security (the old
runtime 17), but in other small tests it seems the system is very slow. I
guess I'm not really that surprised.
I am considering using replication to set up a second server. Our workflow
is fairly simple. There are many tables that are effectively read-only, and
could be replicated using any method. There is one table that will see
updates from the remote site, as well as a trigger that fires when that table
is updated (keeping an audit log).
Can anyone suggest the best method for this case? It would seem that Merge
replication would be fine, but the documentation suggests I will have
problems with ACID on merge, and little else. Transactional sounds fine,
there's only about 5 tables that see updates on any sort of daily basis, and
only the one orders table that really gets hit.
However that table uses a auto-incrementing pkey. Will that even work?
Maury
For uni-directional data flow transactional replication will work best.
Identity values will be assigned on the publisher/data source. If you are
replicating to a table which has the identity property on this column you
will need to use the not for replication switch. By default Transactional
replication will not put the identity property on columns on the
subscriber/data destination.
Merge is intended for clients which are frequently offline and when you need
bi-directional replication. With careful partitioning you will avoid the
collisions which will break the d part of ACID.
If you need bi-directional replication you can use bi=-directional
transactional replication.
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
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:6016EF86-8F9A-4D0D-9C83-CF08AB06107A@.microsoft.com...
> I have a branch office I would like to connect to our database, which is
> fronted by an Access ADP project. I'm having problems with security (the
old
> runtime 17), but in other small tests it seems the system is very slow. I
> guess I'm not really that surprised.
> I am considering using replication to set up a second server. Our workflow
> is fairly simple. There are many tables that are effectively read-only,
and
> could be replicated using any method. There is one table that will see
> updates from the remote site, as well as a trigger that fires when that
table
> is updated (keeping an audit log).
> Can anyone suggest the best method for this case? It would seem that Merge
> replication would be fine, but the documentation suggests I will have
> problems with ACID on merge, and little else. Transactional sounds fine,
> there's only about 5 tables that see updates on any sort of daily basis,
and
> only the one orders table that really gets hit.
> However that table uses a auto-incrementing pkey. Will that even work?
> Maury
|||"Hilary Cotter" wrote:
> For uni-directional data flow transactional replication will work best.
No good here, one of the tables WILL be bi-directional.
> Identity values will be assigned on the publisher/data source.
This is a bit confusing. In my case I am sharing one updating table. If I
turn this off, does this mean that a new row will always be assigned a
"local" identity, one that might be different on the remote machine?
Maury
fronted by an Access ADP project. I'm having problems with security (the old
runtime 17), but in other small tests it seems the system is very slow. I
guess I'm not really that surprised.
I am considering using replication to set up a second server. Our workflow
is fairly simple. There are many tables that are effectively read-only, and
could be replicated using any method. There is one table that will see
updates from the remote site, as well as a trigger that fires when that table
is updated (keeping an audit log).
Can anyone suggest the best method for this case? It would seem that Merge
replication would be fine, but the documentation suggests I will have
problems with ACID on merge, and little else. Transactional sounds fine,
there's only about 5 tables that see updates on any sort of daily basis, and
only the one orders table that really gets hit.
However that table uses a auto-incrementing pkey. Will that even work?
Maury
For uni-directional data flow transactional replication will work best.
Identity values will be assigned on the publisher/data source. If you are
replicating to a table which has the identity property on this column you
will need to use the not for replication switch. By default Transactional
replication will not put the identity property on columns on the
subscriber/data destination.
Merge is intended for clients which are frequently offline and when you need
bi-directional replication. With careful partitioning you will avoid the
collisions which will break the d part of ACID.
If you need bi-directional replication you can use bi=-directional
transactional replication.
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
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:6016EF86-8F9A-4D0D-9C83-CF08AB06107A@.microsoft.com...
> I have a branch office I would like to connect to our database, which is
> fronted by an Access ADP project. I'm having problems with security (the
old
> runtime 17), but in other small tests it seems the system is very slow. I
> guess I'm not really that surprised.
> I am considering using replication to set up a second server. Our workflow
> is fairly simple. There are many tables that are effectively read-only,
and
> could be replicated using any method. There is one table that will see
> updates from the remote site, as well as a trigger that fires when that
table
> is updated (keeping an audit log).
> Can anyone suggest the best method for this case? It would seem that Merge
> replication would be fine, but the documentation suggests I will have
> problems with ACID on merge, and little else. Transactional sounds fine,
> there's only about 5 tables that see updates on any sort of daily basis,
and
> only the one orders table that really gets hit.
> However that table uses a auto-incrementing pkey. Will that even work?
> Maury
|||"Hilary Cotter" wrote:
> For uni-directional data flow transactional replication will work best.
No good here, one of the tables WILL be bi-directional.
> Identity values will be assigned on the publisher/data source.
This is a bit confusing. In my case I am sharing one updating table. If I
turn this off, does this mean that a new row will always be assigned a
"local" identity, one that might be different on the remote machine?
Maury
Merge or Transaction Replication?
I have a branch office I would like to connect to our database, which is
fronted by an Access ADP project. I'm having problems with security (the old
runtime 17), but in other small tests it seems the system is very slow. I
guess I'm not really that surprised.
I am considering using replication to set up a second server. Our workflow
is fairly simple. There are many tables that are effectively read-only, and
could be replicated using any method. There is one table that will see
updates from the remote site, as well as a trigger that fires when that tabl
e
is updated (keeping an audit log).
Can anyone suggest the best method for this case? It would seem that Merge
replication would be fine, but the documentation suggests I will have
problems with ACID on merge, and little else. Transactional sounds fine,
there's only about 5 tables that see updates on any sort of daily basis, and
only the one orders table that really gets hit.
However that table uses a auto-incrementing pkey. Will that even work?
MauryFor uni-directional data flow transactional replication will work best.
Identity values will be assigned on the publisher/data source. If you are
replicating to a table which has the identity property on this column you
will need to use the not for replication switch. By default Transactional
replication will not put the identity property on columns on the
subscriber/data destination.
Merge is intended for clients which are frequently offline and when you need
bi-directional replication. With careful partitioning you will avoid the
collisions which will break the d part of ACID.
If you need bi-directional replication you can use bi=-directional
transactional replication.
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
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:6016EF86-8F9A-4D0D-9C83-CF08AB06107A@.microsoft.com...
> I have a branch office I would like to connect to our database, which is
> fronted by an Access ADP project. I'm having problems with security (the
old
> runtime 17), but in other small tests it seems the system is very slow. I
> guess I'm not really that surprised.
> I am considering using replication to set up a second server. Our workflow
> is fairly simple. There are many tables that are effectively read-only,
and
> could be replicated using any method. There is one table that will see
> updates from the remote site, as well as a trigger that fires when that
table
> is updated (keeping an audit log).
> Can anyone suggest the best method for this case? It would seem that Merge
> replication would be fine, but the documentation suggests I will have
> problems with ACID on merge, and little else. Transactional sounds fine,
> there's only about 5 tables that see updates on any sort of daily basis,
and
> only the one orders table that really gets hit.
> However that table uses a auto-incrementing pkey. Will that even work?
> Maury|||"Hilary Cotter" wrote:
> For uni-directional data flow transactional replication will work best.
No good here, one of the tables WILL be bi-directional.
> Identity values will be assigned on the publisher/data source.
This is a bit confusing. In my case I am sharing one updating table. If I
turn this off, does this mean that a new row will always be assigned a
"local" identity, one that might be different on the remote machine?
Maury
fronted by an Access ADP project. I'm having problems with security (the old
runtime 17), but in other small tests it seems the system is very slow. I
guess I'm not really that surprised.
I am considering using replication to set up a second server. Our workflow
is fairly simple. There are many tables that are effectively read-only, and
could be replicated using any method. There is one table that will see
updates from the remote site, as well as a trigger that fires when that tabl
e
is updated (keeping an audit log).
Can anyone suggest the best method for this case? It would seem that Merge
replication would be fine, but the documentation suggests I will have
problems with ACID on merge, and little else. Transactional sounds fine,
there's only about 5 tables that see updates on any sort of daily basis, and
only the one orders table that really gets hit.
However that table uses a auto-incrementing pkey. Will that even work?
MauryFor uni-directional data flow transactional replication will work best.
Identity values will be assigned on the publisher/data source. If you are
replicating to a table which has the identity property on this column you
will need to use the not for replication switch. By default Transactional
replication will not put the identity property on columns on the
subscriber/data destination.
Merge is intended for clients which are frequently offline and when you need
bi-directional replication. With careful partitioning you will avoid the
collisions which will break the d part of ACID.
If you need bi-directional replication you can use bi=-directional
transactional replication.
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
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:6016EF86-8F9A-4D0D-9C83-CF08AB06107A@.microsoft.com...
> I have a branch office I would like to connect to our database, which is
> fronted by an Access ADP project. I'm having problems with security (the
old
> runtime 17), but in other small tests it seems the system is very slow. I
> guess I'm not really that surprised.
> I am considering using replication to set up a second server. Our workflow
> is fairly simple. There are many tables that are effectively read-only,
and
> could be replicated using any method. There is one table that will see
> updates from the remote site, as well as a trigger that fires when that
table
> is updated (keeping an audit log).
> Can anyone suggest the best method for this case? It would seem that Merge
> replication would be fine, but the documentation suggests I will have
> problems with ACID on merge, and little else. Transactional sounds fine,
> there's only about 5 tables that see updates on any sort of daily basis,
and
> only the one orders table that really gets hit.
> However that table uses a auto-incrementing pkey. Will that even work?
> Maury|||"Hilary Cotter" wrote:
> For uni-directional data flow transactional replication will work best.
No good here, one of the tables WILL be bi-directional.
> Identity values will be assigned on the publisher/data source.
This is a bit confusing. In my case I am sharing one updating table. If I
turn this off, does this mean that a new row will always be assigned a
"local" identity, one that might be different on the remote machine?
Maury
Friday, March 9, 2012
memory question
Hi ,
we have one server with 1 GB of RAM and 500 MB is
pagefile.
Sever is not dedicated to SQL Server since there is also
Microsoft transaction server running and IIS4.
SQL Server connsumes aproximatelly 980 MB and mts alone
around 20 MB.
Thant means that mts has to go to the pagefile.
SQL Server is configured to dinamically adjust the amount
of memory used based upon demand.
Should I configure it to use fixed amount of memory or
that's bad idea ?
This is production server.
Thanks very much!SQL Server uses an algorithm that continually monitors the paging of the OS.
If SQL Server can take some benefits of getting more memory from the OS it
allocates more memory while the paging is tolerable.
In general this algorithm is efficient finding the correct amount of memory
to be allocated. If you think this amount is bigger than you wish it is
reasonable to limit the SQL Server's memory. Just take in mind that the
amount of memory allocated by the SQL Server is not arbitrary.
"Mirna" <anonymous@.discussions.microsoft.com> wrote in message
news:33d701c3fd62$475c4ec0$a001280a@.phx.gbl...
> Hi ,
> we have one server with 1 GB of RAM and 500 MB is
> pagefile.
> Sever is not dedicated to SQL Server since there is also
> Microsoft transaction server running and IIS4.
> SQL Server connsumes aproximatelly 980 MB and mts alone
> around 20 MB.
> Thant means that mts has to go to the pagefile.
> SQL Server is configured to dinamically adjust the amount
> of memory used based upon demand.
> Should I configure it to use fixed amount of memory or
> that's bad idea ?
> This is production server.
> Thanks very much!
we have one server with 1 GB of RAM and 500 MB is
pagefile.
Sever is not dedicated to SQL Server since there is also
Microsoft transaction server running and IIS4.
SQL Server connsumes aproximatelly 980 MB and mts alone
around 20 MB.
Thant means that mts has to go to the pagefile.
SQL Server is configured to dinamically adjust the amount
of memory used based upon demand.
Should I configure it to use fixed amount of memory or
that's bad idea ?
This is production server.
Thanks very much!SQL Server uses an algorithm that continually monitors the paging of the OS.
If SQL Server can take some benefits of getting more memory from the OS it
allocates more memory while the paging is tolerable.
In general this algorithm is efficient finding the correct amount of memory
to be allocated. If you think this amount is bigger than you wish it is
reasonable to limit the SQL Server's memory. Just take in mind that the
amount of memory allocated by the SQL Server is not arbitrary.
"Mirna" <anonymous@.discussions.microsoft.com> wrote in message
news:33d701c3fd62$475c4ec0$a001280a@.phx.gbl...
> Hi ,
> we have one server with 1 GB of RAM and 500 MB is
> pagefile.
> Sever is not dedicated to SQL Server since there is also
> Microsoft transaction server running and IIS4.
> SQL Server connsumes aproximatelly 980 MB and mts alone
> around 20 MB.
> Thant means that mts has to go to the pagefile.
> SQL Server is configured to dinamically adjust the amount
> of memory used based upon demand.
> Should I configure it to use fixed amount of memory or
> that's bad idea ?
> This is production server.
> Thanks very much!
Wednesday, March 7, 2012
memory question
Hi ,
we have one server with 1 GB of RAM and 500 MB is
pagefile.
Sever is not dedicated to SQL Server since there is also
Microsoft transaction server running and IIS4.
SQL Server connsumes aproximatelly 980 MB and mts alone
around 20 MB.
Thant means that mts has to go to the pagefile.
SQL Server is configured to dinamically adjust the amount
of memory used based upon demand.
Should I configure it to use fixed amount of memory or
that's bad idea ?
This is production server.
Thanks very much!SQL Server uses an algorithm that continually monitors the paging of the OS.
If SQL Server can take some benefits of getting more memory from the OS it
allocates more memory while the paging is tolerable.
In general this algorithm is efficient finding the correct amount of memory
to be allocated. If you think this amount is bigger than you wish it is
reasonable to limit the SQL Server's memory. Just take in mind that the
amount of memory allocated by the SQL Server is not arbitrary.
"Mirna" <anonymous@.discussions.microsoft.com> wrote in message
news:33d701c3fd62$475c4ec0$a001280a@.phx.gbl...
> Hi ,
> we have one server with 1 GB of RAM and 500 MB is
> pagefile.
> Sever is not dedicated to SQL Server since there is also
> Microsoft transaction server running and IIS4.
> SQL Server connsumes aproximatelly 980 MB and mts alone
> around 20 MB.
> Thant means that mts has to go to the pagefile.
> SQL Server is configured to dinamically adjust the amount
> of memory used based upon demand.
> Should I configure it to use fixed amount of memory or
> that's bad idea ?
> This is production server.
> Thanks very much!
we have one server with 1 GB of RAM and 500 MB is
pagefile.
Sever is not dedicated to SQL Server since there is also
Microsoft transaction server running and IIS4.
SQL Server connsumes aproximatelly 980 MB and mts alone
around 20 MB.
Thant means that mts has to go to the pagefile.
SQL Server is configured to dinamically adjust the amount
of memory used based upon demand.
Should I configure it to use fixed amount of memory or
that's bad idea ?
This is production server.
Thanks very much!SQL Server uses an algorithm that continually monitors the paging of the OS.
If SQL Server can take some benefits of getting more memory from the OS it
allocates more memory while the paging is tolerable.
In general this algorithm is efficient finding the correct amount of memory
to be allocated. If you think this amount is bigger than you wish it is
reasonable to limit the SQL Server's memory. Just take in mind that the
amount of memory allocated by the SQL Server is not arbitrary.
"Mirna" <anonymous@.discussions.microsoft.com> wrote in message
news:33d701c3fd62$475c4ec0$a001280a@.phx.gbl...
> Hi ,
> we have one server with 1 GB of RAM and 500 MB is
> pagefile.
> Sever is not dedicated to SQL Server since there is also
> Microsoft transaction server running and IIS4.
> SQL Server connsumes aproximatelly 980 MB and mts alone
> around 20 MB.
> Thant means that mts has to go to the pagefile.
> SQL Server is configured to dinamically adjust the amount
> of memory used based upon demand.
> Should I configure it to use fixed amount of memory or
> that's bad idea ?
> This is production server.
> Thanks very much!
Monday, February 20, 2012
Memory Low after running for a day
A program is used to insert data record by record to a SQL Server (2000)
using transaction. After a day, 300M or memory was used up. What would be
the cause of such "memory leak" and how to releasing the memory other than
reboot? Thank you.> A program is used to insert data record by record to a SQL Server (2000)
> using transaction. After a day, 300M or memory was used up. What would be
> the cause of such "memory leak" and how to releasing the memory other than
> reboot? Thank you.
This is not a memory leak, this is how it has to work. SQL Server caches
execution plans and the data, grabs memory when it needs it, but releases it
only on a demand. If you have to, you can limit the memory SQL Server uses
with the maximum memory configuration option
--
Dejan Sarka, SQL Server MVP
FAQ from Neil & others at: http://www.sqlserverfaq.com
Please reply only to the newsgroups.
PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
using transaction. After a day, 300M or memory was used up. What would be
the cause of such "memory leak" and how to releasing the memory other than
reboot? Thank you.> A program is used to insert data record by record to a SQL Server (2000)
> using transaction. After a day, 300M or memory was used up. What would be
> the cause of such "memory leak" and how to releasing the memory other than
> reboot? Thank you.
This is not a memory leak, this is how it has to work. SQL Server caches
execution plans and the data, grabs memory when it needs it, but releases it
only on a demand. If you have to, you can limit the memory SQL Server uses
with the maximum memory configuration option
--
Dejan Sarka, SQL Server MVP
FAQ from Neil & others at: http://www.sqlserverfaq.com
Please reply only to the newsgroups.
PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
Subscribe to:
Comments (Atom)