cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
782
Views
12
Helpful
5
Replies

TMS databse Collation setting - Accent Sensitive or not?

david.reid66
Level 1
Level 1

Hi Guys,

I need clarification on the “Collation” settings for the manually created databases for tmsng and tmspe.

The TMS 14.3.2 install guide quotes a collation for tmsng of: Latin1_General_CI_AI

The TMSPE 1.1 deployment guide quotes a collation for tmspe of: Latin1_General_CI_AS

Why is tmsng AI (Accent Insensitive) and tmspe AS (Accent Sensitive), shouldn't they be both the same? In the UK we tend to remove accents in any case turning Ü into U but I'd like a definitive answer one way or the other.

Thanks,

David Reid

5 Replies 5

daleritc
Cisco Employee
Cisco Employee

Two different applications and two different databases...meaning go with what's documented for each

And it really doesn't matter since a query will  never span the two databases.
/dale

Hi Dale,

My customer wants to set the database collation (for both tmsng & tmspe) to:

SQL_Latin1_General_CP1_CI_AI

They want to use the above collation as it’s their default and they think that the ones specified may cause a problem with some of their maintenance routines.

Can you confirm if this would be the case?

Also, I’ve advised them (as per the TMS 14.3.1 Install guide):

If manually creating the database, the following settings are required for tmsng:

  • ALLOW_SNAPSHOT_ISOLATION must be set to ON
  • READ_COMMITTED_SNAPSHOT must be set to OFF

The customer asked:

“You are asking for row versioning to be enabled by setting “allow_snaphot_isolation” to on for these databases. Is this necessary as obviously it means that multiple versions of your data will be held in TEMPDB until all active transaction that require them are complete, which can obviously affect TEMPDB database size”.

So is this setting necessary?

Thanks,

David

If I read documentations I try to follow RFC2119:

1. MUST   This word, or the terms "REQUIRED" or "SHALL", mean that the
   definition is an absolute requirement of the specification.

TMS release notes:

Database snapshot isolation

ALLOW_SNAPSHOT_ISOLATION is now On by default for the tmsng database. Administrators setting up the database manually must ensure that this setting is enabled. READ_COMMITTED_SNAPSHOT must still be set to Off.

Guess no further discussion needed.

The point is that things might work with different settings, but if not then you are in trouble where TAC can

not really help you. So yes, please follow the guides.

Please remember to rate helpful responses and identify helpful or correct answers.

Please remember to rate helpful responses and identify

+1 on what Martin says

Thanks for the clarification guys.

The customer is asking the "why" and "what if" questions a lot and I'm no SQL expert hence asking you guys

I've already advised him to "do what the guide says" but as you see from the questions, he wants to know if it'll be "ok" using his collation for example.

As you've already stated, if there is an issue TAC will (quite rightly) say do what it says in the guide.

I'll let you know if there are any more follow up questions.

Thanks again.

DR

Getting Started

Find answers to your questions by entering keywords or phrases in the Search bar above. New here? Use these resources to familiarize yourself with the community: