Optimizing SQL 2000 SP4 for large amount of memory

Hi
We will within the next week move our SQL 2000 server SP4 to a new high
performance server (2 x ghz XEON, 4 GB RAM)
The database which is to be running on the server has a physical size of 50
mb on disk.
Is there a way to optimize the SQL 2000 to keep as much as possible
in-memory so searching will as quick as possible? The read / write rate is
approx 1000/1 (we read 1000 x as often as writing)
Is the some memory settings we can tweak or are we better of letting sql
server 2000 handle it?
Thanks in regards
Anders JacobsenJust checking 50Mb not 50 Gb. If so then there's not much you can do, with
4Gb of RAM it will all reside in memory.
--
Simon Sabin
SQL Server MVP
http://sqljunkies.com/weblog/simons
"Anders" <anderskj1@.yahoo.dk> wrote in message
news:%23j7EngYXGHA.4432@.TK2MSFTNGP04.phx.gbl...
> Hi
> We will within the next week move our SQL 2000 server SP4 to a new high
> performance server (2 x ghz XEON, 4 GB RAM)
> The database which is to be running on the server has a physical size of
> 50 mb on disk.
> Is there a way to optimize the SQL 2000 to keep as much as possible
> in-memory so searching will as quick as possible? The read / write rate is
> approx 1000/1 (we read 1000 x as often as writing)
> Is the some memory settings we can tweak or are we better of letting sql
> server 2000 handle it?
> Thanks in regards
> Anders Jacobsen
>|||"Anders" <anderskj1@.yahoo.dk> wrote in message
news:%23j7EngYXGHA.4432@.TK2MSFTNGP04.phx.gbl...
> Hi
> We will within the next week move our SQL 2000 server SP4 to a new high
> performance server (2 x ghz XEON, 4 GB RAM)
> The database which is to be running on the server has a physical size of
50
> mb on disk.
> Is there a way to optimize the SQL 2000 to keep as much as possible
> in-memory so searching will as quick as possible? The read / write rate is
> approx 1000/1 (we read 1000 x as often as writing)
> Is the some memory settings we can tweak or are we better of letting sql
> server 2000 handle it?
Generally you're better off letting SQL Server handle it. It will grab as
much RAM as it can (2GB with Standard, more with Enterprise with the proper
switches) and use it to cache.
Check perfmon and look at the cache hit ratio for one thing to see how
you're doing.
If it's truly 50MB, that'll fit into RAM absolutely w/o problems.
> Thanks in regards
> Anders Jacobsen
>|||> Generally you're better off letting SQL Server handle it. It will grab as
> much RAM as it can (2GB with Standard, more with Enterprise with the
> proper
> switches) and use it to cache.
> Check perfmon and look at the cache hit ratio for one thing to see how
> you're doing.
> If it's truly 50MB, that'll fit into RAM absolutely w/o problems.
Sounds great.
Thanks

Optimizing SQL 2000 SP4 for large amount of memory

