October 12, 2001 at 7:52 am
Hi,
There are two Windows2k systems running SQL Server2K.
The database on the first system is in read/write mode with very frequent writes.
and is replicated on the second system using a block level replication engine.
The second system runs SQL Server2K on the replicated Database in read only mode.
(It is not using SQL Server Replication)
Clients are doing transactions and updates to the database on the first SQL
server. Different set of clients are doing transcations (reads only and noupdates
or new transactions) on the 2nd server. While this is happening, in parallel, the
block transfer engine is shipping blocks of new (updated) raw data from the 1st
server to the 2nd server.
Since the SQL server on the 2nd system has no way of knowing that the raw data
on the disk is changing, he always give stale data which is in its buffers. Is there
a way we can flush all the pages and buffers of the database and reread everything
from the cache, without the database going offline, that is while the connections
are active?
Thanks in advance.
October 12, 2001 at 11:11 am
Not that I know of. Why would you not use replication? Replication solves all of this at very little cost.
Andy
October 12, 2001 at 11:18 am
We already tried the SQL Replicator but it didn't work under heavy transactions on the 1st SQL Server2k eventhough the 2nd SQL Server2k database is read only.
Even Microsoft is not recommending SQL Replicator for high OLTP system.
Thanks
October 12, 2001 at 11:20 am
How many transactions are we talking? Replication usually only generates a load of 5-10% on the machine, less if multi processor. What hardware configuration, SQL version?
Andy
October 12, 2001 at 11:29 am
The hardware is Dell PowerEdge 6400 using Dell storage and running over 40 databases.
8GB of RAM....
Online transactions half a million.
thanks
October 12, 2001 at 11:32 am
Thats what, 4 processors? Half a million transactions...per minute? Day?
October 12, 2001 at 11:34 am
yes it is 4processors and half a million transactions perday.
thanks
October 12, 2001 at 12:20 pm
Guess it depends on what you call a transaction, but that doesnt seem like too much for replication. Figuring it all happens in 8 hours, that breaks down to an average of about 20 transactions per second.
Did you try replication at all, or just decide not to use it?
Andy
October 12, 2001 at 12:21 pm
Another option would be to split the load across multiple servers, using distributed partitions maybe. Then all data would be live.
Andy
October 12, 2001 at 12:48 pm
please can brief how to split the load across multiple servers, using distributed partitions.
thanks
October 12, 2001 at 3:51 pm
Look them up in BOL, but basically you split your table by the PK into 2 parts. If your table is orders using an OrderID (int) as a PK, you place all orders that are even on one server, and all orders that are odd on another server.
Then you define a view on each server (same view name) that unions all data from both servers. No matter which server the users connect to, they will be able to insert/update/delete using the view.
Check "Federated servers" in BOL.
Steve Jones
October 18, 2001 at 12:32 am
Yes see the BOL DBCC commands below called
DBCC DROPCLEANBUFFERS and DBCC FREEPROCCACHE.
hope this helps...
DBCC FREEPROCCACHE
Removes all elements from the procedure cache.
Syntax
DBCC FREEPROCCACHE
Remarks
Use DBCC FREEPROCCACHE to clear the procedure cache. Freeing the procedure cache would cause, for example, an ad-hoc SQL statement to be recompiled rather than reused from the cache.
Result Sets
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Permissions
DBCC FREEPROCACHE permissions default to members of the sysadmin and serveradmin fixed server role only, and are not transferable.
©1988-2000 Microsoft Corporation. All Rights Reserved.
DBCC DROPCLEANBUFFERS
Removes all clean buffers from the buffer pool.
Syntax
DBCC DROPCLEANBUFFERS
Remarks
Use DBCC DROPCLEANBUFFERS to test queries with a cold buffer cache without shutting down and restarting the server.
Result Sets
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Permissions
DBCC DROPCLEANBUFFERS permissions default to members of the sysadmin fixed server role only, and are not transferable.
©1988-2000 Microsoft Corporation. All Rights Reserved.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply