ASG DB replication

[8i5]

Is anyone working in ASG in multiple physical locations over slow wan links? We find performance of the application (particularly startup time) to be poor when the db is remote. Is there any recommendation for running multiple instances of the db and replicating between them with SQL server replication?

Any suggestions or experiences from others working in a multisite environment would be appreciated!

[DevOma]

First at all I can give you some SQL statements to spped up the startup (is part of Patch4, but only for new DB installation - upgrade will be done with some other DB stuff in a future version) - you can execute it, it will be checked in future updates so it makes no difference - but it should speed up the start of the application

Database replication should be no problem - but we don't have any special suggestions - Replication depends almost on how to use the databases...


Perhaps you should check if you have large amount of log data - you can shrink that data in Tools=>Settings=>Logs.

Try to execute the following statements on your database


IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[Logs]') AND name = N'IX_Logs_2')
DROP INDEX [IX_Logs_2] ON [dbo].[Logs] WITH ( ONLINE = OFF )
GO

CREATE NONCLUSTERED INDEX [IX_Logs_2] ON [dbo].[Logs]
(
[Timestamp] DESC
) ON [PRIMARY]
GO


IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[ItemPropertiesUsage]') AND name = N'IX_ItemPropertiesUsage_2')
DROP INDEX [IX_ItemPropertiesUsage_2] ON [dbo].[ItemPropertiesUsage] WITH ( ONLINE = OFF )
GO

CREATE NONCLUSTERED INDEX [IX_ItemPropertiesUsage_2] ON [dbo].[ItemPropertiesUsage]
(
[RolePropertyId]
) ON [PRIMARY]
GO


IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[ItemSort]') AND name = N'IX_ItemSort_1')
DROP INDEX [IX_ItemSort_1] ON [dbo].[ItemSort] WITH ( ONLINE = OFF )
GO

CREATE NONCLUSTERED INDEX [IX_ItemSort_1] ON [dbo].[ItemSort]
(
[UserId] ASC
) ON [PRIMARY]
GO

IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[Items]') AND name = N'IX_Items_2')
DROP INDEX [IX_Items_2] ON [dbo].[Items] WITH ( ONLINE = OFF )
GO

CREATE NONCLUSTERED INDEX [IX_Items_2] ON [dbo].[Items]
(
[UserId] ASC
) ON [PRIMARY]
GO

[8i5]

We have 90 days and max 50 entries enabled on all the logging options. Is that too much?
I ran the sql commands but it hasn't improved startup time.

What do you mean by "Replication depends almost on how to use the databases..." ? We have installed the ASG system with mostly default settings.

[DevOma]

Log size is ok - had customers with about 1 million log entries :-)

There are 2 main options in replication - snapshot replication and transaction replication - you can replicate in both directions or you have one main database and only replicate the changes to other ones. I'm not a pure SQL specialist and can't answer you all questions about replication.

What is your startup time? When running the app it should be almost fast, because most (nearly all) data is cached (loading during startup) - to prevent write access you could deactivate all logs

[8i5]

Startup time is about 45 seconds.

[8i5]

Most of the wait time is spent with the message "Loading data".

Is there something wrong with the cache settings - I noticed the automatic refresh cache (in min) was set to 0 - is this correct? I tried changing it to 60 but it made no difference.

[DevOma]

Refresh cache = 0 => No automatic refresh will be executed!

"Loading data" is when all database data is read and set to the cache - I do not know how many objects you have in your database - with Patch5 (next week) you will be able to MultiEdit your objects again - and you could try to "delete settings" you do not need and set "common properties" to a root object and only inherit these values to all childs - that should decrease the data amount to load...

I give you an example - at the moment (after migration) all RDP-Properties are stored for each connection - but if you have mostly the same values it makes sense to set these values only once on a folder (or root), delete all RDP Properties of the connections - set to Inherit - you will be able to do with only one action via MultiEdit - or if you do not use SSH, VNC or something else - you could remove those roles and properties from folders and connections... that will decrease the data to load and should speed up your initial start time...