Hi
We will within the next week move our SQL 2000 server SP4 to a new high
performance server (2 x ghz XEON, 4 GB RAM)
The database which is to be running on the server has a physical size of 50
mb on disk.
Is there a way to optimize the SQL 2000 to keep as much as possible
in-memory so searching will as quick as possible? The read / write rate is
approx 1000/1 (we read 1000 x as often as writing)
Is the some memory settings we can tweak or are we better of letting sql
server 2000 handle it?
Thanks in regards
Anders JacobsenJust checking 50Mb not 50 Gb. If so then there's not much you can do, with
4Gb of RAM it will all reside in memory.
Simon Sabin
SQL Server MVP
http://sqljunkies.com/weblog/simons
"Anders" <anderskj1@.yahoo.dk> wrote in message
news:%23j7EngYXGHA.4432@.TK2MSFTNGP04.phx.gbl...
> Hi
> We will within the next week move our SQL 2000 server SP4 to a new high
> performance server (2 x ghz XEON, 4 GB RAM)
> The database which is to be running on the server has a physical size of
> 50 mb on disk.
> Is there a way to optimize the SQL 2000 to keep as much as possible
> in-memory so searching will as quick as possible? The read / write rate is
> approx 1000/1 (we read 1000 x as often as writing)
> Is the some memory settings we can tweak or are we better of letting sql
> server 2000 handle it?
> Thanks in regards
> Anders Jacobsen
>|||"Anders" <anderskj1@.yahoo.dk> wrote in message
news:%23j7EngYXGHA.4432@.TK2MSFTNGP04.phx.gbl...
> Hi
> We will within the next week move our SQL 2000 server SP4 to a new high
> performance server (2 x ghz XEON, 4 GB RAM)
> The database which is to be running on the server has a physical size of
50
> mb on disk.
> Is there a way to optimize the SQL 2000 to keep as much as possible
> in-memory so searching will as quick as possible? The read / write rate is
> approx 1000/1 (we read 1000 x as often as writing)
> Is the some memory settings we can tweak or are we better of letting sql
> server 2000 handle it?
Generally you're better off letting SQL Server handle it. It will grab as
much RAM as it can (2GB with Standard, more with Enterprise with the proper
switches) and use it to cache.
Check perfmon and look at the cache hit ratio for one thing to see how
you're doing.
If it's truly 50MB, that'll fit into RAM absolutely w/o problems.

> Thanks in regards
> Anders Jacobsen
>|||> Generally you're better off letting SQL Server handle it. It will grab as
> much RAM as it can (2GB with Standard, more with Enterprise with the
> proper
> switches) and use it to cache.
> Check perfmon and look at the cache hit ratio for one thing to see how
> you're doing.
> If it's truly 50MB, that'll fit into RAM absolutely w/o problems.
Sounds great.
Thanks

Optimizing insert performance

