How To Time Shift Microsoft SQL Server Database
This whitepaper will delve into the background of SQL Server software, the basics of its date and time functionality, and how-to time travel SQL Server accordingly with TimeShiftX®.
What is Microsoft SQL Server?
SQL server is a relational database management system (DBMS) provided by the company Microsoft and was first released in 1989. It provides a slew of functionality and services but the main purpose is to store and retrieve data, just like other popular DBMS software such as Oracle, MySQL, IBM DB2, Sybase, etc. The last three major SQL versions were 2014, 2016, and the newly released 2017 which has brought Linux support [1].
Data Types
Below are the existing date and time data types in SQL. These data types define the type of data a column can contain and have various formats, byte storage size, precision, and accuracy. To see the full specifications, read the Microsoft SQL documentation [2].
|
These data types all follow the Gregorian calendar, which is the most used calendar in the world and the successor to the Julian calendar. The Gregorian calendar’s major innovation was the enhancement of the formula to calculate leap years which improved the calendar accuracy. [3]
Function Calls
SQL function calls enable users to perform various actions and receive the results as a value. For Date and Time functions, SQL pulls the date and time values directly from the Windows or Linux Operating System. These functions offer many actions such as to modify, compare, validate, and extract the date and time values. The basic functions that return the full date and time value fall under two categories, high and low precision. The difference being the return values for low precision shows a granularity of 3 significant digits after the decimal point (to the thousandths place), whereas high precision goes beyond that.
| High Precision | Low Precision |
| SYSDATETIME(); | GETDATE(); |
| SYSUTCDATETIME(); | GETUTCDATE(); |
| SYSDATETIMEOFFSET(); | CURRENT_TIMESTAMP; |
Time Travel SQL Database
Without a tool, your options for time shifting SQL are quite bleak. You can attempt to manipulate data records, the OS system, or both. Either is not recommended due to the cumbersome amount of work, damage to the system, and lack of proper automation and test coverage. In addition, your domain authentication protocol, like Active Directory, will block all attempts to time travel and thus prevent you from executing any temporal testing.
With TimeShiftX, you can time travel SQL seamlessly by creating a “virtual clock” for the SQL server via a simple Command Line or GUI action. This will enable you to time travel and run any future or past date simulation on the database.
To do so, simply create a TimeShiftX virtual clock for SQL to time travel. The most popular methods are to create a virtual clock for the SQL server binaries (e.g. sqlservr.exe) or for the service account that SQL Server runs as. This is the same for all operating system platforms, whether Windows, Linux, Solaris, AIX, HP-UX, etc. Afterwards, any SQL action will reflect the virtual clock value. If you remove the “virtual clock”, then the SQL server actions will instantly revert back to the system’s present date and time.
To initiate time travel you will employ a TimeShiftX command or GUI.
C:\> timeshiftx Windows terminal command
$ timeshiftx Linux/UNIX terminal command
TimeShiftx Windows GUI
SQL will now show the future or past virtual clock date.
Figure 1: Low Precision Functions time traveling to 2016.
Figure 2: High Precision Functions time traveling to 2016.
Figure 3: Low Precision Functions time shifting to 2030.
Figure 4: High Precision Functions time shifting to 2030.
Benefit
By date shifting your database, you can run all your forward or back date testing scenarios for any industry scenarios such as financial control testing, policy life cycle testing, enrollment validation, etc.
[1] https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-setup#supportedplatforms
[2] https://docs.microsoft.com/en-us/sql/t-sql/functions/date-and-time-data-types-and-functions-transact-sql
[3] https://www.timeanddate.com/calendar/gregorian-calendar.html
