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

question on synchronization for Intel Developer Services artic

Thanks, Andrew. Can I bounce another, higher level question off you?
The gist of my article is that the tipping point for asynchronous, OC
applications is mobile repair and service personnel. Here's a case study tha
t
the Intel guys like:
http://www.ianywhere.com/success_st...ton_north.html.
The idea generally is that IT infrastructure is already in place in standard
office & enterprise environments. So there's inertia when there's any talk o
f
changing the infrastructure from always-on to sometimes-on, even when there'
s
problems. (For instance, if you're working connected via Wi-Fi to Outlook,
SAP perhaps a CRM program, and then you walk from floor to floor, changing
subnets and walking in and out WiFi hotspots, you'll often get lots of error
messages.) But when the office/white collar-types see the productivity gains
service/maintenance personnel get from working in OC fashion, then the white
collar workers demand the same kind infrastructure, etc., etc.
I guess my questions is, do you buy it? That giving service/maintenance
workers mobile applications that work well with sporadic network connectivit
y
(perhaps wireless, perhaps ethernet, perhaps dial-up) will be the tipping
point to change the way lots of sw is architected?
Thanks,
Geoff
"Andrew J. Kelly" wrote:

> If you are talking about replication then any of the SQL Server editions c
an
> do some form of replication, not just CE. What edition will run on your
> mobile device depends on what OS it is running. ADO.net has a lot of
> features based around offline datasets and make it relatively easy to buil
d
> applications that can work off line and update later. I would suggest you
> have a look at www.microsoft.com/sql and browse around in the different
> areas that may be of interest to you.
> --
> Andrew J. Kelly SQL MVP
>
> "Geoff" <Geoff@.discussions.microsoft.com> wrote in message
> news:9E776F8F-AA13-432F-B40C-78C3A2AA5DD1@.microsoft.com...
>
>Well I can't say I have given this a lot of thought lately but yes there are
definite advantages and productivity gains to be had by using applications
that can work the way you need when disconnected. As such a certain
percentage of new software will be written to take advantage of that. That
does not mean everything will change but many professions are already
demanding remote or offline access today.
Andrew J. Kelly SQL MVP
"Geoff" <Geoff@.discussions.microsoft.com> wrote in message
news:5F224D8D-4278-450F-B04F-11BFC5DC1687@.microsoft.com...
> Thanks, Andrew. Can I bounce another, higher level question off you?
> The gist of my article is that the tipping point for asynchronous, OC
> applications is mobile repair and service personnel. Here's a case study
> that
> the Intel guys like:
> http://www.ianywhere.com/success_st...ton_north.html.
> The idea generally is that IT infrastructure is already in place in
> standard
> office & enterprise environments. So there's inertia when there's any talk
> of
> changing the infrastructure from always-on to sometimes-on, even when
> there's
> problems. (For instance, if you're working connected via Wi-Fi to Outlook,
> SAP perhaps a CRM program, and then you walk from floor to floor, changing
> subnets and walking in and out WiFi hotspots, you'll often get lots of
> error
> messages.) But when the office/white collar-types see the productivity
> gains
> service/maintenance personnel get from working in OC fashion, then the
> white
> collar workers demand the same kind infrastructure, etc., etc.
> I guess my questions is, do you buy it? That giving service/maintenance
> workers mobile applications that work well with sporadic network
> connectivity
> (perhaps wireless, perhaps ethernet, perhaps dial-up) will be the tipping
> point to change the way lots of sw is architected?
> Thanks,
> Geoff
>
> "Andrew J. Kelly" wrote:
>|||Thanks, Andrew. May I quote you on this in my article?
Geoff gkoch AT stanfordalumn DOT org
"Andrew J. Kelly" wrote:

> Well I can't say I have given this a lot of thought lately but yes there a
re
> definite advantages and productivity gains to be had by using applications
> that can work the way you need when disconnected. As such a certain
> percentage of new software will be written to take advantage of that. Tha
t
> does not mean everything will change but many professions are already
> demanding remote or offline access today.
> --
> Andrew J. Kelly SQL MVP
>
> "Geoff" <Geoff@.discussions.microsoft.com> wrote in message
> news:5F224D8D-4278-450F-B04F-11BFC5DC1687@.microsoft.com...
>
>|||Sure, just keep in mind this is my personal opinion based on my real life
experiences and am not trying to dictate how future apps will be built<g>.
Andrew J. Kelly SQL MVP
"Geoff" <Geoff@.discussions.microsoft.com> wrote in message
news:D8AD9D1A-BCEA-48E3-8262-0FC6008EEECB@.microsoft.com...
> Thanks, Andrew. May I quote you on this in my article?
> Geoff gkoch AT stanfordalumn DOT org
> "Andrew J. Kelly" wrote:
>|||Will do and thank you.
"Andrew J. Kelly" wrote:

> Sure, just keep in mind this is my personal opinion based on my real life
> experiences and am not trying to dictate how future apps will be built<g>.
> --
> Andrew J. Kelly SQL MVP
>
> "Geoff" <Geoff@.discussions.microsoft.com> wrote in message
> news:D8AD9D1A-BCEA-48E3-8262-0FC6008EEECB@.microsoft.com...
>
>

Question on cube granularity again

Hi, all,

Again, I wonder is it a good idea to design the granularity level of the cube as the same as in its underlying relational database e.g the most detailed level as it is in its fact table and its dimension table.

I am looking forward to hearing from you for any advices.

Thanks a lot in advance.

With best regards,

Yours sincerely,

It really depends on your requirements. I would say you probably should make the granularities the same unless it's going to cause serious performance problems with the processing and/or querying of your cube - it just means you have all the data your users might want available. Changing the granularity of your cube once you've gone into production would also be a bit messy.

Chris

|||

Hi, Chris,

Thanks a lot for your advices. Very good point.

With best regards,

Yours sincerely,