Please Note: Do not create any further objects, apart from Synonyms in the new SynergyOne databases

When migrating to Single Database, synonyms have been created to preserve third party integrations. Synonyms will only be included for migrations from v67. For new clients implemented in v68, synonym objects will not be created.

A synonym is a reference to the new single database structure. For example, a MSQuery created in v67 which had a reference to the table of SynergyOneFinance.dbo.Debtors will continue to work as it would refer (by SQL) to finance.Debtors after the migration have been conducted and synonyms have been created.

Example uses of synonmys to preserve third party integrations:

It is recommend that any new direct queries to the database is now created with the reference to the new database structure (using dbo, finance, media) for the schemas instead of database names.

After migration to v68, you will be prompted with the following- do not remove ODBC entries if you wish to maintain legacy database connectivity.

Missing permissions?

If you have legacy reports or MS Queries, in some cases you may experience users who have no access to the Synonym databases.

Select the database server
Select Security > Logins
Select the affected user, right click on the user and select Properties
Under User Mapping, select access to the required synonym databases as per the following diagram

Source

https://docs.microsoft.com/en-us/sql/relational-databases/synonyms/synonyms-database-engine

Tracking whether the Synonym databases are being used

Given that the synonym databases are only there for compatibility with third party access, it is important to be able to identify if they are actually being used. This section describes two methods. Method 1 simply identifies if the Synonym databases are being used... This method is unable to identity who is querying or when, the second method is to use the SQL Profiler which can identity who is accessing the database

Method 1 - querying the SQL Server Plan Cache

Method 1 searches the SQL Server plan cache to see if there is any activity on the Synonym databases. PLEASE NOTE that this method is not precise as the plan cache is volatile and is cleared whenever the SQL server is restarted. It can also be cleared manually by issuing the SQL command DBCC FREEPROCCACHE

The attached script (CheckSynonymDatabaseAccess.sql) will query the plan cache and store the results in scratch.SQLPlanCacheForSynonymAccess. You can setup an agent job to run this script over a period of time if you wish.

Please note also that this simply indicates the synonym databases are being used - it does not indicate who is actually using them, you may be able to work it out based on the queries being stored, otherwise you might want to use the profiler method.

Method 2 - Using SQL Server Profiler

The SQL Server Profiler method allows you to determine who is accessing the Synonym Databases but it does have a performance overhead on the SQL server. When you begin your new trace be sure to add the following selections

Go to the 'Events Selection' tab and check 'Show all events' and 'Show all columns'.
Press 'the Column Filters' button, select 'Database' under 'Like' enter SynergyOne% (The percent indicates any database starting with SynergyOne - eg SynergyOne, SynergyOneFinance and SynergyOneMedia etc)
From the events list expand the 'Stored Procedures' section and tick the SP:Starting option
Also expand the 'TSQL' section and tick the SQL:StmtStarting option

You can now run your trace. The resultant profiler screen will identity any activity on the synonym databases. It is up to you how long you wish to run it for. If you wish to leave it running for a long time I would suggest investigating a server-side trace which is beyond the scope of this article in it's current form.