June 20, 2018 at 4:36 am
Basically, we have a 2 tables. One is being used by the app for querying (active), and one is used by ETL to prepare the next data set (staging). Once the staging table is all loaded, logically, they rename the table from staging to active and active to staging (finding some issue (error/blocking) here), so the app sees the freshest data.
How can we avoid online rename that tables? Is there any other alternative way?
June 20, 2018 at 5:00 am
We have used SYNONYMs for this purpose.
Synonyms (Database Engine)
CREATE SYNONYM (Transact-SQL)
Use 2 identical tables.
Load Table1 and point the synonym at it.
Load table 2 - Then redirect the synonym.
App and procs always see the synonym.
You then just need to make sure you are loading the unused table
June 20, 2018 at 5:39 am
dudekula.kareemulla - Wednesday, June 20, 2018 4:36 AMBasically, we have a 2 tables. One is being used by the app for querying (active), and one is used by ETL to prepare the next data set (staging). Once the staging table is all loaded, logically, they rename the table from staging to active and active to staging (finding some issue (error/blocking) here), so the app sees the freshest data.
How can we avoid online rename that tables? Is there any other alternative way?
I agree with DesNorton. Use synonyms (or "pass-through" views). It's nasty fast and they'll wait until someone is done using them.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 20, 2018 at 7:48 am
Another fast option is partition switching.
Here's a quick example...USE CodeTest
GO
/* Create a couple of tables to test with. */
CREATE TABLE dbo.TableProd (
SomeID INT NOT NULL IDENTITY(1,1)
CONSTRAINT pk_TableProd PRIMARY KEY CLUSTERED,
SomeValue UNIQUEIDENTIFIER NOT NULL
);
GO
CREATE TABLE dbo.TableStage(
SomeID INT NOT NULL IDENTITY(1,1)
CONSTRAINT pk_TableStage PRIMARY KEY CLUSTERED,
SomeValue UNIQUEIDENTIFIER NOT NULL
);
GO
/* Note that both tables MUST have identical structures. This includes all indexes.*/
--===================================================
/* Add some data to both tables to simulate a real world situation where
the prod table has existing (stale) data and the staging table has the
new data that needs to become the new prod data. */
WITH
cte_n1 (n) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) n (n)),
cte_n2 (n) AS (SELECT 1 FROM cte_n1 a CROSS JOIN cte_n1 b),
cte_Tally (n) AS (
SELECT TOP (500000)
ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM
cte_n2 a CROSS JOIN cte_n2 b
)
INSERT dbo.TableProd (SomeValue)
SELECT NEWID() FROM cte_Tally t;
WITH
cte_n1 (n) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) n (n)),
cte_n2 (n) AS (SELECT 1 FROM cte_n1 a CROSS JOIN cte_n1 b),
cte_Tally (n) AS (
SELECT TOP (1000000)
ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM
cte_n2 a CROSS JOIN cte_n2 b
)
INSERT dbo.TableStage (SomeValue)
SELECT NEWID() FROM cte_Tally t;
--===================================================
/* Do a quick check of the "before" values... */
SELECT TOP (10) * FROM dbo.TableProd tp;
SELECT TOP (10) * FROM dbo.TableStage ts;
--===================================================
/* make the actual switch... */
BEGIN TRANSACTION /* Assuming that we want to avoid the possibility of an empty prod table, use a transaction block so that everything will either succeed together or fail together. */
TRUNCATE TABLE dbo.TableProd; /* The "destination table MUST be empty for the partition switch to work. */
ALTER TABLE dbo.TableStage SWITCH TO dbo.TableProd; /* That actual partition switch. */
COMMIT TRANSACTION;
--===================================================
/* Now, let's check that the prod table now has the Staging data and that the staging table is empty. */
SELECT TOP (10) * FROM dbo.TableProd tp;
SELECT TOP (10) * FROM dbo.TableStage ts;
--===================================================
/* ... and then let's cleanup after ourselves... */
DROP TABLE dbo.TableProd;
DROP TABLE dbo.TableStage;
Note that the entire solution is just these four tines... and the nature of both commands will allow it to work in milliseconds, even on very large tables.BEGIN TRANSACTION
TRUNCATE TABLE dbo.TableProd;
ALTER TABLE dbo.TableStage SWITCH TO dbo.TableProd;
COMMIT TRANSACTION;
The rest of that script is just test setup.
June 20, 2018 at 8:42 am
DesNorton - Wednesday, June 20, 2018 5:00 AMWe have used SYNONYMs for this purpose.Synonyms (Database Engine)
CREATE SYNONYM (Transact-SQL)Use 2 identical tables.
Load Table1 and point the synonym at it.
Load table 2 - Then redirect the synonym.
App and procs always see the synonym.You then just need to make sure you are loading the unused table
In this case, I need to change ETL job every time to point the empty table (Table1 or Table2) each time. I don't want to touch always that ETL job.
Also in prod env we don't have permission to drop any objects (synonyms).
June 20, 2018 at 10:42 am
dudekula.kareemulla - Wednesday, June 20, 2018 8:42 AMDesNorton - Wednesday, June 20, 2018 5:00 AMWe have used SYNONYMs for this purpose.Synonyms (Database Engine)
CREATE SYNONYM (Transact-SQL)Use 2 identical tables.
Load Table1 and point the synonym at it.
Load table 2 - Then redirect the synonym.
App and procs always see the synonym.You then just need to make sure you are loading the unused table
In this case, I need to change ETL job every time to point the empty table (Table1 or Table2) each time. I don't want to touch always that ETL job.
Also in prod env we don't have permission to drop any objects (synonyms).
You shouldn't need to change the ETL job. Have one synonym that the regular users will know about, and a second synonym that the ETL job knows about. The users and ETL code will never need to know what physical table their synonym is pointing at.
June 20, 2018 at 10:53 am
Chris Harshman - Wednesday, June 20, 2018 10:42 AMdudekula.kareemulla - Wednesday, June 20, 2018 8:42 AMDesNorton - Wednesday, June 20, 2018 5:00 AMWe have used SYNONYMs for this purpose.Synonyms (Database Engine)
CREATE SYNONYM (Transact-SQL)Use 2 identical tables.
Load Table1 and point the synonym at it.
Load table 2 - Then redirect the synonym.
App and procs always see the synonym.You then just need to make sure you are loading the unused table
In this case, I need to change ETL job every time to point the empty table (Table1 or Table2) each time. I don't want to touch always that ETL job.
Also in prod env we don't have permission to drop any objects (synonyms).You shouldn't need to change the ETL job. Have one synonym that the regular users will know about, and a second synonym that the ETL job knows about. The users and ETL code will never need to know what physical table their synonym is pointing at.
Chris is correct. The whole purpose of repointing the synonyms is so that you DON'T have to change code every time you want to do the flop of the tables.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 20, 2018 at 7:06 pm
p.s. It also seems like an easy thing to test. Create a new table with a computed column on it and add a million rows of fake data. See if it breaks. As the old saying goes, "One good test is worth a thousand expert opinions". 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
June 20, 2018 at 11:14 pm
Jeff Moden - Wednesday, June 20, 2018 10:53 AMChris Harshman - Wednesday, June 20, 2018 10:42 AMdudekula.kareemulla - Wednesday, June 20, 2018 8:42 AMDesNorton - Wednesday, June 20, 2018 5:00 AMWe have used SYNONYMs for this purpose.Synonyms (Database Engine)
CREATE SYNONYM (Transact-SQL)Use 2 identical tables.
Load Table1 and point the synonym at it.
Load table 2 - Then redirect the synonym.
App and procs always see the synonym.You then just need to make sure you are loading the unused table
In this case, I need to change ETL job every time to point the empty table (Table1 or Table2) each time. I don't want to touch always that ETL job.
Also in prod env we don't have permission to drop any objects (synonyms).You shouldn't need to change the ETL job. Have one synonym that the regular users will know about, and a second synonym that the ETL job knows about. The users and ETL code will never need to know what physical table their synonym is pointing at.
Chris is correct. The whole purpose of repointing the synonyms is so that you DON'T have to change code every time you want to do the flop of the tables.
Ok I agreed, code changes not there if we use synonyms. But here my problem is to repoint the synonyms, as I mentioned earlier we don't have drop permission to any objects.
If I want to repoint the synonyms definitely we need to drop and recreate the synonyms, since we can't alter the synonyms.:ermm:
June 21, 2018 at 4:52 am
dudekula.kareemulla - Wednesday, June 20, 2018 11:14 PMOk I agreed, code changes not there if we use synonyms. But here my problem is to repoint the synonyms, as I mentioned earlier we don't have drop permission to any objects.
If I want to repoint the synonyms definitely we need to drop and recreate the synonyms, since we can't alter the synonyms.:ermm:
Create a proc that does the drop and create.
Have the job exec the proc on successful load.
June 21, 2018 at 6:51 am
dudekula.kareemulla - Wednesday, June 20, 2018 11:14 PMOk I agreed, code changes not there if we use synonyms. But here my problem is to repoint the synonyms, as I mentioned earlier we don't have drop permission to any objects.
If I want to repoint the synonyms definitely we need to drop and recreate the synonyms, since we can't alter the synonyms.:ermm:
I'd think that the ETL process in the production environment would be running not as you but as the SQL Server Agent user, or some other proxy account that the administrators set up. That's the user that would need the ability to recreate synonyms, not you.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply