I would like to run a query that finds prime numbers, and I would like to run it forever.
DECLARE @RangeStart INT = 1, @RangeEnd INT = 100000; DECLARE @HowMany INT = CEILING((@RangeEnd - @RangeStart + 1) / 2.0); ;WITH frst AS ( SELECT tmp.thing1 FROM (VALUES (0), (0), (0), (0), (0), (0), (0), (0), (0), (0)) tmp(thing1) ), scnd AS ( SELECT 0 AS [thing2] FROM frst t1 CROSS JOIN frst t2 CROSS JOIN frst t3 ), base AS ( SELECT TOP( CONVERT( INT, CEILING(SQRT(@RangeEnd)) ) ) ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS [num] FROM scnd s1 CROSS JOIN scnd s2 ), nums AS ( SELECT TOP (@HowMany) (ROW_NUMBER() OVER (ORDER BY (SELECT 1)) * 2) + (@RangeStart - 1 - (@RangeStart%2)) AS [num] FROM base b1 CROSS JOIN base b2 ), divs AS ( SELECT [num] FROM base b3 WHERE b3.[num] > 4 AND b3.[num] % 2 <> 0 AND b3.[num] % 3 <> 0 ) SELECT given.[num] AS [Prime] FROM (VALUES (2), (3)) given(num) WHERE given.[num] >= @RangeStart UNION ALL SELECT n.[num] AS [Prime] FROM nums n WHERE n.[num] BETWEEN 5 AND @RangeEnd AND n.[num] % 3 <> 0 AND NOT EXISTS (SELECT * FROM divs d WHERE d.[num] <> n.[num] AND n.[num] % d.[num] = 0 );
So we can easily generate a list of all prime numbers from 0 to 100.
But let's say I want the query to run for the next 20 years, so I set as limit a very large number:
DECLARE @RangeStart INT = 1, @RangeEnd INT = 100000000000000000000000000000000000000000000000000;
And here is where the problem comes: how can I maintain this query for the next 20 years?
Because the Sieve of Eratosthenes has a peculiarity:
If it gets interrupted you have to restart from the beginning.
So my questions start here:
- How can I manage to change the CPU, upgrade RAM, change Hard Drive, etc...
- How can I manage a failover disaster scenario in order to avoid that script to stop?
- Will the failover guarantee that the query won't stop?
- Cloud redundancy and failover to different providers (Azure / AWS / GCE) will this assure that the script won't stop?
- What if the failover is from Azure to AWS in 2 different parts of the globe? Will this failover keep the stored procedure running?
And now the Disaster Recovery part:
- Can I take a backup, let's say every month, of the status of that stored procedure, and eventually resume from that image?
- If I run the stored procedure on a virtual machine and I take snapshots, can I resume the stored procedure?
I know for sure that someone is doing this: this online database of factorized prime numbers http://factordb.com/status.php has managed to increase from 200MB (in 2014) to nearly 800MB today (2019).