Production Vs Development

Hi,
I've just come into a new organisation and my unit's role is mostly
reporting on mostly OLAP type data (although our "warehouse" is pretty much
useless, and so we work off periodic snapshots of the OLTP data, which seems
to be tolerable performance-wise).
I'm having a bit of trouble coming to grips with their idea of
"Development", "Test", and "Production" environments.
First off, there is no such thing as "test", just dev and prod.
Next, the IT Department that manages the what they call "production" is
unrelated to my business unit. They are apparently very unresponsive and can
routinely take six months to effect any requested change to the "production"
reporting database objects. We have read-only access to the production
server/database.
To work around this, my unit has lapsed into the habit of running their
reports off the dev server (which they have control over). When I've pointed
out to them respectfully, that they appear to be doing production work on a
dev server, I come up against a brick wall.
"No." They say. "If we're not reporting off the production server, it's not
production reporting." But then they go on to publish these reports as
representative of the state of the business.
Now I would have though that any environment you use to produce outputs that
you regard as being a part of your business are defacto "production", but I'm
getting no traction with such a novel concept here.
It strikes me though, that Microsoft, given the price differential between
their development and production licensing would have a fairly water-tight
differentiation between dev and prod. What is MS's idea of the difference,
and where can I find a version of it that is written in words that grade
threes could cope with?
Cheers
GPOWhat you have is a problem in the leadership (or rather lack of it) of your
organization. If you want the kind of change you've described, a document
from Microsoft isn't going to change anything.
If you can get a server, load an Express version on it and declare it a
production instance. Then move the reporting to it as you can. Lead by
example.
"GPO" <GPO@.discussions.microsoft.com> wrote in message
news:2FC64B51-5540-4FF0-81DF-7E690ACD3645@.microsoft.com...
> Hi,
> I've just come into a new organisation and my unit's role is mostly
> reporting on mostly OLAP type data (although our "warehouse" is pretty
> much
> useless, and so we work off periodic snapshots of the OLTP data, which
> seems
> to be tolerable performance-wise).
> I'm having a bit of trouble coming to grips with their idea of
> "Development", "Test", and "Production" environments.
> First off, there is no such thing as "test", just dev and prod.
> Next, the IT Department that manages the what they call "production" is
> unrelated to my business unit. They are apparently very unresponsive and
> can
> routinely take six months to effect any requested change to the
> "production"
> reporting database objects. We have read-only access to the production
> server/database.
> To work around this, my unit has lapsed into the habit of running their
> reports off the dev server (which they have control over). When I've
> pointed
> out to them respectfully, that they appear to be doing production work on
> a
> dev server, I come up against a brick wall.
> "No." They say. "If we're not reporting off the production server, it's
> not
> production reporting." But then they go on to publish these reports as
> representative of the state of the business.
> Now I would have though that any environment you use to produce outputs
> that
> you regard as being a part of your business are defacto "production", but
> I'm
> getting no traction with such a novel concept here.
> It strikes me though, that Microsoft, given the price differential between
> their development and production licensing would have a fairly water-tight
> differentiation between dev and prod. What is MS's idea of the difference,
> and where can I find a version of it that is written in words that grade
> threes could cope with?
> Cheers
> GPO
>|||I cling to the hope that some basic guidelines from MS will help. I don't
think management is dishonest; merely incompetent. Question is "Where can I
find such guidelines - and written in a way that even my management could
understand?"
The other thing is that SQL Express might not be appropriate anyway. I
thought Express was not intended for an enterprise sized operation, and the
4GB limit would kill us if nothing else.
Regards
GPO
"Jay" wrote:
> What you have is a problem in the leadership (or rather lack of it) of your
> organization. If you want the kind of change you've described, a document
> from Microsoft isn't going to change anything.
> If you can get a server, load an Express version on it and declare it a
> production instance. Then move the reporting to it as you can. Lead by
> example.
> "GPO" <GPO@.discussions.microsoft.com> wrote in message
> news:2FC64B51-5540-4FF0-81DF-7E690ACD3645@.microsoft.com...
> > Hi,
> >
> > I've just come into a new organisation and my unit's role is mostly
> > reporting on mostly OLAP type data (although our "warehouse" is pretty
> > much
> > useless, and so we work off periodic snapshots of the OLTP data, which
> > seems
> > to be tolerable performance-wise).
> >
> > I'm having a bit of trouble coming to grips with their idea of
> > "Development", "Test", and "Production" environments.
> >
> > First off, there is no such thing as "test", just dev and prod.
> > Next, the IT Department that manages the what they call "production" is
> > unrelated to my business unit. They are apparently very unresponsive and
> > can
> > routinely take six months to effect any requested change to the
> > "production"
> > reporting database objects. We have read-only access to the production
> > server/database.
> >
> > To work around this, my unit has lapsed into the habit of running their
> > reports off the dev server (which they have control over). When I've
> > pointed
> > out to them respectfully, that they appear to be doing production work on
> > a
> > dev server, I come up against a brick wall.
> >
> > "No." They say. "If we're not reporting off the production server, it's
> > not
> > production reporting." But then they go on to publish these reports as
> > representative of the state of the business.
> >
> > Now I would have though that any environment you use to produce outputs
> > that
> > you regard as being a part of your business are defacto "production", but
> > I'm
> > getting no traction with such a novel concept here.
> >
> > It strikes me though, that Microsoft, given the price differential between
> > their development and production licensing would have a fairly water-tight
> > differentiation between dev and prod. What is MS's idea of the difference,
> > and where can I find a version of it that is written in words that grade
> > threes could cope with?
> >
> > Cheers
> >
> > GPO
> >
>
>|||MS might do a lot of things but one thing they don't do is dictate the
opeartional policies of your company.
That is a role of the Senior Management team of your company.
You talk about 'my unit'. Are you the Manager of 'my unit'? If you are then
simply issue a written instruction to all members of 'my unit' setting out
how things shall work and require that they acknowledge, in writing, that
they have read and understood the instruction. After that, if anyone
disregards the instruction then it becomes a disciplinary matter.
Is the Senior Management of the company aware of the 'unreponsive'ness of
the production IT team? If not then make them aware of it. If I were on the
Senior Management team and that sort of information came to my attention I
would be doing some very serious backside kicking.
"GPO" <GPO@.discussions.microsoft.com> wrote in message
news:7A2C1D04-3FEA-4A06-9C9F-70FEC67659F9@.microsoft.com...
>I cling to the hope that some basic guidelines from MS will help. I don't
> think management is dishonest; merely incompetent. Question is "Where can
> I
> find such guidelines - and written in a way that even my management could
> understand?"
> The other thing is that SQL Express might not be appropriate anyway. I
> thought Express was not intended for an enterprise sized operation, and
> the
> 4GB limit would kill us if nothing else.
> Regards
> GPO
> "Jay" wrote:
>> What you have is a problem in the leadership (or rather lack of it) of
>> your
>> organization. If you want the kind of change you've described, a document
>> from Microsoft isn't going to change anything.
>> If you can get a server, load an Express version on it and declare it a
>> production instance. Then move the reporting to it as you can. Lead by
>> example.
>> "GPO" <GPO@.discussions.microsoft.com> wrote in message
>> news:2FC64B51-5540-4FF0-81DF-7E690ACD3645@.microsoft.com...
>> > Hi,
>> >
>> > I've just come into a new organisation and my unit's role is mostly
>> > reporting on mostly OLAP type data (although our "warehouse" is pretty
>> > much
>> > useless, and so we work off periodic snapshots of the OLTP data, which
>> > seems
>> > to be tolerable performance-wise).
>> >
>> > I'm having a bit of trouble coming to grips with their idea of
>> > "Development", "Test", and "Production" environments.
>> >
>> > First off, there is no such thing as "test", just dev and prod.
>> > Next, the IT Department that manages the what they call "production" is
>> > unrelated to my business unit. They are apparently very unresponsive
>> > and
>> > can
>> > routinely take six months to effect any requested change to the
>> > "production"
>> > reporting database objects. We have read-only access to the production
>> > server/database.
>> >
>> > To work around this, my unit has lapsed into the habit of running their
>> > reports off the dev server (which they have control over). When I've
>> > pointed
>> > out to them respectfully, that they appear to be doing production work
>> > on
>> > a
>> > dev server, I come up against a brick wall.
>> >
>> > "No." They say. "If we're not reporting off the production server, it's
>> > not
>> > production reporting." But then they go on to publish these reports as
>> > representative of the state of the business.
>> >
>> > Now I would have though that any environment you use to produce outputs
>> > that
>> > you regard as being a part of your business are defacto "production",
>> > but
>> > I'm
>> > getting no traction with such a novel concept here.
>> >
>> > It strikes me though, that Microsoft, given the price differential
>> > between
>> > their development and production licensing would have a fairly
>> > water-tight
>> > differentiation between dev and prod. What is MS's idea of the
>> > difference,
>> > and where can I find a version of it that is written in words that
>> > grade
>> > threes could cope with?
>> >
>> > Cheers
>> >
>> > GPO
>> >
>>|||"...MS might do a lot of things but one thing they don't do is dictate the
opeartional policies of your company..."
Yes but surely they'd take an interest in whether people were purchasing
developer software (cheap cheap cheap) and then using it in a production
capacity. My question is how do they make this judgement?
Sadly it's not "my unit" in the sense that I manage it. I don't. I say "my
unit" because it's the unit I work for.
"...Is the Senior Management of the company aware of the 'unreponsive'ness
..." Well it's a large government department. Unresponsiveness is culturally
ingrained.
"Stephany Young" wrote:
> MS might do a lot of things but one thing they don't do is dictate the
> opeartional policies of your company.
> That is a role of the Senior Management team of your company.
> You talk about 'my unit'. Are you the Manager of 'my unit'? If you are then
> simply issue a written instruction to all members of 'my unit' setting out
> how things shall work and require that they acknowledge, in writing, that
> they have read and understood the instruction. After that, if anyone
> disregards the instruction then it becomes a disciplinary matter.
> Is the Senior Management of the company aware of the 'unreponsive'ness of
> the production IT team? If not then make them aware of it. If I were on the
> Senior Management team and that sort of information came to my attention I
> would be doing some very serious backside kicking.
>
> "GPO" <GPO@.discussions.microsoft.com> wrote in message
> news:7A2C1D04-3FEA-4A06-9C9F-70FEC67659F9@.microsoft.com...
> >I cling to the hope that some basic guidelines from MS will help. I don't
> > think management is dishonest; merely incompetent. Question is "Where can
> > I
> > find such guidelines - and written in a way that even my management could
> > understand?"
> >
> > The other thing is that SQL Express might not be appropriate anyway. I
> > thought Express was not intended for an enterprise sized operation, and
> > the
> > 4GB limit would kill us if nothing else.
> >
> > Regards
> >
> > GPO
> >
> > "Jay" wrote:
> >
> >> What you have is a problem in the leadership (or rather lack of it) of
> >> your
> >> organization. If you want the kind of change you've described, a document
> >> from Microsoft isn't going to change anything.
> >>
> >> If you can get a server, load an Express version on it and declare it a
> >> production instance. Then move the reporting to it as you can. Lead by
> >> example.
> >>
> >> "GPO" <GPO@.discussions.microsoft.com> wrote in message
> >> news:2FC64B51-5540-4FF0-81DF-7E690ACD3645@.microsoft.com...
> >> > Hi,
> >> >
> >> > I've just come into a new organisation and my unit's role is mostly
> >> > reporting on mostly OLAP type data (although our "warehouse" is pretty
> >> > much
> >> > useless, and so we work off periodic snapshots of the OLTP data, which
> >> > seems
> >> > to be tolerable performance-wise).
> >> >
> >> > I'm having a bit of trouble coming to grips with their idea of
> >> > "Development", "Test", and "Production" environments.
> >> >
> >> > First off, there is no such thing as "test", just dev and prod.
> >> > Next, the IT Department that manages the what they call "production" is
> >> > unrelated to my business unit. They are apparently very unresponsive
> >> > and
> >> > can
> >> > routinely take six months to effect any requested change to the
> >> > "production"
> >> > reporting database objects. We have read-only access to the production
> >> > server/database.
> >> >
> >> > To work around this, my unit has lapsed into the habit of running their
> >> > reports off the dev server (which they have control over). When I've
> >> > pointed
> >> > out to them respectfully, that they appear to be doing production work
> >> > on
> >> > a
> >> > dev server, I come up against a brick wall.
> >> >
> >> > "No." They say. "If we're not reporting off the production server, it's
> >> > not
> >> > production reporting." But then they go on to publish these reports as
> >> > representative of the state of the business.
> >> >
> >> > Now I would have though that any environment you use to produce outputs
> >> > that
> >> > you regard as being a part of your business are defacto "production",
> >> > but
> >> > I'm
> >> > getting no traction with such a novel concept here.
> >> >
> >> > It strikes me though, that Microsoft, given the price differential
> >> > between
> >> > their development and production licensing would have a fairly
> >> > water-tight
> >> > differentiation between dev and prod. What is MS's idea of the
> >> > difference,
> >> > and where can I find a version of it that is written in words that
> >> > grade
> >> > threes could cope with?
> >> >
> >> > Cheers
> >> >
> >> > GPO
> >> >
> >>
> >>
> >>
>

process cube but leave transaction open?

We have a massive cube. All but one of the fact tables are by 3am. But the final fact table isn't loaded in the SQL data warehouse until 11am. The users want the cube processed and available as immediately as possible. But the cube can't show ANY updated data until it shows ALL updated data including the fact table from 11am. Any thoughts on making this happen?

One option would be to process the cube at 3am in another database. Then process the final measure group at 11am. Then use the Synchronize command to get those changes to the live database soon after 11am. But there are two drawbacks... 1) This requires twice the amount of disk space as before and 2) I can't figure out a way to synchronize two databases on the same server.

Another option would be to process the cube at 3am but leave that transaction open (so end users can't see the new data) for 8 hours until you process the final measure group and then close the transaction. This doesn't seem like a good plan, but I wanted to hear people's thoughts.

I would certainly experiment with leaving transaction open for 8 hours. I don't see why it doesn't seem like a good plan to you. Note, that you will still have doubled usage of disk space, since two versions will be coexisting for the 8 hours.|||

Well, maybe it's not a dumb idea. I guess the reason I was worried about it is just my relational background and the idea to never leave transactions open. But I guess that doesn't matter in this case.

So my main concern with the technical feasibility is whether the server will clean up the idle session (in so doing cancel the transaction). Am I going to have to run some cheap discover function ever 5 mins for 8 hours to keep the session alive? Any other way?

Thanks for the feedback Mosha.

|||

If server canceling the idle session becomes a problem - you can always bump up the session expiration timeout.

|||

I've got this working. Cube processing is broken up into two parts with the connection being dropped in between. Just like you said, it works fine. Just had to bump up the IdleOrphanSessionTimeout setting.

There is one problem, though. If we get a failure on part 2 of the processing (like if there's an invalid dimension key in a fact table) then that failure rolls back the entire transaction, not just the stuff being processed during part 2. Wondering if there's a way around this. I can walk you through reproducing this against Adventure Works:

First, change the query binding of the Reseller_Sales_2004 partition of the Reseller Sales measure group to be:

SELECT [dbo].[FactResellerSales].[ProductKey],-999 as OrderDateKey,[dbo].[FactResellerSales].[DueDateKey],[dbo].[FactResellerSales].[ShipDateKey],[dbo].[FactResellerSales].[ResellerKey], [dbo].[FactResellerSales].[EmployeeKey],[dbo].[FactResellerSales].[PromotionKey],[dbo].[FactResellerSales].[CurrencyKey],[dbo].[FactResellerSales].[SalesTerritoryKey],[dbo].[FactResellerSales].[SalesOrderNumber],[dbo].[FactResellerSales].[SalesOrderLineNumber],[dbo].[FactResellerSales].[RevisionNumber],[dbo].[FactResellerSales].[OrderQuantity],[dbo].[FactResellerSales].[UnitPrice],[dbo].[FactResellerSales].[ExtendedAmount],[dbo].[FactResellerSales].[UnitPriceDiscountPct],[dbo].[FactResellerSales].[DiscountAmount],[dbo].[FactResellerSales].[ProductStandardCost],[dbo].[FactResellerSales].[TotalProductCost],[dbo].[FactResellerSales].[SalesAmount],[dbo].[FactResellerSales].[TaxAmt],[dbo].[FactResellerSales].[Freight],[dbo].[FactResellerSales].[CarrierTrackingNumber],[dbo].[FactResellerSales].[CustomerPONumber]
FROM [dbo].[FactResellerSales]
WHERE OrderDateKey >= '915' AND OrderDateKey <= '1280'

(Note that "-999 as OrderDateKey" will cause a failure.)

Second, pull up the directory contains the 2004 partition for Internet Sales. (On your computer it should be something like c:\program files\Microsoft SQL Server\mssql.2\OLAP\data\Adventure Works DW.0.db\Adventure Works DW.18.cub\Fact Internet Sales 1.18.det\Internet_Sales_2004.16.prt) After part 1 completes, you'll see the number of files in this directory double. After part 2 fails, you'll see those new files disappear as the transaction was rolled back.

Then, compile a little VB.NET app, put a breakpoint on the Sleep command so you can see the number of files in that 2004 partition directory double. Then run it:

Public Shared Function Main(ByVal args As String()) As Integer

Dim sServer As String = "localhost"

Dim sDatabase As String = "Adventure Works DW"

ProcessPart1(sServer, sDatabase)

System.Threading.Thread.Sleep(5000) 'simulate a break in the program

ProcessPart2(sServer, sDatabase)

End Function

Public Shared Sub ProcessPart1(ByVal sServer As String, ByVal sDatabase As String)

Dim server As New Server()

server.Connect(sServer)

Dim database As Database = server.Databases.FindByName(sDatabase)

'save the session ID so we can retrieve it later and connect to this session later

'alternately, it could be saved to a SQL database instead

database.Annotations.SetText("ProcessCubeSessionID", server.SessionID)

database.Update(UpdateOptions.Default, UpdateMode.Update)

server.BeginTransaction()

Try

Dim cube As Cube = database.Cubes.FindByName("Adventure Works")

Dim mg As MeasureGroup = cube.MeasureGroups.FindByName("Internet Sales")

mg.Process(ProcessType.ProcessFull)

'after processing has succeeded, change the orphan session timeout

'to 24 hours so that this session we're about to leave open won't timeout

'before we're done with it

server.ServerProperties("IdleOrphanSessionTimeout").Value = CStr(24 * 60 * 60)

server.Update()

'false means don't end session

'leave the session open so we can connect to it later

'and continue with the open transaction

server.Disconnect(False)

'don't commit the transaction now

'leave it open until we finish the processing later

Catch ex As Exception

server.RollbackTransaction()

'blank out the ProcessCubeSessionID annotation

database.Annotations.SetText("ProcessCubeSessionID", Nothing, True)

database.Update(UpdateOptions.Default, UpdateMode.Update)

server.Disconnect(True)

Throw

End Try

End Sub

Public Shared Sub ProcessPart2(ByVal sServer As String, ByVal sDatabase As String)

Dim server As New Server()

server.Connect(sServer)

Dim database As Database = server.Databases.FindByName(sDatabase)

Dim oldSession As String = database.Annotations.GetText("ProcessCubeSessionID")

If String.IsNullOrEmpty(oldSession) Then

Throw New Exception("Was not able to find the existing session which " _

& "processed the other 95% of the cube and has the open transaction! " _

& "The cube will not be processed.")

End If

server.Disconnect(True)

Try

server.Connect(sServer, oldSession)

Dim cube As Cube = database.Cubes.FindByName("Adventure Works")

Dim mg As MeasureGroup = cube.MeasureGroups.FindByName("Reseller Sales")

mg.Process(ProcessType.ProcessFull) 'BUG?: if this fails, it rolls back the transaction!!!

server.CommitTransaction()

'blank out the ProcessCubeSessionID annotation

database.Annotations.SetText("ProcessCubeSessionID", Nothing, True)

database.Update(UpdateOptions.Default, UpdateMode.Update)

'set this property back to the default now that

'we're done needing orphan sessions to survive

server.ServerProperties("IdleOrphanSessionTimeout").Value = _

server.ServerProperties("IdleOrphanSessionTimeout").DefaultValue

server.Update()

server.Disconnect(True) 'true means to end session

Catch ex As Exception

'there was a problem with the last 5% of the processing...

'so leave the session open so the problem can be corrected and we can

'start with 95% of the processing from earlier in the morning

'being completed already

server.Disconnect(False) 'false means don't end session

Throw

End Try

End Sub

If you search for the line in the code which has a comment saying "BUG?" that's where the processing fails because of our tweak to the partition query. When that processing fails.

|||

I'd still like to hear if anyone knows a workaround, but I've posted this as a bug:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=278000

|||I expect the bug that you filed to be resolved "By Design". The definition of the transaction is that it either fully succeed or fully fails. That's the letter "A" in the ACID definition - A for Atomicy. You have very long transaction, but it doesn't matter - if anything fails within transaction, the entire transaction is rolled back.

process cube but leave transaction open?

We have a massive cube. All but one of the fact tables are by 3am. But the final fact table isn't loaded in the SQL data warehouse until 11am. The users want the cube processed and available as immediately as possible. But the cube can't show ANY updated data until it shows ALL updated data including the fact table from 11am. Any thoughts on making this happen?

One option would be to process the cube at 3am in another database. Then process the final measure group at 11am. Then use the Synchronize command to get those changes to the live database soon after 11am. But there are two drawbacks... 1) This requires twice the amount of disk space as before and 2) I can't figure out a way to synchronize two databases on the same server.

Another option would be to process the cube at 3am but leave that transaction open (so end users can't see the new data) for 8 hours until you process the final measure group and then close the transaction. This doesn't seem like a good plan, but I wanted to hear people's thoughts.

I would certainly experiment with leaving transaction open for 8 hours. I don't see why it doesn't seem like a good plan to you. Note, that you will still have doubled usage of disk space, since two versions will be coexisting for the 8 hours.|||

Well, maybe it's not a dumb idea. I guess the reason I was worried about it is just my relational background and the idea to never leave transactions open. But I guess that doesn't matter in this case.

So my main concern with the technical feasibility is whether the server will clean up the idle session (in so doing cancel the transaction). Am I going to have to run some cheap discover function ever 5 mins for 8 hours to keep the session alive? Any other way?

Thanks for the feedback Mosha.

|||

If server canceling the idle session becomes a problem - you can always bump up the session expiration timeout.

|||

I've got this working. Cube processing is broken up into two parts with the connection being dropped in between. Just like you said, it works fine. Just had to bump up the IdleOrphanSessionTimeout setting.

There is one problem, though. If we get a failure on part 2 of the processing (like if there's an invalid dimension key in a fact table) then that failure rolls back the entire transaction, not just the stuff being processed during part 2. Wondering if there's a way around this. I can walk you through reproducing this against Adventure Works:

First, change the query binding of the Reseller_Sales_2004 partition of the Reseller Sales measure group to be:

SELECT [dbo].[FactResellerSales].[ProductKey],-999 as OrderDateKey,[dbo].[FactResellerSales].[DueDateKey],[dbo].[FactResellerSales].[ShipDateKey],[dbo].[FactResellerSales].[ResellerKey], [dbo].[FactResellerSales].[EmployeeKey],[dbo].[FactResellerSales].[PromotionKey],[dbo].[FactResellerSales].[CurrencyKey],[dbo].[FactResellerSales].[SalesTerritoryKey],[dbo].[FactResellerSales].[SalesOrderNumber],[dbo].[FactResellerSales].[SalesOrderLineNumber],[dbo].[FactResellerSales].[RevisionNumber],[dbo].[FactResellerSales].[OrderQuantity],[dbo].[FactResellerSales].[UnitPrice],[dbo].[FactResellerSales].[ExtendedAmount],[dbo].[FactResellerSales].[UnitPriceDiscountPct],[dbo].[FactResellerSales].[DiscountAmount],[dbo].[FactResellerSales].[ProductStandardCost],[dbo].[FactResellerSales].[TotalProductCost],[dbo].[FactResellerSales].[SalesAmount],[dbo].[FactResellerSales].[TaxAmt],[dbo].[FactResellerSales].[Freight],[dbo].[FactResellerSales].[CarrierTrackingNumber],[dbo].[FactResellerSales].[CustomerPONumber]
FROM [dbo].[FactResellerSales]
WHERE OrderDateKey >= '915' AND OrderDateKey <= '1280'

(Note that "-999 as OrderDateKey" will cause a failure.)

Second, pull up the directory contains the 2004 partition for Internet Sales. (On your computer it should be something like c:\program files\Microsoft SQL Server\mssql.2\OLAP\data\Adventure Works DW.0.db\Adventure Works DW.18.cub\Fact Internet Sales 1.18.det\Internet_Sales_2004.16.prt) After part 1 completes, you'll see the number of files in this directory double. After part 2 fails, you'll see those new files disappear as the transaction was rolled back.

Then, compile a little VB.NET app, put a breakpoint on the Sleep command so you can see the number of files in that 2004 partition directory double. Then run it:

Public Shared Function Main(ByVal args As String()) As Integer

Dim sServer As String = "localhost"

Dim sDatabase As String = "Adventure Works DW"

ProcessPart1(sServer, sDatabase)

System.Threading.Thread.Sleep(5000) 'simulate a break in the program

ProcessPart2(sServer, sDatabase)

End Function

Public Shared Sub ProcessPart1(ByVal sServer As String, ByVal sDatabase As String)

Dim server As New Server()

server.Connect(sServer)

Dim database As Database = server.Databases.FindByName(sDatabase)

'save the session ID so we can retrieve it later and connect to this session later

'alternately, it could be saved to a SQL database instead

database.Annotations.SetText("ProcessCubeSessionID", server.SessionID)

database.Update(UpdateOptions.Default, UpdateMode.Update)

server.BeginTransaction()

Try

Dim cube As Cube = database.Cubes.FindByName("Adventure Works")

Dim mg As MeasureGroup = cube.MeasureGroups.FindByName("Internet Sales")

mg.Process(ProcessType.ProcessFull)

'after processing has succeeded, change the orphan session timeout

'to 24 hours so that this session we're about to leave open won't timeout

'before we're done with it

server.ServerProperties("IdleOrphanSessionTimeout").Value = CStr(24 * 60 * 60)

server.Update()

'false means don't end session

'leave the session open so we can connect to it later

'and continue with the open transaction

server.Disconnect(False)

'don't commit the transaction now

'leave it open until we finish the processing later

Catch ex As Exception

server.RollbackTransaction()

'blank out the ProcessCubeSessionID annotation

database.Annotations.SetText("ProcessCubeSessionID", Nothing, True)

database.Update(UpdateOptions.Default, UpdateMode.Update)

server.Disconnect(True)

Throw

End Try

End Sub

Public Shared Sub ProcessPart2(ByVal sServer As String, ByVal sDatabase As String)

Dim server As New Server()

server.Connect(sServer)

Dim database As Database = server.Databases.FindByName(sDatabase)

Dim oldSession As String = database.Annotations.GetText("ProcessCubeSessionID")

If String.IsNullOrEmpty(oldSession) Then

Throw New Exception("Was not able to find the existing session which " _

& "processed the other 95% of the cube and has the open transaction! " _

& "The cube will not be processed.")

End If

server.Disconnect(True)

Try

server.Connect(sServer, oldSession)

Dim cube As Cube = database.Cubes.FindByName("Adventure Works")

Dim mg As MeasureGroup = cube.MeasureGroups.FindByName("Reseller Sales")

mg.Process(ProcessType.ProcessFull) 'BUG?: if this fails, it rolls back the transaction!!!

server.CommitTransaction()

'blank out the ProcessCubeSessionID annotation

database.Annotations.SetText("ProcessCubeSessionID", Nothing, True)

database.Update(UpdateOptions.Default, UpdateMode.Update)

'set this property back to the default now that

'we're done needing orphan sessions to survive

server.ServerProperties("IdleOrphanSessionTimeout").Value = _

server.ServerProperties("IdleOrphanSessionTimeout").DefaultValue

server.Update()

server.Disconnect(True) 'true means to end session

Catch ex As Exception

'there was a problem with the last 5% of the processing...

'so leave the session open so the problem can be corrected and we can

'start with 95% of the processing from earlier in the morning

'being completed already

server.Disconnect(False) 'false means don't end session

Throw

End Try

End Sub

If you search for the line in the code which has a comment saying "BUG?" that's where the processing fails because of our tweak to the partition query. When that processing fails.

|||

I'd still like to hear if anyone knows a workaround, but I've posted this as a bug:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=278000

|||I expect the bug that you filed to be resolved "By Design". The definition of the transaction is that it either fully succeed or fully fails. That's the letter "A" in the ACID definition - A for Atomicy. You have very long transaction, but it doesn't matter - if anything fails within transaction, the entire transaction is rolled back.