rda submitsql: package

Showing posts with label package. Show all posts

Wednesday, March 28, 2012

raid 5 v raid 10

We are looking to run a web based CRM package that runs on SQL 2005.
It has been recommend that we set the it up with RAID 5 however on
reading up on this it looks like RAID 10 could be better option.
The hard ware spec so far is for RAID 5
2 * 72.6 GB Hard Drives for the operating system
3 * 146.8 GB Hard Drives the database.
If were to go for RAID 10, as to requires equal number of drives I
presume that we would need to up it from 3 to 4.
What are other peoples experiences/viewsIf you have over 20% of your IO activity is write based IO you will get
better performance with RAID 10. While RAID 5 is more expensive than RAID
10 typically the performance gains offset this added cost.
--
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"jp" <jeremypw@.googlemail.com> wrote in message
news:1168248735.954175.305990@.s34g2000cwa.googlegroups.com...
> We are looking to run a web based CRM package that runs on SQL 2005.
> It has been recommend that we set the it up with RAID 5 however on
> reading up on this it looks like RAID 10 could be better option.
> The hard ware spec so far is for RAID 5
> 2 * 72.6 GB Hard Drives for the operating system
> 3 * 146.8 GB Hard Drives the database.
> If were to go for RAID 10, as to requires equal number of drives I
> presume that we would need to up it from 3 to 4.
> What are other peoples experiences/views
>|||"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23Uzk0HxMHHA.5012@.TK2MSFTNGP02.phx.gbl...
> If you have over 20% of your IO activity is write based IO you will get
> better performance with RAID 10. While RAID 5 is more expensive than RAID
> 10 typically the performance gains offset this added cost.
Uh, I think you mean while RAID 5 is LESS expensive than RAID 10?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "jp" <jeremypw@.googlemail.com> wrote in message
> news:1168248735.954175.305990@.s34g2000cwa.googlegroups.com...
>> We are looking to run a web based CRM package that runs on SQL 2005.
>> It has been recommend that we set the it up with RAID 5 however on
>> reading up on this it looks like RAID 10 could be better option.
>> The hard ware spec so far is for RAID 5
>> 2 * 72.6 GB Hard Drives for the operating system
>> 3 * 146.8 GB Hard Drives the database.
>> If were to go for RAID 10, as to requires equal number of drives I
>> presume that we would need to up it from 3 to 4.
>> What are other peoples experiences/views
>|||Hi,
Well, actually, RAID 10 could outperform RAID 5, especially because of
that CRM uses a lot of random writes which is the black point of RAID 5
level.
The theory is the following, assuming you have n similar disks :
RAID 5 Write time : 1/(n-1)
RAID 10 Write time : 1/(n/[number of disks per RAID 1 set])
RAID 5 Read time : 1/(n-1)
RAID 10 Read time : 1/n
RAID 5 fault tolerance : 1 disk
RAID 10 fault tolerance : [number of RAID 0 sets] * ([number of disks
per RAID 1 set] - 1)
Nevertheless, this is only theory... And here are some of the problems
in the real life :
- While updating small sets of data (just as you might do in a CRM
application), you make a random write... Thus, in RAID 5, the
controller has to re-read the entire stripe to write the parity blocks.
Which dramatically brings down the RAID 5 performances... As you double
the theorical write time...
- While inserting large amounts of data, you make a sequential write...
Thus RAID 5 would be much more efficient than any other RAID level,
assuming you need fault tolerance.
- While reading either small sets of data or large amounts of data,
RAID 5 is a little bit slower than RAID 10...
- RAID 5 loses much less disk capacity than RAID 10, and as so is much
cheaper.
In conclusion you might use RAID 10 if your application needs a lot of
random writes... However, as a matter of cost, I would recommand you to
keep RAID 5 and to add a third set of drives to store transaction logs.
In many cases I've came accross, the I/O bottleneck raises because of
the usage of the same set of disks for data and transaction log... As
RAID 5 wouldn't have been a real problem...
C=E9dric Del Nibbio
MCSD .NET
MCTS SQL Server 2005
http://cedric-delnibbio-sql.blogspot.com
jp a =E9crit :
> We are looking to run a web based CRM package that runs on SQL 2005.
> It has been recommend that we set the it up with RAID 5 however on
> reading up on this it looks like RAID 10 could be better option.
> The hard ware spec so far is for RAID 5
> 2 * 72.6 GB Hard Drives for the operating system
> 3 * 146.8 GB Hard Drives the database.
> If were to go for RAID 10, as to requires equal number of drives I
> presume that we would need to up it from 3 to 4.
> > What are other peoples experiences/views|||Thanks Strider - you're right - RAID 10 has a minimum of 4 drives and 50%
utilization. RAID 5 needs a minimum of 3 drives with n-1 utilization.
So RAID 5 is cheaper.
--
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:u1D1dLyMHHA.2456@.TK2MSFTNGP06.phx.gbl...
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:%23Uzk0HxMHHA.5012@.TK2MSFTNGP02.phx.gbl...
>> If you have over 20% of your IO activity is write based IO you will get
>> better performance with RAID 10. While RAID 5 is more expensive than
>> RAID 10 typically the performance gains offset this added cost.
>
> Uh, I think you mean while RAID 5 is LESS expensive than RAID 10?
>
>> --
>> Hilary Cotter
>> Looking for a SQL Server replication book?
>> http://www.nwsu.com/0974973602.html
>> Looking for a FAQ on Indexing Services/SQL FTS
>> http://www.indexserverfaq.com
>>
>> "jp" <jeremypw@.googlemail.com> wrote in message
>> news:1168248735.954175.305990@.s34g2000cwa.googlegroups.com...
>> We are looking to run a web based CRM package that runs on SQL 2005.
>> It has been recommend that we set the it up with RAID 5 however on
>> reading up on this it looks like RAID 10 could be better option.
>> The hard ware spec so far is for RAID 5
>> 2 * 72.6 GB Hard Drives for the operating system
>> 3 * 146.8 GB Hard Drives the database.
>> If were to go for RAID 10, as to requires equal number of drives I
>> presume that we would need to up it from 3 to 4.
>> What are other peoples experiences/views
>>
>|||Theoretical pros and cons aside, whether or not you need a three-plus-drive
RAID5 or a four-plus-drive RAID10 should be determined by the I/O
requirements of your app(s), and that only you or your app folks can answer.
You can look at some of your previous perfmon counters for clue.
Alternatively, you can run some tests to get an even more accurate estimate.
It's possisle that RAID5 can provide sufficient I/O throughput to meet your
requirements.
Linchi
"jp" wrote:
> We are looking to run a web based CRM package that runs on SQL 2005.
> It has been recommend that we set the it up with RAID 5 however on
> reading up on this it looks like RAID 10 could be better option.
> The hard ware spec so far is for RAID 5
> 2 * 72.6 GB Hard Drives for the operating system
> 3 * 146.8 GB Hard Drives the database.
> If were to go for RAID 10, as to requires equal number of drives I
> presume that we would need to up it from 3 to 4.
> What are other peoples experiences/views
>|||for writing process Raid 5 is MORE expensive from performance point of view
;-)
not from a money point of view where Raid 5 is cheaper then raid 10 for the
same usable disk space. (3 disks in raid 5 = 4 disks in Raid 10, so cheaper
for the same space but less performance)
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:u1D1dLyMHHA.2456@.TK2MSFTNGP06.phx.gbl...
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:%23Uzk0HxMHHA.5012@.TK2MSFTNGP02.phx.gbl...
>> If you have over 20% of your IO activity is write based IO you will get
>> better performance with RAID 10. While RAID 5 is more expensive than
>> RAID 10 typically the performance gains offset this added cost.
>
> Uh, I think you mean while RAID 5 is LESS expensive than RAID 10?
>
>> --
>> Hilary Cotter
>> Looking for a SQL Server replication book?
>> http://www.nwsu.com/0974973602.html
>> Looking for a FAQ on Indexing Services/SQL FTS
>> http://www.indexserverfaq.com
>>
>> "jp" <jeremypw@.googlemail.com> wrote in message
>> news:1168248735.954175.305990@.s34g2000cwa.googlegroups.com...
>> We are looking to run a web based CRM package that runs on SQL 2005.
>> It has been recommend that we set the it up with RAID 5 however on
>> reading up on this it looks like RAID 10 could be better option.
>> The hard ware spec so far is for RAID 5
>> 2 * 72.6 GB Hard Drives for the operating system
>> 3 * 146.8 GB Hard Drives the database.
>> If were to go for RAID 10, as to requires equal number of drives I
>> presume that we would need to up it from 3 to 4.
>> What are other peoples experiences/views
>>
>|||Thanks for the info
Linchi Shea wrote:
> Theoretical pros and cons aside, whether or not you need a three-plus-drive
> RAID5 or a four-plus-drive RAID10 should be determined by the I/O
> requirements of your app(s), and that only you or your app folks can answer.
> You can look at some of your previous perfmon counters for clue.
> Alternatively, you can run some tests to get an even more accurate estimate.
> It's possisle that RAID5 can provide sufficient I/O throughput to meet your
> requirements.
> Linchi
> "jp" wrote:
> > We are looking to run a web based CRM package that runs on SQL 2005.
> > It has been recommend that we set the it up with RAID 5 however on
> > reading up on this it looks like RAID 10 could be better option.
> >
> > The hard ware spec so far is for RAID 5
> > 2 * 72.6 GB Hard Drives for the operating system
> > 3 * 146.8 GB Hard Drives the database.
> >
> > If were to go for RAID 10, as to requires equal number of drives I
> > presume that we would need to up it from 3 to 4.
> >
> > What are other peoples experiences/views
> >
> >|||Thanks for the info
Linchi Shea wrote:
> Theoretical pros and cons aside, whether or not you need a three-plus-drive
> RAID5 or a four-plus-drive RAID10 should be determined by the I/O
> requirements of your app(s), and that only you or your app folks can answer.
> You can look at some of your previous perfmon counters for clue.
> Alternatively, you can run some tests to get an even more accurate estimate.
> It's possisle that RAID5 can provide sufficient I/O throughput to meet your
> requirements.
> Linchi
> "jp" wrote:
> > We are looking to run a web based CRM package that runs on SQL 2005.
> > It has been recommend that we set the it up with RAID 5 however on
> > reading up on this it looks like RAID 10 could be better option.
> >
> > The hard ware spec so far is for RAID 5
> > 2 * 72.6 GB Hard Drives for the operating system
> > 3 * 146.8 GB Hard Drives the database.
> >
> > If were to go for RAID 10, as to requires equal number of drives I
> > presume that we would need to up it from 3 to 4.
> >
> > What are other peoples experiences/views
> >
> >

Friday, March 23, 2012

R/W access problem with var in script task

Hi there

I have a a global variable for the package.

Now in script task of data flow I am trying to assign some value to it.

I have also defined same variable in Read/Write property of script task.

But at run time it gives me following error:

"The collection of variables locked for read and write access is not available outside of PostExecute."

How do we resolve this?

Thanks and Regards

Rahul Kumar

And in which overrided Sub are you trying to access this variable?

-Tom

|||

Hi Tom

I am doing all this in

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

|||

Try this:

Public Overloads Overrides Sub ProcessInput(ByVal inputID As Integer, ByVal buffer As PipelineBuffer)
If Buffer Is Nothing Then
Throw New ArgumentNullException("buffer")
End If
Dim vars As IDTSVariables90
Dim varServerName As IDTSVariable90
Dim varPackageName As IDTSVariable90

....

Try

...
Me.VariableDispenser.LockForRead("ServerName")

...
Me.VariableDispenser.GetVariables(vars)
varServerName = vars.Item(0) (0 because this was the first variable you've locked)

...

catch ....

|||

You can find lots of examples by just typing in msnsearch "using variable in script component"

like for example:

http://msdn2.microsoft.com/en-us/library/aa337079.aspx

-Tom

|||

Tom, your code sample is not actually for a Script Component, rather a full custom component.

Rahul, to be clear, when using the variable lock lists provide in the UI of the the Script Component you cannot access variables in the process input member (MyBuffer_ProcessInputRow). You can access read-only variables in the PreExecute and read-write variables in PostExecute

From the BOL link in Tom's second post -

Important:

The collection of ReadWriteVariables is only available in the PostExecute method to maximize performance and minimize the risk of locking conflicts.

If you do the locking yourself though you get more control. Try this sample code, but create the variables first if you try and run it.

Public Class ScriptMain

Inherits UserComponent

Private counter As Integer

Public Overrides Sub PreExecute()

' Initialise Counter from RO variable

counter = Me.Variables.VariableRO

MyBase.PreExecute()

End Sub

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

' Increment Counter

counter = counter + 1

' Use manual locking to set variable in Process Input

Dim variables As IDTSVariables90

Me.VariableDispenser.LockOneForRead("VariableRWManual", variables)

variables(0).Value = 125

variables.Unlock()

End Sub

Public Overrides Sub PostExecute()

' Store updated counter in RW variable

Me.Variables.VariableRW = counter

' Set a value for testing using manual locking

Dim variables As IDTSVariables90

Me.VariableDispenser.LockOneForRead("Variable", variables)

variables(0).Value = 5

variables.Unlock()

MyBase.PostExecute()

End Sub

End Class

|||

Hi Darren

Great Help!! thanks,

I got my work done.

Regards

Rahul Kumar,Software Engineer,India

sql

R/W access problem with var in script Component

Hi there

I have a a global variable for the package.

Now in script task of data flow I am trying to assign some value to it.

I have also defined same variable in Read/Write property of script task.

But at run time it gives me following error:

"The collection of variables locked for read and write access is not available outside of PostExecute."

How do we resolve this?

Thanks and Regards

Rahul Kumar

And in which overrided Sub are you trying to access this variable?

-Tom

|||

Hi Tom

I am doing all this in

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

|||

Try this:

....

Try

...
Me.VariableDispenser.LockForRead("ServerName")

...
Me.VariableDispenser.GetVariables(vars)
varServerName = vars.Item(0) (0 because this was the first variable you've locked)

...

catch ....

|||

You can find lots of examples by just typing in msnsearch "using variable in script component"

like for example:

http://msdn2.microsoft.com/en-us/library/aa337079.aspx

-Tom

|||

Tom, your code sample is not actually for a Script Component, rather a full custom component.

From the BOL link in Tom's second post -

Important:

The collection of ReadWriteVariables is only available in the PostExecute method to maximize performance and minimize the risk of locking conflicts.

If you do the locking yourself though you get more control. Try this sample code, but create the variables first if you try and run it.

Public Class ScriptMain

Inherits UserComponent

Private counter As Integer

Public Overrides Sub PreExecute()

' Initialise Counter from RO variable

counter = Me.Variables.VariableRO

MyBase.PreExecute()

End Sub

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

' Increment Counter

counter = counter + 1

' Use manual locking to set variable in Process Input

Dim variables As IDTSVariables90

Me.VariableDispenser.LockOneForRead("VariableRWManual", variables)

variables(0).Value = 125

variables.Unlock()

End Sub

Public Overrides Sub PostExecute()

' Store updated counter in RW variable

Me.Variables.VariableRW = counter

' Set a value for testing using manual locking

Dim variables As IDTSVariables90

Me.VariableDispenser.LockOneForRead("Variable", variables)

variables(0).Value = 5

variables.Unlock()

MyBase.PostExecute()

End Sub

End Class

|||

Hi Darren

Great Help!! thanks,

I got my work done.

Regards

Rahul Kumar,Software Engineer,India

Friday, March 9, 2012

Quick Question regarding number of instances...

I am developing an application for a client who already has a liscensed SQL server running. They have a software package that requires an instance using "Windows Only" for the authentication mode. I need an instance to use "SQL Server and Windows" for my application to be able to speak with the server from other computers on their network (or at least I believe I do...).

My question is, "is there any restrictions on the 'number' of instances an institution can run on a computer that is liscensed to run the software"?

Alternately, if there is a limit, I need to figure out if it is possible to set up the application (VB6, btw) to connect to the server from outside the host computer if it is using "Windows Only" Authentication. The application will be installed on several computers in the client's network and I'm not familiar with how to dynamicly poll the 'current Windows Login' information.

It would be much simpler for the application to create a new instance with the desired authentication method, but is there a security issue in doing this? Would the data be any more at risk to outside corruption using the SQL server authentication mode?Number of named instances if default instance is already present is 15. Total number of instances including default instance cannot exceed 16.

Windows authentication is safer simply because there is no need to provide user ID and password in clear text. If you use OLE DB Provider then "Intgrated Security=SSPI'and for DSN-less connections, - "Trusted_Connection=Yes"

Wednesday, March 7, 2012

Quick Question - Need some advice on troubleshooting SSIS Errors

Hi Everyone:

I have a SSIS Package which in brief moves data from one SQL data store to another. On my local machine, the package executes fines, and does what it is supposed to do, by moving data from Point A to Point B. I have both Point A(DB) and Point B(DB) on my local machine. But when I deploy this package over to ASSEMBLY which is basically another environment, I get weird intermittent errors. What should I do? Can someone give me some tips on how to do good error logging in SSIS? I am currently writing to Windows Event Log, and I seem to look at some errors that are coming thru. What else can I do on SSIS Package side, to improve error reporting and handling, so troubleshooting is faster and more effective. I just need some stable advice on how to setup my package to do error logging where troubleshooting issues in different environments is more effective. Also I need to know, what could be causing these issues? Thanks.

Try turning on package logging. Set it to log to SQL Server. It will then be in the chosen databases' sysdtslog90 table.

Right click on the control flow background to get at package logging.|||

There are plenty of resources out there....

SSIS Logging

http://www.databasejournal.com/features/mssql/article.php/3562406

http://msdn2.microsoft.com/en-us/library/ms167456.aspx

http://www.microsoft.com/downloads/details.aspx?FamilyID=526e1fce-7ad5-4a54-b62c-13ffcd114a73&DisplayLang=en

Custom Logging:

http://blogs.conchango.com/jamiethomson/archive/2005/06/11/SSIS_3A00_-Custom-Logging-Using-Event-Handlers.aspx

|||

Do i have to specify this table in the configuration column when setting up the log provider to write to SQL Server tables? Where do i find sysdtslog90 table? Thanks.

|||

MA2005 wrote:

Do i have to specify this table in the configuration column when setting up the log provider to write to SQL Server tables? Where do i find sysdtslog90 table? Thanks.

When you set up logging, you have to chose a database/connection. In that database you'll find the sysdtslog90 table.|||

Here is the error I keep getting, which I am not getting in my Local Machine Environment. Hmmmm... any ideas?

Executing the query "exec dbo.uspSaveMain_CEAImport ?, ?, ? OUTPUT" failed with the following error: "Grantor does not have GRANT permission.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set

correctly, or connection not established correctly.

|||It looks like your user lacks some DB permission in the env where you are pointing to.

Wednesday, March 28, 2012

raid 5 v raid 10

raid 5 v raid 10

raid 5 v raid 10

Friday, March 23, 2012

R/W access problem with var in script task

R/W access problem with var in script Component

Friday, March 9, 2012

Quick Question regarding number of instances...

Wednesday, March 7, 2012

Quick Question - Need some advice on troubleshooting SSIS Errors

rda submitsql

Blog Archive

About Me