Hi there.
We're using SQL Server 2000 on a Windows 2000 box with dual
hyperthreaded Intel CPUs, raided hard disks and 2GB RAM. It should
certainly be able to handle our currently fairly modest demands. We
have all the latest security patches. Parallelism is disabled.
We're currently experiencing unacceptable slowness for inserts to a
particular table, Account_Transaction (see the DDL at the end of this
message). As you can see, the main features are a BIGINT auto-increment
primary key, and three foreign keys. This table currently has 7 million
rows and another million are inserted each month. We are currently
unhappy with the length of time it takes to insert into this table as
this operation is at the heart of our application.
Here is an example insert, called from within a stored procedure:
INSERT INTO dbo.Account_Transaction(
invoice_id, transaction_type_id, qty, amount, commission,
tax_percentage, currency_code, points, line_id, comment,
balance_before, external_id)
VALUES
(
@.invoice_id,
@.transaction_type_id,
@.qty,
@.amount,
@.amount_commission,
@.tax_percentage,
@.currency_code_home,
@.points,
@.line_id,
@.comment,
@.balance,
@.external_id
)
Looking at this query's SP:StmtCompleted event from the profiler we can
see the query took 375ms to complete and performed 76 reads (and
apparently zero writes, although the data was definitely inserted). We
think this is far too long.
What is strange though is that if we run the exact same statement
OUTSIDE of the calling procedure in query analyzer, substituting
literals for the variables, then it apparently completes in just 63ms,
although it performs the same number of writes:
INSERT INTO dbo.Account_Transaction(
invoice_id, transaction_type_id, qty, amount, commission,
tax_percentage, currency_code, points, line_id, comment,
balance_before, external_id)
VALUES
(6842289, 12, 1, 1, 0, 10, 'AUD', 0, 4, 'test', 0, 0)
If we now try the same two things on our test database, running on a a
bog-standard desktop machine, with only 300000 rows in the
Account_Transaction table, then both queries execute in similar times of
around 70ms.
So my question is - what is causing the slowness, and how can we speed
it up? How do we make the insert operation from within the SP take the
same amount of time as it takes when run on its own?
Any help appreciated,
Mike C
CREATE TABLE [dbo].[Account_Transaction] (
[id] [bigint] IDENTITY (1, 1) NOT NULL ,
[invoice_id] [bigint] NOT NULL ,
[transaction_type_id] [int] NOT NULL ,
[qty] [int] NOT NULL ,
[amount] [money] NOT NULL ,
[tax_percentage] [decimal](9, 3) NULL ,
[commission] [float] NOT NULL ,
[currency_code] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[line_id] [int] NOT NULL ,
[points] [int] NOT NULL ,
[comment] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[balance_before] [money] NOT NULL ,
[balance_after] [money] NULL ,
[external_id] [bigint] NULL ,
[msrepl_tran_version] [uniqueidentifier] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Account_Transaction] WITH NOCHECK ADD
CONSTRAINT [PK_Account_Transaction] PRIMARY KEY CLUSTERED
(
[id]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[Account_Transaction] WITH NOCHECK ADD
CONSTRAINT [DF__Account_T__amoun__3118447E] DEFAULT (0) FOR [amount],
CONSTRAINT [DF__Account_T__point__320C68B7] DEFAULT (0) FOR [points],
CONSTRAINT [DF__Account_T__msrep__31783731] DEFAULT (newid()) FOR
[msrepl_tran_version]
GO
CREATE INDEX [IX_Account_Transaction] ON
[dbo].[Account_Transaction]([invoice_id]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[Account_Transaction] ADD
CONSTRAINT [FK_Account_Transaction_Account_Invoice]
FOREIGN KEY
(
[invoice_id]
) REFERENCES [dbo].[Account_Invoice] (
[id]
) ON DELETE CASCADE ON UPDATE CASCADE ,
CONSTRAINT [FK_Account_Transaction_Line] FOREIGN KEY
(
[line_id]
) REFERENCES [dbo].[Line] (
[id]
),
CONSTRAINT [FK_Account_Transaction_Transaction_Type
] FOREIGN KEY
(
[transaction_type_id]
) REFERENCES [dbo].[Transaction_Type] (
[id]
)
GOMike
1) Make sure that your table has no triggers
2) Try drop the indexes just prior to INSERTING and re-create them after
Another thing might help that if you do it by using SP which accepted
parameters try to re-assign these parameters to local variable
CREATE PROC myProc
@.par INT
AS
DECLARE @.lpar INT
SET @.lpar =@.par
INSERT INTO Table VALUES (@.lpar )
"Mike Chamberlain" <none@.hotmail.com> wrote in message
news:eGXwhg%23HGHA.1180@.TK2MSFTNGP09.phx.gbl...
> Hi there.
> We're using SQL Server 2000 on a Windows 2000 box with dual hyperthreaded
> Intel CPUs, raided hard disks and 2GB RAM. It should certainly be able to
> handle our currently fairly modest demands. We have all the latest
> security patches. Parallelism is disabled.
> We're currently experiencing unacceptable slowness for inserts to a
> particular table, Account_Transaction (see the DDL at the end of this
> message). As you can see, the main features are a BIGINT auto-increment
> primary key, and three foreign keys. This table currently has 7 million
> rows and another million are inserted each month. We are currently
> unhappy with the length of time it takes to insert into this table as this
> operation is at the heart of our application.
> Here is an example insert, called from within a stored procedure:
> INSERT INTO dbo.Account_Transaction(
> invoice_id, transaction_type_id, qty, amount, commission,
> tax_percentage, currency_code, points, line_id, comment,
> balance_before, external_id)
> VALUES
> (
> @.invoice_id,
> @.transaction_type_id,
> @.qty,
> @.amount,
> @.amount_commission,
> @.tax_percentage,
> @.currency_code_home,
> @.points,
> @.line_id,
> @.comment,
> @.balance,
> @.external_id
> )
> Looking at this query's SP:StmtCompleted event from the profiler we can
> see the query took 375ms to complete and performed 76 reads (and
> apparently zero writes, although the data was definitely inserted). We
> think this is far too long.
> What is strange though is that if we run the exact same statement OUTSIDE
> of the calling procedure in query analyzer, substituting literals for the
> variables, then it apparently completes in just 63ms, although it performs
> the same number of writes:
> INSERT INTO dbo.Account_Transaction(
> invoice_id, transaction_type_id, qty, amount, commission,
> tax_percentage, currency_code, points, line_id, comment,
> balance_before, external_id)
> VALUES
> (6842289, 12, 1, 1, 0, 10, 'AUD', 0, 4, 'test', 0, 0)
> If we now try the same two things on our test database, running on a a
> bog-standard desktop machine, with only 300000 rows in the
> Account_Transaction table, then both queries execute in similar times of
> around 70ms.
> So my question is - what is causing the slowness, and how can we speed it
> up? How do we make the insert operation from within the SP take the same
> amount of time as it takes when run on its own?
> Any help appreciated,
> --
> Mike C
>
>
> CREATE TABLE [dbo].[Account_Transaction] (
> [id] [bigint] IDENTITY (1, 1) NOT NULL ,
> [invoice_id] [bigint] NOT NULL ,
> [transaction_type_id] [int] NOT NULL ,
> [qty] [int] NOT NULL ,
> [amount] [money] NOT NULL ,
> [tax_percentage] [decimal](9, 3) NULL ,
> [commission] [float] NOT NULL ,
> [currency_code] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [line_id] [int] NOT NULL ,
> [points] [int] NOT NULL ,
> [comment] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [balance_before] [money] NOT NULL ,
> [balance_after] [money] NULL ,
> [external_id] [bigint] NULL ,
> [msrepl_tran_version] [uniqueidentifier] NOT NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Account_Transaction] WITH NOCHECK ADD
> CONSTRAINT [PK_Account_Transaction] PRIMARY KEY CLUSTERED
> (
> [id]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Account_Transaction] WITH NOCHECK ADD
> CONSTRAINT [DF__Account_T__amoun__3118447E] DEFAULT (0) FOR [amount],
> CONSTRAINT [DF__Account_T__point__320C68B7] DEFAULT (0) FOR [points],
> CONSTRAINT [DF__Account_T__msrep__31783731] DEFAULT (newid()) FOR
> [msrepl_tran_version]
> GO
> CREATE INDEX [IX_Account_Transaction] ON
> [dbo].[Account_Transaction]([invoice_id]) WITH FILLFACTOR = 90 ON
> [PRIMARY]
> GO
> ALTER TABLE [dbo].[Account_Transaction] ADD
> CONSTRAINT [FK_Account_Transaction_Account_Invoice]
FOREIGN KEY
> (
> [invoice_id]
> ) REFERENCES [dbo].[Account_Invoice] (
> [id]
> ) ON DELETE CASCADE ON UPDATE CASCADE ,
> CONSTRAINT [FK_Account_Transaction_Line] FOREIGN KEY
> (
> [line_id]
> ) REFERENCES [dbo].[Line] (
> [id]
> ),
> CONSTRAINT [FK_Account_Transaction_Transaction_Type
] FOREIGN KEY
> (
> [transaction_type_id]
> ) REFERENCES [dbo].[Transaction_Type] (
> [id]
> )
> GO|||It sounds like Mike is speaking of single transactional inserts and not bulk
inserts, so dropping / recreating indexes on the table would not be
practical.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23Unqxm%23HGHA.528@.TK2MSFTNGP12.phx.gbl...
> Mike
> 1) Make sure that your table has no triggers
> 2) Try drop the indexes just prior to INSERTING and re-create them after
>
> Another thing might help that if you do it by using SP which accepted
> parameters try to re-assign these parameters to local variable
>
> CREATE PROC myProc
> @.par INT
> AS
> DECLARE @.lpar INT
>
> SET @.lpar =@.par
> INSERT INTO Table VALUES (@.lpar )
>
>
> "Mike Chamberlain" <none@.hotmail.com> wrote in message
> news:eGXwhg%23HGHA.1180@.TK2MSFTNGP09.phx.gbl...
>|||Consider if the stored procedure is re-compiling each time it is executed.
This could explain the overhead when executing the insert from the procedure
while it still performs the same number of writes as the test insert from
Query Analyzer.
INF: Conditions for Stored Procedure Recompilation
http://support.microsoft.com/defaul...kb;en-us;104445
How to identify the cause of recompilation in an SP:Recompile event
http://support.microsoft.com/defaul...kb;en-us;308737
"Mike Chamberlain" <none@.hotmail.com> wrote in message
news:eGXwhg%23HGHA.1180@.TK2MSFTNGP09.phx.gbl...
> Hi there.
> We're using SQL Server 2000 on a Windows 2000 box with dual hyperthreaded
> Intel CPUs, raided hard disks and 2GB RAM. It should certainly be able to
> handle our currently fairly modest demands. We have all the latest
> security patches. Parallelism is disabled.
> We're currently experiencing unacceptable slowness for inserts to a
> particular table, Account_Transaction (see the DDL at the end of this
> message). As you can see, the main features are a BIGINT auto-increment
> primary key, and three foreign keys. This table currently has 7 million
> rows and another million are inserted each month. We are currently
> unhappy with the length of time it takes to insert into this table as this
> operation is at the heart of our application.
> Here is an example insert, called from within a stored procedure:
> INSERT INTO dbo.Account_Transaction(
> invoice_id, transaction_type_id, qty, amount, commission,
> tax_percentage, currency_code, points, line_id, comment,
> balance_before, external_id)
> VALUES
> (
> @.invoice_id,
> @.transaction_type_id,
> @.qty,
> @.amount,
> @.amount_commission,
> @.tax_percentage,
> @.currency_code_home,
> @.points,
> @.line_id,
> @.comment,
> @.balance,
> @.external_id
> )
> Looking at this query's SP:StmtCompleted event from the profiler we can
> see the query took 375ms to complete and performed 76 reads (and
> apparently zero writes, although the data was definitely inserted). We
> think this is far too long.
> What is strange though is that if we run the exact same statement OUTSIDE
> of the calling procedure in query analyzer, substituting literals for the
> variables, then it apparently completes in just 63ms, although it performs
> the same number of writes:
> INSERT INTO dbo.Account_Transaction(
> invoice_id, transaction_type_id, qty, amount, commission,
> tax_percentage, currency_code, points, line_id, comment,
> balance_before, external_id)
> VALUES
> (6842289, 12, 1, 1, 0, 10, 'AUD', 0, 4, 'test', 0, 0)
> If we now try the same two things on our test database, running on a a
> bog-standard desktop machine, with only 300000 rows in the
> Account_Transaction table, then both queries execute in similar times of
> around 70ms.
> So my question is - what is causing the slowness, and how can we speed it
> up? How do we make the insert operation from within the SP take the same
> amount of time as it takes when run on its own?
> Any help appreciated,
> --
> Mike C
>
>
> CREATE TABLE [dbo].[Account_Transaction] (
> [id] [bigint] IDENTITY (1, 1) NOT NULL ,
> [invoice_id] [bigint] NOT NULL ,
> [transaction_type_id] [int] NOT NULL ,
> [qty] [int] NOT NULL ,
> [amount] [money] NOT NULL ,
> [tax_percentage] [decimal](9, 3) NULL ,
> [commission] [float] NOT NULL ,
> [currency_code] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [line_id] [int] NOT NULL ,
> [points] [int] NOT NULL ,
> [comment] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [balance_before] [money] NOT NULL ,
> [balance_after] [money] NULL ,
> [external_id] [bigint] NULL ,
> [msrepl_tran_version] [uniqueidentifier] NOT NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Account_Transaction] WITH NOCHECK ADD
> CONSTRAINT [PK_Account_Transaction] PRIMARY KEY CLUSTERED
> (
> [id]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Account_Transaction] WITH NOCHECK ADD
> CONSTRAINT [DF__Account_T__amoun__3118447E] DEFAULT (0) FOR [amount],
> CONSTRAINT [DF__Account_T__point__320C68B7] DEFAULT (0) FOR [points],
> CONSTRAINT [DF__Account_T__msrep__31783731] DEFAULT (newid()) FOR
> [msrepl_tran_version]
> GO
> CREATE INDEX [IX_Account_Transaction] ON
> [dbo].[Account_Transaction]([invoice_id]) WITH FILLFACTOR = 90 ON
> [PRIMARY]
> GO
> ALTER TABLE [dbo].[Account_Transaction] ADD
> CONSTRAINT [FK_Account_Transaction_Account_Invoice]
FOREIGN KEY
> (
> [invoice_id]
> ) REFERENCES [dbo].[Account_Invoice] (
> [id]
> ) ON DELETE CASCADE ON UPDATE CASCADE ,
> CONSTRAINT [FK_Account_Transaction_Line] FOREIGN KEY
> (
> [line_id]
> ) REFERENCES [dbo].[Line] (
> [id]
> ),
> CONSTRAINT [FK_Account_Transaction_Transaction_Type
] FOREIGN KEY
> (
> [transaction_type_id]
> ) REFERENCES [dbo].[Transaction_Type] (
> [id]
> )
> GOsql

