May 6, 2009 at 8:09 am
I have two multicore servers
The first (server1) is used to import (and process) various data sources. This server does the most of the SQL processing getting the data into its most useable format
The second (server2) holds the data for web access. We have various webapplications and reporting services websites that are being used all the time throughout the day by hundreds of users.
We update the data on an intra day basis (currently every 2 hours but we are moving to update every half hour)
I am attempting to work out the "best" way to truncate a data table on server2 and insert the data from the table on server1 without any users being able to run select statments between truncating the data and inserting the data. Ideally this would delay whatever connection they are trying to achieve until the insert statement has completed.
I believe the following code will work (but have not tested it yet) however this does not allow us to use the fast load table feature of SSIS.
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
GO
BEGIN TRANSACTION;
GO
TRUNCATE TABLE ABC
GO
INSERT INTO ABC
SELECT * FROM [server1].[database1].[dbo].ABC
GO
COMMIT TRANSACTION;
GO
Can anyone think of a better way?
May 7, 2009 at 8:20 am
The best form in my opnion, is to create a Job to execute every 1 hour, using SSIS (export, import), informing that the table should be initialized, in this case is faster and you don´t need to create a Linkserver.
May 8, 2009 at 6:43 am
If you are into partitioning, which I hope you are with your kind of question, I'd recommend that you should look into SWITCHing in your table data. Then dropping your table you switched out... This is really quick and will have minimal effect on your users...
Look for example at this:
And think of your Table as a partitioned table with only One partition (except during the split second before switching out the old data)..
This can be done in an SQL Task and you could wrap it in a transaction to not have any users mucking with your data during the milliseconds your two metadata operations are running)
May 9, 2009 at 10:53 pm
just an idea, and i don't know if its a good one or not, but couldn't you deny access to the tables to everyone except the account running the update?
declare @str_login_name varchar(255)
declare @str_cmd varchar(4000)
declare cur_logins_to_deny CURSOR FOR
SELECT [name] from sys.logins where [name] not in ('account to perform updates');
OPEN cur
FETCH .. into @str_login
WHILE @@FETCH_STATUS = 0
BEGIN
SET @str_cmd = 'DENY SELECT ON [dbo].[table_name] TO ' & quotename(@str_login)
sp_executesql @str_cmd
SET @str_cmd = 'DENY DELETE ON .... etc
FETCH NEXT FROM ... into @str_login
END
close
deallocate
or you could deny to public and grant to just that account, then grant back to the public after the load is complete.
i would think that would do what you need to do, but i haven't tested it out.
best of luck,
Steve
May 11, 2009 at 2:46 am
This is an application problem, not a SQL Server problem.
The traditional answer to this problem is to create another table to act as a application lock table.
You update the application lock table to say if the rest of the application data is available for use or is in a restricted state. All the other parts of your application then need to check this table before access the erst of the data.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
May 11, 2009 at 3:42 am
Ed,
If that if your traditional answer I would say traditionally it is wrong 🙂 What you then are assuming is that your table is only going to be accessed by Applications 100% under your control. This might happen during a short period after the initial creation of the table but as time goes by it will be less and less likely.
The solution should be as application independent as possible...
I don't agree with DENYing approach either because that would cause other issues like 'false/fake' Permission errors that shouldn't happen..
Minimize the loading time and make all queries Wait (on the lock) until the table has the correct data I believe is the best approach. To minimize the time as much as possible you should make the loading a metadata operation and not any type of DML operation.
May 11, 2009 at 3:56 am
There are many ways to get the status of the application lock table honoured by processes that access the data.
If your access is via an application where you have authorship control, then the necessary code can be added to the application.
If the access is via ad-hoc queries, then you provide acces to the data via a view or set of views, not direct to the base table. In this situation, each view joins the fact table with the lock table, with a predicate of lock.status='OPEN'.
Alternatively, if your need is to just prevent access for the duration of a transaction, you can use Snapshot isolation, so that all readers see the table as it was before the updating transaction started. This approach needs no change to any code apart from ensuring that all update logic is wrapped within a single transaction.
There may be other approaches to resolve this problem.
You need to look at the business requirements to decide which solution best fits your needs.
If you need your readers to only access the 'officially current' data, then you may need some form of application lock table so that access can be prevented if the load is late or fails.
If all you need to do is prevent readers and updaters blocking each other, then this could be done with Snapshot isolation, or simply by scheduling the load to complete before (say) 7am and telling users to avoid access before this time.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
May 12, 2009 at 9:08 pm
Perhaps you should look at the TransactionOption and IsolationLevel properties in SSIS (2005).
They seem to be what you're after to:
* Use SSIS
* Prevent Users from reading incomplete data
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply