Moved from: bobsql.com
Prior to SQL Server 2017 CU4 for Linux (Linux only) the generation of a sequential UUID may not function as expected. SQL Server’s NewSequentialId() calls the Windows API UuidCreateSequential, which is limited in SQLPAL, prior to the CU 4 update.
SQL Server on Linux – CU4 advances the logic in the SQLPAL, in support of UuidCreateSequential, adding a pseudo-mac, random increment, boot id and persisting the value in the /var/opt/mssql/.secrets/instance_id file. The SQLPAL uuid now contains the following.
| Timestamp | Boot Id | Mac Address |
- Timestamp: is initialized, during SQLPAL startup with random data from /dev/urandom. During each invocation of UuidCreateSequential the value is incremented randomly.
- Boot Id: Represents the sequence of the uuid. On Windows the system stores this in the registry and during startup increments the value. For SQLPAL the value is stored in /var/opt/mssql/.secrets/instance_id and incremented when SQLPAL is started.
- Mac Address: Is usually associated with a system component (network card) and for SQL Server on Linux, SQLPAL generates a pseudo-mac using a uuid (uuid_generate) and preserves the value in the instance_id file during the first startup.
Using the preserved pseudo-mac and sequentially incrementing Boot id from the instance_id file along with a random timestamp and random increment activities SQL Server on Linux CU4 produces unique, sequential ids that survive restart.
FAQ
Question: What happens if the instance_id file is lost?
Answer: You should maintain a backup of the instance_id file. However, if the file becomes lost the first startup of SQLPAL will generate and persist new pseudo-mac and boot values. Conflict with existing data is unlikely but the new value may sort before those values stored in the database already.
Question: What if I change the time of the system clock?
Answer: The system time does not impact the uuid generation. The uuid timestamp information is a 64 bit value, randomly generated during SQLPAL startup. Then incremented by a random value.
Question: Won’t incrementing the timestamp portion of the uuid, while keeping the boot id and mac address fixed (during a specific execution) expose me to value rollover?
Answer: Mathematically yes, practically no. To rollover the timestamp value for the same sequence will take decades, at a 100 million uuid creations per second.
Question: If timestamp can’t rollover what about restarts and the boot id?
Answer: Again, mathematically yes, practically no. To rollover of the boot id requires (max unsigned int or 4 billion restarts) of the same SQL instance.
Question: What happens if I restore a database from Server A to Server B and continue to insert values on Server B?
Answer: It does not matter if you are using SQL Server for Linux or Windows because the system dictates the sequential uuid. Server B may be generating uuids that sort before those from Server A. The inserts may occur at the beginning of the index, causing splits and in rare cases you may notice the performance impact.
Question: What happens to my sequential uuids after an availability group failover?
Answer: Same as the restore answer above. The new primary may insert in locations other than the end of the index.
Reference: UUID RFC
Posted at https://sl.advdat.com/3sd5ETOhttps://sl.advdat.com/3sd5ETO