Question on Tranaction Isolation Level.

Source:
SQL2K
SP3
Destination:
SQL2K5
SP2
I have requirements to import data from the source to the destination, and
once on the destination the data will be read only. I also have the
situation where the data owners (not me) may make schema changes on the
source whenever they want to, and do not need to tell me. Most of the time
this would only be widening an existing column, so I'm not all that worried.
This data is only to be grabbed once a night during a slow period.
Options:
Replication: This is out because the data owner will want to widen a column
without needing to consult with me first (I would need to drop replication
first). I'm aware of the changes to 2005 replication, but they won't be
upgrading anytime soon.
DTS/ SSIS: This is out because they will not want to tell me if they widen a
column. Just because they widen the source, that doesn't help my
destination.
Because of all these requirements/ circumstances, I want to just create a
Linked Server and do a Select...Into every night and drop/ re-create the
table on the destination. This will make schema changes on the source
transparent to me. Of course though, I don't want my Select...Into to block
users on the source while the import is occurring. That being said, I was
thinking about setting the Transaction Isolation Level (TIL) to Read
Uncommitted (RU) in the Select...Into. I don't really care if the
consistency is off a bit as it's only updated once a day anyways. This is
how it would look:
Set Transaction Isolation Level Read Uncommitted
select * into T48
from myLinkedServer.database.dbo.T48
So my question is: Will setting the TIL to RU like this actually set it for
the source (the Linked Server) or the destination (my server)? If it does in
fact set it for the source, would NOLOCK work?
TIA, ChrisR1) Can't you use SSIS and force it to drop/create tables each night? That
would probably be most efficient mechanism.
2) You pick up performance if you set the database to ReadOnly when you are
done moving data.
3) I don't believe the set (or other hints) will translate over to the
linked server.
"ChrisR" <ChrisR@.foo.com> wrote in message
news:Oet8c6dAIHA.1204@.TK2MSFTNGP03.phx.gbl...
> Source:
> SQL2K
> SP3
> Destination:
> SQL2K5
> SP2
> I have requirements to import data from the source to the destination, and
> once on the destination the data will be read only. I also have the
> situation where the data owners (not me) may make schema changes on the
> source whenever they want to, and do not need to tell me. Most of the time
> this would only be widening an existing column, so I'm not all that
> worried. This data is only to be grabbed once a night during a slow
> period.
> Options:
> Replication: This is out because the data owner will want to widen a
> column without needing to consult with me first (I would need to drop
> replication first). I'm aware of the changes to 2005 replication, but they
> won't be upgrading anytime soon.
> DTS/ SSIS: This is out because they will not want to tell me if they widen
> a column. Just because they widen the source, that doesn't help my
> destination.
>
> Because of all these requirements/ circumstances, I want to just create a
> Linked Server and do a Select...Into every night and drop/ re-create the
> table on the destination. This will make schema changes on the source
> transparent to me. Of course though, I don't want my Select...Into to
> block users on the source while the import is occurring. That being said,
> I was thinking about setting the Transaction Isolation Level (TIL) to Read
> Uncommitted (RU) in the Select...Into. I don't really care if the
> consistency is off a bit as it's only updated once a day anyways. This is
> how it would look:
> Set Transaction Isolation Level Read Uncommitted
> select * into T48
> from myLinkedServer.database.dbo.T48
> So my question is: Will setting the TIL to RU like this actually set it
> for the source (the Linked Server) or the destination (my server)? If it
> does in fact set it for the source, would NOLOCK work?
> TIA, ChrisR
>|||1) I don't think SSIS will widen the columns on the destination
automatically if they have been widened on the source (perhaps it can
somehow as I don't have a lot of SSIS experience?). Select...Into will take
care of this for me. Sure I can bury my Select...Into code in a SSIS
Package, but I don't think that's what you were getting at.
2) Not really an option, but thanks.
3) This is what I was afraid of.
Thanks!
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13fq948dgolb0a5@.corp.supernews.com...
> 1) Can't you use SSIS and force it to drop/create tables each night? That
> would probably be most efficient mechanism.
> 2) You pick up performance if you set the database to ReadOnly when you
> are done moving data.
> 3) I don't believe the set (or other hints) will translate over to the
> linked server.
>
> "ChrisR" <ChrisR@.foo.com> wrote in message
> news:Oet8c6dAIHA.1204@.TK2MSFTNGP03.phx.gbl...
>> Source:
>> SQL2K
>> SP3
>> Destination:
>> SQL2K5
>> SP2
>> I have requirements to import data from the source to the destination,
>> and once on the destination the data will be read only. I also have the
>> situation where the data owners (not me) may make schema changes on the
>> source whenever they want to, and do not need to tell me. Most of the
>> time this would only be widening an existing column, so I'm not all that
>> worried. This data is only to be grabbed once a night during a slow
>> period.
>> Options:
>> Replication: This is out because the data owner will want to widen a
>> column without needing to consult with me first (I would need to drop
>> replication first). I'm aware of the changes to 2005 replication, but
>> they won't be upgrading anytime soon.
>> DTS/ SSIS: This is out because they will not want to tell me if they
>> widen a column. Just because they widen the source, that doesn't help my
>> destination.
>>
>> Because of all these requirements/ circumstances, I want to just create a
>> Linked Server and do a Select...Into every night and drop/ re-create the
>> table on the destination. This will make schema changes on the source
>> transparent to me. Of course though, I don't want my Select...Into to
>> block users on the source while the import is occurring. That being said,
>> I was thinking about setting the Transaction Isolation Level (TIL) to
>> Read Uncommitted (RU) in the Select...Into. I don't really care if the
>> consistency is off a bit as it's only updated once a day anyways. This is
>> how it would look:
>> Set Transaction Isolation Level Read Uncommitted
>> select * into T48
>> from myLinkedServer.database.dbo.T48
>> So my question is: Will setting the TIL to RU like this actually set it
>> for the source (the Linked Server) or the destination (my server)? If it
>> does in fact set it for the source, would NOLOCK work?
>> TIA, ChrisR
>|||One other approach would be to import into a permanent staging table -
perhaps in a staging database - that matches your data SOURCE which I
take it is not changing. Then use SQL to INSERT to the target table
from staging.
Roy Harvey
Beacon Falls, CT
On Fri, 28 Sep 2007 07:49:13 -0700, "ChrisR" <ChrisR@.foo.com> wrote:
>Source:
>SQL2K
>SP3
>Destination:
>SQL2K5
>SP2
>I have requirements to import data from the source to the destination, and
>once on the destination the data will be read only. I also have the
>situation where the data owners (not me) may make schema changes on the
>source whenever they want to, and do not need to tell me. Most of the time
>this would only be widening an existing column, so I'm not all that worried.
>This data is only to be grabbed once a night during a slow period.
>Options:
>Replication: This is out because the data owner will want to widen a column
>without needing to consult with me first (I would need to drop replication
>first). I'm aware of the changes to 2005 replication, but they won't be
>upgrading anytime soon.
>DTS/ SSIS: This is out because they will not want to tell me if they widen a
>column. Just because they widen the source, that doesn't help my
>destination.
>
>Because of all these requirements/ circumstances, I want to just create a
>Linked Server and do a Select...Into every night and drop/ re-create the
>table on the destination. This will make schema changes on the source
>transparent to me. Of course though, I don't want my Select...Into to block
>users on the source while the import is occurring. That being said, I was
>thinking about setting the Transaction Isolation Level (TIL) to Read
>Uncommitted (RU) in the Select...Into. I don't really care if the
>consistency is off a bit as it's only updated once a day anyways. This is
>how it would look:
>Set Transaction Isolation Level Read Uncommitted
>select * into T48
>from myLinkedServer.database.dbo.T48
>So my question is: Will setting the TIL to RU like this actually set it for
>the source (the Linked Server) or the destination (my server)? If it does in
>fact set it for the source, would NOLOCK work?
>TIA, ChrisR
>|||I think what you are saying is to first do a Select...Into a local table as
opposed to directly into the Linked Server? If so, I was already thinking
the same thing. If not, could you please elaborate?
"Roy Harvey (SQL Server MVP)" <roy_harvey@.snet.net> wrote in message
news:jvjqf39vh2kut6smef1rg2dj33crnkstij@.4ax.com...
> One other approach would be to import into a permanent staging table -
> perhaps in a staging database - that matches your data SOURCE which I
> take it is not changing. Then use SQL to INSERT to the target table
> from staging.
> Roy Harvey
> Beacon Falls, CT
> On Fri, 28 Sep 2007 07:49:13 -0700, "ChrisR" <ChrisR@.foo.com> wrote:
>>Source:
>>SQL2K
>>SP3
>>Destination:
>>SQL2K5
>>SP2
>>I have requirements to import data from the source to the destination, and
>>once on the destination the data will be read only. I also have the
>>situation where the data owners (not me) may make schema changes on the
>>source whenever they want to, and do not need to tell me. Most of the time
>>this would only be widening an existing column, so I'm not all that
>>worried.
>>This data is only to be grabbed once a night during a slow period.
>>Options:
>>Replication: This is out because the data owner will want to widen a
>>column
>>without needing to consult with me first (I would need to drop replication
>>first). I'm aware of the changes to 2005 replication, but they won't be
>>upgrading anytime soon.
>>DTS/ SSIS: This is out because they will not want to tell me if they widen
>>a
>>column. Just because they widen the source, that doesn't help my
>>destination.
>>
>>Because of all these requirements/ circumstances, I want to just create a
>>Linked Server and do a Select...Into every night and drop/ re-create the
>>table on the destination. This will make schema changes on the source
>>transparent to me. Of course though, I don't want my Select...Into to
>>block
>>users on the source while the import is occurring. That being said, I was
>>thinking about setting the Transaction Isolation Level (TIL) to Read
>>Uncommitted (RU) in the Select...Into. I don't really care if the
>>consistency is off a bit as it's only updated once a day anyways. This is
>>how it would look:
>>Set Transaction Isolation Level Read Uncommitted
>>select * into T48
>>from myLinkedServer.database.dbo.T48
>>So my question is: Will setting the TIL to RU like this actually set it
>>for
>>the source (the Linked Server) or the destination (my server)? If it does
>>in
>>fact set it for the source, would NOLOCK work?
>>TIA, ChrisR|||On Fri, 28 Sep 2007 13:03:23 -0700, "ChrisR" <ChrisR@.foo.com> wrote:
>I think what you are saying is to first do a Select...Into a local table as
>opposed to directly into the Linked Server? If so, I was already thinking
>the same thing. If not, could you please elaborate?
You have a source table somewhere on another server, call it Source.
You have a target table on the target server, call it Target. Target
is subject to change, but Source is not. Create a permanent table
Target_Imported, on the target server. Put it in the same database as
Target, or in a staging database. Make the columns match the Target
table in name and order, but make the column data types match the
related column in Source. Since the types match importing from Source
into Target_Import using BCP, DTS, SSIS or even INSERT/SELECT from a
linked server should be straight forward. Because the column names
match, copying the data between Target_Imported and Target requires a
simple INSERT/SELECT. You could add WITH RECOMPILE to the stored
procedure that does the copy so that the sort of minor datatype
changes you describe are handled without intervention.
Hope that is clearer. I don't know that it is any better than what
you proposed, just another idea.
Roy Harvey
Beacon Falls, CT|||Thanks, but you have it backwards. The source can change at any time,
without any notice. Therefore, if the source changes, I want the target to
change as well. Hence, Select...Into.
Thanks again!
"Roy Harvey (SQL Server MVP)" <roy_harvey@.snet.net> wrote in message
news:c2oqf3p37k0ieg5n6j34120j662a0e319a@.4ax.com...
> On Fri, 28 Sep 2007 13:03:23 -0700, "ChrisR" <ChrisR@.foo.com> wrote:
>>I think what you are saying is to first do a Select...Into a local table
>>as
>>opposed to directly into the Linked Server? If so, I was already thinking
>>the same thing. If not, could you please elaborate?
> You have a source table somewhere on another server, call it Source.
> You have a target table on the target server, call it Target. Target
> is subject to change, but Source is not. Create a permanent table
> Target_Imported, on the target server. Put it in the same database as
> Target, or in a staging database. Make the columns match the Target
> table in name and order, but make the column data types match the
> related column in Source. Since the types match importing from Source
> into Target_Import using BCP, DTS, SSIS or even INSERT/SELECT from a
> linked server should be straight forward. Because the column names
> match, copying the data between Target_Imported and Target requires a
> simple INSERT/SELECT. You could add WITH RECOMPILE to the stored
> procedure that does the copy so that the sort of minor datatype
> changes you describe are handled without intervention.
> Hope that is clearer. I don't know that it is any better than what
> you proposed, just another idea.
> Roy Harvey
> Beacon Falls, CT