optimal database configure

hi,
I use sqlw2k sp3 server. I have configure 2xRAID1 with 4 ucp and 8GB
RAM. The first RAID1 for operation system and binary sql and the secend
RAID1 for data. I want to use one instance for two independent database.
Both database will have about 10-20GB data.
I am not sure where put the transation log (on which RAID the first or
the secend). This is my one question.
And my secend question concerning filegroup. Do you have any recommend
to the optimal of this?
rgs,
sylwester
--
Posted via http://dbforums.comIf you only have two RAID1 arrays you might be better off placing the =OS, binaries, data files on one array and the log files on the other =array.
a better setup would involve three raid sets
RAID set: OS + binaries + tempdb + system databases
RAID set: data files
RAID set: log files
The level of RAID that you choose should depend on your desired level of =performance and redundancy.
-- Keith
"skosci" <skoscielecky@.hotmail.com> wrote in message =news:3334411.1062764290@.dbforums.com...
> > hi,
> > > > I use sqlw2k sp3 server. I have configure 2xRAID1 with 4 ucp and 8GB
> RAM. The first RAID1 for operation system and binary sql and the =secend
> RAID1 for data. I want to use one instance for two independent =database.
> Both database will have about 10-20GB data.
> > > > I am not sure where put the transation log (on which RAID the first or
> the secend). This is my one question.
> > > > > > And my secend question concerning filegroup. Do you have any recommend
> to the optimal of this?
> > > > rgs,
> > sylwester
> > > --
> Posted via http://dbforums.com|||Hi Keith,
So I supposed like you. I cannot change configure on the level
RAID just now.
On the one RAID1 installed OS with 33GB.
The secend RAID1 has got 66GB.
Real size of both database is 12GB. In your first recommend I should
reinstall OS.
Tell me more what do you think if I setup like below:
first RAID set: OS+binaries+tempdb+systemdatabase and
secend RAID set: data files and log files.
How much it's worse from your first recommend?
Regards,
Sylwester
Posted via http://dbforums.com

operating systems

Hello,
We aim to use the current server (w2k,sql2000ent, 2x1ghz, 1gb ram, mirrored
pair & raid 5 for data) as a failover machine and upgrade to a new server
for production.
The 2 machines will be in different countries. Users in both countries will
use the production server. If the connection between the countries is
broken, the remote users will use the failover.
We propose to have merge replication between the 2 machines (mainly one
way).
How can we estimate the physical physical size of data that would need to be
transferred to the failover m/c for a given time period?
Presumably it would make sense to use w2003 on both machines?
Do the operating systems on 2 merge replicating machines have to be the
same?
Any advice would be appreciated.
Thanks SOC.
I would not use merge for this - I would use bi-directional transactional
replication. This is ideal because your data is highly partitioned.
Transactions occur on only one side at a time.
Transactional replication is in general much faster than merge replication.
Transactional replication is designed for server to server replication -
whereas merge is designed for clients who often go offline. Merge
replication adds a GUID column to each table you are replicating.
Transactional requires a primary key on each table.
Merge replication can be more difficult to trouble shoot than transactional.
Merge also requires (in general) more processing than transactional.
This is not to say of course that merge is always less performant than
transactional or slower than transactional. If you have a solution where
there are heavy updates, the servers are not connected for significant
lengths of time, and transactions originate on one server - merge is faster
and performs better. Consider a stock market application where 1) the
majority of the transactions are updates 2) the servers connect once a day
3) the transactions all occur on the publisher - in this case merge performs
way better than tranny.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"soc" <zxc0@.yahoo.com> wrote in message
news:eWfg9uQ4EHA.2404@.TK2MSFTNGP14.phx.gbl...
> Hello,
> We aim to use the current server (w2k,sql2000ent, 2x1ghz, 1gb ram,
> mirrored pair & raid 5 for data) as a failover machine and upgrade to a
> new server for production.
> The 2 machines will be in different countries. Users in both countries
> will use the production server. If the connection between the countries is
> broken, the remote users will use the failover.
> We propose to have merge replication between the 2 machines (mainly one
> way).
> How can we estimate the physical physical size of data that would need to
> be transferred to the failover m/c for a given time period?
> Presumably it would make sense to use w2003 on both machines?
> Do the operating systems on 2 merge replicating machines have to be the
> same?
> Any advice would be appreciated.
> Thanks SOC.
>
|||Thanks Hilary,
I will look into bi-directional tranasactional replication.
-Presumably it would make sense to use w2003 on both machines?
-Do the operating systems on 2 replicating machines have to be the
same?
Any advice would be appreciated.
Thanks SOC.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:O8z42MR4EHA.4092@.TK2MSFTNGP14.phx.gbl...
>I would not use merge for this - I would use bi-directional transactional
>replication. This is ideal because your data is highly partitioned.
>Transactions occur on only one side at a time.
> Transactional replication is in general much faster than merge
> replication. Transactional replication is designed for server to server
> replication - whereas merge is designed for clients who often go offline.
> Merge replication adds a GUID column to each table you are replicating.
> Transactional requires a primary key on each table.
> Merge replication can be more difficult to trouble shoot than
> transactional. Merge also requires (in general) more processing than
> transactional.
> This is not to say of course that merge is always less performant than
> transactional or slower than transactional. If you have a solution where
> there are heavy updates, the servers are not connected for significant
> lengths of time, and transactions originate on one server - merge is
> faster and performs better. Consider a stock market application where 1)
> the majority of the transactions are updates 2) the servers connect once a
> day 3) the transactions all occur on the publisher - in this case merge
> performs way better than tranny.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> Now available for purchase at:
> http://www.nwsu.com/0974973602.html
> "soc" <zxc0@.yahoo.com> wrote in message
> news:eWfg9uQ4EHA.2404@.TK2MSFTNGP14.phx.gbl...
>
|||you do not have to have the same operating system. It is adviseable to use
NT server operating systems, ie NT 4., Win2k, and Win2003.
I think you can get away with using NT workstation, or XP professional but
there can be a connection limit you can bang into.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"soc" <zxc0@.yahoo.com> wrote in message
news:egaFkBT4EHA.2540@.TK2MSFTNGP09.phx.gbl...
> Thanks Hilary,
> I will look into bi-directional tranasactional replication.
> -Presumably it would make sense to use w2003 on both machines?
> -Do the operating systems on 2 replicating machines have to be the
> same?
> Any advice would be appreciated.
> Thanks SOC.
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:O8z42MR4EHA.4092@.TK2MSFTNGP14.phx.gbl...
>