Moving some DBs to another server
-
Hi!
I have a database, most changes to which are recorded to another database with additional information: who did, when and so on. This is done by triggers on original database.
To move some load from the central server, I am thinking about moving the database where changes of the original DB are recorded, to another server.
So all the triggers work will have to be done over network (1Gbit)
The original DB is 8Gb size, the "log" DB is about 17Gb. The servers RAM is 4Gb. (It has also some other DBs)
The problem I face is: due to the lack of RAM (Significant RAM increase is not possible on the server) the procedures are often moved out of cache and need to be compiled each time someone runs them. When the procedure is used often and by many users we have them locked for the time of compilation. So other users have to wait, that causes slow application reaction. Cache Hit Ratio counter is about 75% on the server.
By moving the "log" database, I hope to redistribute the servers RAM to the main database
and to increase the Cache Hit Ratio.
I would like to hear your opinions on this matter: how effective, from the point of view of productivity and reliability will be a moving "log" database to another server?
Thanks.
-
Hi Roust_m,
IMHO, introducing over-the-network distributed transactions is going to make your problems worse. And, what do I base this opinion on? Just gut feel, so you're well within your rights to send this post to the recycle bin.
BTW. Doesn't a trigger operating across 2 databases on the same server introduce a sort of distributed transaction anyway? I thought it did, but I could be extremely wrong.
Personally, I'd bring the log/audit database into the live database, possibly segregating the table onto filegroup on another drive. If the size of the the audit/log table is an issue, I'd do an out-of-hours batch archive of older rows to another database.
Cheers,
- Mark
Cheers,
- Mark -
2mccork:
quote:
Personally, I'd bring the log/audit database into the live database, possibly segregating the table onto filegroup on another drive. If the size of the the audit/log table is an issue, I'd do an out-of-hours batch archive of older rows to another database.This could be a solution, if to keep only one day changes in audit/log database.
Is there a way to figure out how much memory eats each database on a server?
-
"dbcc memusage" gives a breakdown of buffer used by DBs, Objects. However, it's not documented.... well it IS documented, but here's the BOL entry...
quote:
Removed; no longer supported or available. Remove all references of DBCC MEMUSAGE and replace with references to these Performance Monitor counters.So, considering the commands does return results, is the above a disclaimer in case it returns incorrect results??
Cheers,
- Mark
Cheers,
- Mark
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply