Mobilink 12.01 and SQL Server 2008

[Former Member]

I have a DBA that won't grant "view server state" to the database user that I use to start the Mobilink Synchronization Server.  My question is there a work around since I saw that this right is a requirement?  Also, for curiosity what is being performed internally to require this right at the server level?

Thanks,

[jeff_albion · Accepted solution]

Hi Kevin,

Since Microsoft SQL Server 2005+ supports snapshot isolation, MobiLink needs to be careful about how it generates the 'last download timestamp' on these systems. Consider the following problem:

1. A synchronization comes in to synchronize and completes. Last download time is T1.

2. Another connection to MSSQL is opened and starts a snapshot isolation transaction. Inserts a record with the last_updated timestamp column set to the current time, T2.

3. A second synchronization comes in to synchronize at T3.

4. The snapshot isolation transaction commits with time T2 on the record.

5. A third synchronization comes in at T4, missing the record (since T2 < T3 < T4).

To resolve this problem, MobiLink will instead look for open transactions on the consolidated server and use the oldest transaction's start time as the last download time instead. You can use the "-dt" MobiLink server switch to control whether MobiLink only looks in the current database or in all databases on Microsoft SQL Server for this infromation.

If MobiLink is only looking at the current database (-dt), it will use the following query:

SELECT CONVERT(CHAR(50),"
    "CASE WHEN MIN(at.transaction_begin_time) IS NOT NULL "
    "THEN MIN(at.transaction_begin_time) ELSE GETDATE() END,21) "
    "FROM sys.dm_tran_active_transactions at, sys.dm_tran_database_transactions dt "
    "WHERE at.transaction_id=dt.transaction_id AND at.transaction_type=1 AND "
    "at.transaction_state=2 AND dt.database_id=DB_ID()

If MobiLink is looking at all databases on the SQL Server system we remove the "AND dt.database_id=DB_ID()" predicate.

Notably, "sys.dm_tran_active_transactions" and "sys.dm_tran_database_transactions" are both "Transaction Related Dynamic Views and Functions" in Microsoft SQL Server, which are locked by the "VIEW SERVER STATE" privilege.

Regards,

Jeff Albion

SAP Active Global Support