binding SQL server to localhost?

Greetings all,
I am a network security professional rather than a MS SQL admin, so I
apologize in advance if this is a bit of a basic question for this
list. I know an admin setting up a SQL server that will only be
accesible by a webserver running on the same host (not happy about
running private vs publicly avaialable services on the same host , but
it's what we've got). As such, I'd like to recommend to him that the
SQL server only listen on the localhost ip, 127.0.0.1, thereby making
it inaccesible to the outside world. I looked around the MS
knowledgebase but couldn't find a clear document stating how to do
this. Is it even possible? Is there a better option for this
configuration?
Thanks,
BrianYou propably need to firewall the server then.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
<tinbox@.nyct.net> schrieb im Newsbeitrag
news:1114053613.247153.87700@.o13g2000cwo.googlegroups.com...
> Greetings all,
> I am a network security professional rather than a MS SQL admin, so I
> apologize in advance if this is a bit of a basic question for this
> list. I know an admin setting up a SQL server that will only be
> accesible by a webserver running on the same host (not happy about
> running private vs publicly avaialable services on the same host , but
> it's what we've got). As such, I'd like to recommend to him that the
> SQL server only listen on the localhost ip, 127.0.0.1, thereby making
> it inaccesible to the outside world. I looked around the MS
> knowledgebase but couldn't find a clear document stating how to do
> this. Is it even possible? Is there a better option for this
> configuration?
> Thanks,
> Brian
>

binding SQL server to localhost?

Greetings all,
I am a network security professional rather than a MS SQL admin, so I
apologize in advance if this is a bit of a basic question for this
list. I know an admin setting up a SQL server that will only be
accesible by a webserver running on the same host (not happy about
running private vs publicly avaialable services on the same host , but
it's what we've got). As such, I'd like to recommend to him that the
SQL server only listen on the localhost ip, 127.0.0.1, thereby making
it inaccesible to the outside world. I looked around the MS
knowledgebase but couldn't find a clear document stating how to do
this. Is it even possible? Is there a better option for this
configuration?
Thanks,
Brian
You propably need to firewall the server then.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
<tinbox@.nyct.net> schrieb im Newsbeitrag
news:1114053613.247153.87700@.o13g2000cwo.googlegro ups.com...
> Greetings all,
> I am a network security professional rather than a MS SQL admin, so I
> apologize in advance if this is a bit of a basic question for this
> list. I know an admin setting up a SQL server that will only be
> accesible by a webserver running on the same host (not happy about
> running private vs publicly avaialable services on the same host , but
> it's what we've got). As such, I'd like to recommend to him that the
> SQL server only listen on the localhost ip, 127.0.0.1, thereby making
> it inaccesible to the outside world. I looked around the MS
> knowledgebase but couldn't find a clear document stating how to do
> this. Is it even possible? Is there a better option for this
> configuration?
> Thanks,
> Brian
>

binding SQL server to localhost?

Greetings all,
I am a network security professional rather than a MS SQL admin, so I
apologize in advance if this is a bit of a basic question for this
list. I know an admin setting up a SQL server that will only be
accesible by a webserver running on the same host (not happy about
running private vs publicly avaialable services on the same host , but
it's what we've got). As such, I'd like to recommend to him that the
SQL server only listen on the localhost ip, 127.0.0.1, thereby making
it inaccesible to the outside world. I looked around the MS
knowledgebase but couldn't find a clear document stating how to do
this. Is it even possible? Is there a better option for this
configuration?
Thanks,
BrianYou propably need to firewall the server then.
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
<tinbox@.nyct.net> schrieb im Newsbeitrag
news:1114053613.247153.87700@.o13g2000cwo.googlegroups.com...
> Greetings all,
> I am a network security professional rather than a MS SQL admin, so I
> apologize in advance if this is a bit of a basic question for this
> list. I know an admin setting up a SQL server that will only be
> accesible by a webserver running on the same host (not happy about
> running private vs publicly avaialable services on the same host , but
> it's what we've got). As such, I'd like to recommend to him that the
> SQL server only listen on the localhost ip, 127.0.0.1, thereby making
> it inaccesible to the outside world. I looked around the MS
> knowledgebase but couldn't find a clear document stating how to do
> this. Is it even possible? Is there a better option for this
> configuration?
> Thanks,
> Brian
>sql

binding SQL server to localhost?

Greetings all,

I am a network security professional rather than a MS SQL admin, so I
apologize in advance if this is a bit of a basic question for this
list. I also cross-posted this to microsoft.public.sqlserver.server,
so sorry if anyone's read it already.

I know an admin setting up a SQL server that will only be
accesible by a webserver running on the same host (not happy about
running private vs publicly avaialable services on the same host , but
it's what we've got). As such, I'd like to recommend to him that the
SQL server only listen on the localhost ip, 127.0.0.1, thereby making
it inaccesible to the outside world. I looked around the MS
knowledgebase but couldn't find a clear document stating how to do
this. Is it even possible? Is there a better option for this
configuration?

It's been suggested that firewalling is the only option, but I'd really
like to do *both* (firewall & bind to localhost). The firewall in this
case will have to be host-based instead (software) instead of hardware
for non-technical reasons, so additionally if anyone recommends a
software firewall they use for this purpose I'd appreciate it. My firs
impulse is to recommend Tiny, but I've never used a software firewall
for an MS SQL/Web server before.

Thanks,
BrianOne option is to disable network access completely, and use only shared
memory for access to MSSQL (this is how MSDE operates by default since
SP3), so only applications running on the same machine will be able to
access it. Although someone could still attack MSSQL by compromising
the web server, or using SQL injection.

MSSQL itself doesn't provide any way to accept connections from
specified hosts - you would normally use the operating system's IP
filtering functions to do that.

Simon|||There isn't a way to have sql server listen on a specific ip. Sorry.

If you haven't already seen this, take a look at the Network
Configuration dialog on the general tab of the server's properties.
Two protocols are enabled by default. TCPIP is one of them, and you
can change the port and set it to ignore discovery broadcasts. That
would make the server invisible to anyone looking for it. However,
anyone who portscans the server would notice whatever port you put it
on and (I'd assume) be able to figure out that it's a SQL Server. So,
you do still need to firewall it.

The other protocol is called Named Pipes. That's basically using
memory to communicate. If the application you're developing supports
it, I'd suggest using this and turning off TCPIP support altogether.
This is probably faster anyway.

Chris

binary_checksum and validated software environments

Hi,

It appears that binary_checksum can give the same checksum for
different strings, which is a bit worrying. (I guess the algorithm is
the problem in the context of a repeating pattern.)

e.g.
select binary_checksum('A')
,binary_checksum('AAAAAAAAAAAAAAAAA')
,binary_checksum('AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA A')

,binary_checksum('AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAA')

My question...
Is this approach to generating checksums adequate for managing the
object scripts in the SQL Server to ensure that they haven't changed. I
guess that the probability of somebody making a change to a script and
ending up with the same checksum is almost negligible. Has anybody used
this approach in an FDA validated production environment, i.e. 'no ifs,
no buts'? Would it stand up to scrutiny?

Any experiences, thoughts?

Regards

LiamChecksums do not guarantee unique values for different string, it is a
rotating add algorithm, since it is based on XOR ing values and only returns
a very limited set of possible values (the range of int), so you will get
collisions. You will also find that the amount of collisions it is very
depending on the collation sequence used. This is why SQL Server 2005
introduces the HashBytes function.

GertD@.SQLDev.Net

Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2005 All rights reserved.

"Liam Caffrey" <liam.caffrey@.gmail.com> wrote in message
news:1117749763.058901.177080@.g49g2000cwa.googlegr oups.com...
> Hi,
> It appears that binary_checksum can give the same checksum for
> different strings, which is a bit worrying. (I guess the algorithm is
> the problem in the context of a repeating pattern.)
> e.g.
> select binary_checksum('A')
> ,binary_checksum('AAAAAAAAAAAAAAAAA')
> ,binary_checksum('AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA A')
> ,binary_checksum('AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAA')
> My question...
> Is this approach to generating checksums adequate for managing the
> object scripts in the SQL Server to ensure that they haven't changed. I
> guess that the probability of somebody making a change to a script and
> ending up with the same checksum is almost negligible. Has anybody used
> this approach in an FDA validated production environment, i.e. 'no ifs,
> no buts'? Would it stand up to scrutiny?
> Any experiences, thoughts?
> Regards
> Liam|||So an unchanged checksum does not guarantee that the data has not
changed. That seems to be clear.

However, I would guess that the chances of (a) someone making a change
to a script and (b) that change delivering functionality that can be
compiled and be meaningful, is fairly, if not very remote, as to be
irrelevant.

Does anybody use the SQL Server checksum for maintaining audit control
of database objects?

Regards

Liam|||Hi Liam,

Yes, I have seen the checksum used for audting database changes on one
of my previous projects. It was used as a 'belt and braces'
verification mechanism to ensure that the database change management
solution (DB Ghost www.dbghost.com) worked as expected. i.e. it
upgraded a target database to be the same as a source database that was
built from scripts in a source control system.

I'm happy to report (because I now work for Innovartis, the makers of
DB Ghost) that a) the checksum function itself never gave any false
positives during the entire time (2 years) that I worked there and b)
DB Ghost never failed either.

The only false positives produced were due to the way SQL Server
stores/reproduces the text for objects such as Stored Procedures/views
etc. in syscomments Scripting the same object on two databases gives
annoying whitespace differences that are suprisingly difficult to
remove. However, that is not to say this is unsurmountable, it was
just deemed as a waste of time to investigate given that it only
occured once in a blue moon.

As you seem to understand the need for ultimate auditability and
traceability for changes to SQL Server then it would be worth your
while looking at DB Ghost. It's the final piece that makes full
lifecycle configuration management possible for SQL Server code changes
i.e. it will allow you to trace a change to the production schema all
the way back through development to the actual business case that
caused it. This assumes that you already have in place a configuration
management system of some kind.

Regs,

Malc|||> I would guess that the chances of (a) someone making a change
> to a script and (b) that change delivering functionality that can be
> compiled and be meaningful, is fairly, if not very remote, as to be
> irrelevant.

You would be wrong. It's trivially easy to find checksum collisions.

SELECT BINARY_CHECKSUM('ABA'), BINARY_CHECKSUM('ACQ')

Result:

---- ----
17761 17761

(1 row(s) affected)

As a further test I ran the following query on the syscomments table of
a database containing about 200 procs, views, etc. and got 3 rows
returned:

SELECT DISTINCT OBJECT_NAME(T1.id), BINARY_CHECKSUM(T1.text)
FROM syscomments AS T1
JOIN syscomments AS T2
ON BINARY_CHECKSUM(T1.text) = BINARY_CHECKSUM(T2.text)
AND T1.text <> T2.text

This result is perfectly natural and expected when you consider that
there are "only" 2^32 possible checksums - many orders of magnitude
fewer than the number of syntactically valid and useful pieces of
script.

--
David Portas
SQL Server MVP
--|||Liam Caffrey (liam.caffrey@.gmail.com) writes:
> So an unchanged checksum does not guarantee that the data has not
> changed. That seems to be clear.
> However, I would guess that the chances of (a) someone making a change
> to a script and (b) that change delivering functionality that can be
> compiled and be meaningful, is fairly, if not very remote, as to be
> irrelevant.
> Does anybody use the SQL Server checksum for maintaining audit control
> of database objects?

When I was building a set of tables for our load tool, one thing I
looking for, was a way to track if someone had loaded an object outside
the load tool. That is, the load tool would track all loads in a table,
but would of course not track if someone tried to load an object through
Query Analyzer or somesuch.

I was indeed considering using
CHECKSUM_AGG(BINARY_CHECKSUM(syscomments.text)). The reason I eventually
didn't was that I wanted to support all three of SQL 6.5 and SQL 7 and
SQL 2000, and the troublesome here was SQL 7, which does not have
the checksum functions, but does have ALTER PROCEDURE which does not leave
a trace in sysobjects.

So eventually, I found no other way to rely on the columns crdate and
schema_ver in sysobjects. I found that for each ALTER PROCEDURE,
schema_ver is incremented with 16. This is not very documented, though.
Since I had to go with this on SQL 7, I figured I could just as well
use it on SQL 2000 as well.

On SQL 2005 it's easier. There is now a sys.objects.modified_date,
horray!

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Binary Primary Key

I have a 256 bit hash value to identify chunks of data. Would it be a good idea to create a 32-byte binary field as the primary key or encode the bytes to a string?

Well, to me if your natural key really is binary then that is the way I would designate the primary key -- as binary. I'd still like to hear other opinions, though

BigInt performance on SQL 64

Has anyone done some benchmarks of BigInt vs. Int on SQL Server
2000/2005 64 bit? If so I would like to know what your conclusion was.Performance in regards to what? A bigint is twice the size of an Int so it
will take up twice the memory and disk space for tables and indexes. You
really shouldn't be looking at performance to make a decision such as this.
You need to consider the data that you will be storing in that column. What
will the business rules dictate. Why use a BigInt when an Int will suffice?
Why use an Int if it can't hold your largest value?
Andrew J. Kelly SQL MVP
"Will" <whbmitchell@.gmail.com> wrote in message
news:1139525534.880875.88630@.o13g2000cwo.googlegroups.com...
> Has anyone done some benchmarks of BigInt vs. Int on SQL Server
> 2000/2005 64 bit? If so I would like to know what your conclusion was.
>|||I have. BigInt will store a much larger number than Int will. So storing a
really big number in and Int will be much slower than with a BigInt :)
Seriously, I too would like to hear if there is any difference.
Since it is just 8 bytes instead of 4 bytes, it will be a bit slower, for
many reasons on any version because at the very least a bit more disk space
will be required. Fact is, if you need a BigInt, use it, otherwise use an
int. I wouldn't expect to do much math on a number > 2 billion, (certainly
not extensively,) and that is where a minute difference might be noticed
based on 32 or 64 bit hardware, though even on 64 bit hardware the Int
"shouldn't" be slower than the BigInt, since math would be done in the same
hardware, just probably no "tricks" to deal with the number that is bigger
than the native registers.
Do you have any specific instances where you think it was slower?
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Will" <whbmitchell@.gmail.com> wrote in message
news:1139525534.880875.88630@.o13g2000cwo.googlegroups.com...
> Has anyone done some benchmarks of BigInt vs. Int on SQL Server
> 2000/2005 64 bit? If so I would like to know what your conclusion was.
>

BI Strategy

I have been charged with implementing a BI strategy for my company. As I am new to BI I have been researching BI quite a bit but still feel overwelmed by all the information out there. As far as I have researched, I found many front-end tools that my company can use. I would like to set up some sort of data warehouse. My company will be using scorecards, dashboards, mining, KPIs and analytics. Most of my data is in SQL 2000, some is in SQL 2005. I am looking for the best process to setup the data warehouse and am having a hard time finding the right approach. Some people suggest cubes and others suggest to stay away from cubes and use an OLAP process. I do need a way to keep the data up to date as well, preferably at least daily. Does anyone have thoughts on an approach or 2?

This site(http://www.kimballgroup.com/) is my primary reference for DW-Architechture.

The Kimball group have a book that combines their general project approach(that is above all suppliers) with the approach of how to implement it on the Microsoft BI-platform.

Have a look at "The Microsoft DataWareHouse Toolkit".

HTH

Thomas Ivarsson

|||

Hello again Shawn. A cube and OLAP is the same. If you update your warehouse daily a cube can solve part of your business problem.

Never start a scorecard project without having a the experience of building a data warehouse before that.

HTH

Thomas Ivarsson

|||

Shawn,

6 months ago I was pretty much in the same boat as you. I found that book that Thomas mentioned superb in helping me to implement our DW solution using Microsoft BI Toolkit. I can highly recommend it.

What you should also do is to (having gotten an overview from the Kimball book) buy books on the various specialist areas, i.e. SSAS, SSRS, SSIS - here i found the ones from WROX to be very useful..

Professional SQL Server 2005 Integration Services

Professional SQL Server 2005 Analysis Services

Professional SQL Server 2005 Reporting Services

As they tend to go in to alot more detail than Kimball's book (which is more conceptual) and provide code & examples to show you how it's done "step by step"

BI Strategy

I have been charged with implementing a BI strategy for my company. As I am new to BI I have been researching BI quite a bit but still feel overwelmed by all the information out there. As far as I have researched, I found many front-end tools that my company can use. I would like to set up some sort of data warehouse. My company will be using scorecards, dashboards, mining, KPIs and analytics. Most of my data is in SQL 2000, some is in SQL 2005. I am looking for the best process to setup the data warehouse and am having a hard time finding the right approach. Some people suggest cubes and others suggest to stay away from cubes and use an OLAP process. I do need a way to keep the data up to date as well, preferably at least daily. Does anyone have thoughts on an approach or 2?

This site(http://www.kimballgroup.com/) is my primary reference for DW-Architechture.

The Kimball group have a book that combines their general project approach(that is above all suppliers) with the approach of how to implement it on the Microsoft BI-platform.

Have a look at "The Microsoft DataWareHouse Toolkit".

HTH

Thomas Ivarsson

|||

Hello again Shawn. A cube and OLAP is the same. If you update your warehouse daily a cube can solve part of your business problem.

Never start a scorecard project without having a the experience of building a data warehouse before that.

HTH

Thomas Ivarsson

|||

Shawn,

6 months ago I was pretty much in the same boat as you. I found that book that Thomas mentioned superb in helping me to implement our DW solution using Microsoft BI Toolkit. I can highly recommend it.

What you should also do is to (having gotten an overview from the Kimball book) buy books on the various specialist areas, i.e. SSAS, SSRS, SSIS - here i found the ones from WROX to be very useful..

Professional SQL Server 2005 Integration Services

Professional SQL Server 2005 Analysis Services

Professional SQL Server 2005 Reporting Services

As they tend to go in to alot more detail than Kimball's book (which is more conceptual) and provide code & examples to show you how it's done "step by step"

BI Portal 2003 on a 64 Bit Machine

Hello Everyone,

Has anyoen tried to use BI Portal on a 64 bit machine with a 64 bit SQL Server? Any problems?

Thanks,

Joseph

Not sure what product you refer to. This forum is about to SQL Server Analysis Services related issues.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

BI Portal is microsoft's addition to sharepoint (web parts) in able to create pages that connect directly to Analysis Services cubes. We've used it before on a 32 bit server connecting to Analysis Server 2005.

I was just wondering if anyone has tried to use BI Portal on a 64 bit machine to connect to a 64 bit Analysis Server of SQL 2005.

BI Dev Studio - Calculations - Error in Application

Hi!

New Windows Server 2003 R2 64 Bit EN installation with new SQL Server 2005 DEV SP1 DE installation. While we open BI Development Studio and goto Cube and Calculations we do not get the Calculations Designer - instead we have an "Error in the application" error message. In the Source XML we can view the calculations, but cannot use the designer.

Is this a known issue?

Does someone know an solution to solve this issue? workarround?

Best regards

HANNES MAYER

Hello

You can probably find the answer in this post: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=756982&SiteID=1, I had the same problem, I think, and it's solved in that thread.

better sql join

A bit out of topic but here i can find the right person who explain me...

I have 3 tables: User(ID,Name),Customer(ID,Name),Order(CustomerID,UserID,Qty)

I want to know the difference (i think in performance because are both right) between:

SELECT U.Name, C.Name

FROM User AS U, Customer AS C, Order as O

WHERE C.ID=O.CustomerID AND U.ID=O.UserID

AND U.ID=5

AND C.ID=7

SELECT U.Name, C.Name

FROM User AS U, Customer AS C, Order as O

WHERE C.ID=O.CustomerID AND U.ID=O.UserID

AND O.UserID=5

AND O.CustomerID=7

Thx

Hi Manuel

As far as I can see you second query has mutually exclusive criteria

AND O.ID=5

AND O.ID=7

Did you mean AND ( O.ID=5 OR O.ID=7) ?

And as an additional suggestion, the quesry would be easier to read if it was written with use of INNER JOINs instead of conneting tables on thhe "WHERE" clause. Its behaviour is more consistent.

NB.

|||

ok for innerjoin..

I did an error:

I mean O.CustomerID =5

O.UserID =7

|||Manuel, in the case of:
select * from A, B where A.col1 = B.col1
if A is smaller and has a good index, I think SQL Server will chose A as the inner table. Because of this we should put our restrictive where clause on B, like this:
select * from A, B where A.col1 = B.col1 and B.col2 = 5

That will have a better performance than putting the restrictive clause on A like this:
select * from A, B where A.col1 = B.col1 and A.col2 = 5

As a general rule, I normally put the restrictive where clause on the foreign key (FK)table, not on the primary key (PK) table. This is because I think SQL Server will choose the PK table as the inner table. Somebody from Microsoft SQL Server development team would be able to explain more about SQL Server's nested loop join plan strategy.

To understand what is inner table and outer table, I would recommend you to read page 90 of Peter Gulutzan's book, SQL Performance Tuning. Basically, the table size affect number of page read from disk. If the table is small, there is more chance to put all the pages in the cache.

Vincent|||

Thx for reply.

Expect Microsoft? Bah!

And if i'm not using SQLServer?

|||? Hi Manuel, Did you try both? Have you checked the execution plans to see if they are any different? I just created a small test (with a similar pattern, but on only two tables) and in my case both execution plans where exactly the same. If you observed different execution plan, I'd like to see some SQL to reproduce it here! -- Hugo Kornelis, SQL Server MVP <manuel0081@.discussions.microsoft.com> schreef in bericht news:5278193f-98a0-47a3-8978-416cf71318d5_WBRev3_@.discussions..microsoft.com...This post has been edited either by the author or a moderator in the Microsoft Forums: http://forums.microsoft.com A bit out of topic but here i can find the right person who explain me... I have 3 tables: User(ID,Name),Customer(ID,Name),Order(CustomerID,UserID,Qty) I want to know the difference (i think in performance because are both right) between: SELECT U.Name, C.Name FROM User AS U, Customer AS C, Order as O WHERE C.ID=O.CustomerID AND U.ID=O.UserID AND U.ID=5 AND C.ID=7 SELECT U.Name, C.Name FROM User AS U, Customer AS C, Order as O WHERE C.ID=O.CustomerID AND U.ID=O.UserID AND O.UserID=5 AND O.CustomerID=7 Thx

Best way to do batch inserts

All -
I've read up quite a bit on simply doing batch inserts where the scenario
involves inserting a good number of records (maybe a 1000?) at single time.
I definitely do not want to use bulk inserts, so let's rule that out
immediately. It is also my opinion that is not efficient to pass the
"values" as string (or whatever) and have a loop in a stored proc that
parses the string an the insert repeatedly (if anyone disagrees with this
statement, please let me know).
Having concluded the above, it leaves me with two options:
1.Concate a string of insert/values statements
2.Concate a string of insert/select/union all statements where the
"select/union all" represents my values.
First of all does anyone have any metrics as to which of the approaches
above peforms better?
Secondly, in a multi-user, concurrent environment, which is better?
a)Putting all inserts in a single transaction
b)Putting each insert in a single transaction
I understand that there is a lot more I/O with option (b), but I'm of the
opinion that option (a) can lead to table locking etc. Which should I worry
about more?
For now, these are my questions. I'm not looking for the magic bullet here,
I will do some testing myself, but I wanted to poll the minds of the smart
folk who read these posts to see if anyone had already done this or had
opinions
Thanks.
iceA 1000 rows at a time is not much of a challenge. I don't understand why
you've ruled out bulk inserts. They can be very fast. If you don't want to
insert your data directly into the target tables via bulk insert, you could
load staging tables that way and then transfer over via SQL.
As for larger inserts, it boils down to how much concurrency do you need?
If you can take some downtime, then it is faster to do things in a single
chunk (some exceptions do apply). However, if you can't afford to be down
for too long, then you may have to do a bunch of min-transactions with, say,
100 - 1000 rows at a time.
HTH
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"Ice" <ice@.nospam.com> wrote in message
news:uR0lcN9JFHA.3500@.TK2MSFTNGP14.phx.gbl...
All -
I've read up quite a bit on simply doing batch inserts where the scenario
involves inserting a good number of records (maybe a 1000?) at single time.
I definitely do not want to use bulk inserts, so let's rule that out
immediately. It is also my opinion that is not efficient to pass the
"values" as string (or whatever) and have a loop in a stored proc that
parses the string an the insert repeatedly (if anyone disagrees with this
statement, please let me know).
Having concluded the above, it leaves me with two options:
1.Concate a string of insert/values statements
2.Concate a string of insert/select/union all statements where the
"select/union all" represents my values.
First of all does anyone have any metrics as to which of the approaches
above peforms better?
Secondly, in a multi-user, concurrent environment, which is better?
a)Putting all inserts in a single transaction
b)Putting each insert in a single transaction
I understand that there is a lot more I/O with option (b), but I'm of the
opinion that option (a) can lead to table locking etc. Which should I worry
about more?
For now, these are my questions. I'm not looking for the magic bullet here,
I will do some testing myself, but I wanted to poll the minds of the smart
folk who read these posts to see if anyone had already done this or had
opinions
Thanks.
ice|||Thanks for your quick reply.
I know that "Bulk Inserts" will get this done fast. But I'm looking for a
generic solution that would work for different databases without having to
use their specific implementations to solve the problem. I think using the
"staging" idea in this scenario is probably adding more work than might be
necessary. When you say "via SQL" what are you suggesting I use?
With regards to concurrency and downtime, I want to operate under assumption
that there will be no downtime. So based on that, it would seem that you
are suggesting that inserting ~1000 records in a single transaction is
better than ~1000 individual transactions. I am correct with this
deduction?
ice
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:#4LF2W9JFHA.1280@.TK2MSFTNGP09.phx.gbl...
> A 1000 rows at a time is not much of a challenge. I don't understand why
> you've ruled out bulk inserts. They can be very fast. If you don't want
to
> insert your data directly into the target tables via bulk insert, you
could
> load staging tables that way and then transfer over via SQL.
> As for larger inserts, it boils down to how much concurrency do you need?
> If you can take some downtime, then it is faster to do things in a single
> chunk (some exceptions do apply). However, if you can't afford to be down
> for too long, then you may have to do a bunch of min-transactions with,
say,
> 100 - 1000 rows at a time.
> HTH
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
> .
> "Ice" <ice@.nospam.com> wrote in message
> news:uR0lcN9JFHA.3500@.TK2MSFTNGP14.phx.gbl...
> All -
> I've read up quite a bit on simply doing batch inserts where the scenario
> involves inserting a good number of records (maybe a 1000?) at single
time.
> I definitely do not want to use bulk inserts, so let's rule that out
> immediately. It is also my opinion that is not efficient to pass the
> "values" as string (or whatever) and have a loop in a stored proc that
> parses the string an the insert repeatedly (if anyone disagrees with this
> statement, please let me know).
> Having concluded the above, it leaves me with two options:
> 1.Concate a string of insert/values statements
> 2.Concate a string of insert/select/union all statements where the
> "select/union all" represents my values.
> First of all does anyone have any metrics as to which of the approaches
> above peforms better?
> Secondly, in a multi-user, concurrent environment, which is better?
> a)Putting all inserts in a single transaction
> b)Putting each insert in a single transaction
> I understand that there is a lot more I/O with option (b), but I'm of the
> opinion that option (a) can lead to table locking etc. Which should I
worry
> about more?
> For now, these are my questions. I'm not looking for the magic bullet
here,
> I will do some testing myself, but I wanted to poll the minds of the smart
> folk who read these posts to see if anyone had already done this or had
> opinions
> Thanks.
> ice
>|||> First of all does anyone have any metrics as to which of the approaches
> above peforms better?
To add to Tom's response, I ran a quick test from Query Analyzer and found
the separate inserts outperformed the UNION ALL method when SET NOCOUNT ON
was specified.
Hope this helps.
Dan Guzman
SQL Server MVP
"Ice" <ice@.nospam.com> wrote in message
news:uR0lcN9JFHA.3500@.TK2MSFTNGP14.phx.gbl...
> All -
> I've read up quite a bit on simply doing batch inserts where the scenario
> involves inserting a good number of records (maybe a 1000?) at single
> time.
> I definitely do not want to use bulk inserts, so let's rule that out
> immediately. It is also my opinion that is not efficient to pass the
> "values" as string (or whatever) and have a loop in a stored proc that
> parses the string an the insert repeatedly (if anyone disagrees with this
> statement, please let me know).
> Having concluded the above, it leaves me with two options:
> 1.Concate a string of insert/values statements
> 2.Concate a string of insert/select/union all statements where the
> "select/union all" represents my values.
> First of all does anyone have any metrics as to which of the approaches
> above peforms better?
> Secondly, in a multi-user, concurrent environment, which is better?
> a)Putting all inserts in a single transaction
> b)Putting each insert in a single transaction
> I understand that there is a lot more I/O with option (b), but I'm of the
> opinion that option (a) can lead to table locking etc. Which should I
> worry
> about more?
> For now, these are my questions. I'm not looking for the magic bullet
> here,
> I will do some testing myself, but I wanted to poll the minds of the smart
> folk who read these posts to see if anyone had already done this or had
> opinions
> Thanks.
> ice
>|||Doing 1000 individual transaction will take longer end-to-end. However,
each little insert is likely to take not much time by itself. A 1000 row
insert would take longer than a single-row insert. However, a 1000 row
insert should be of the order of a second or two. If you want to insert,
say, a million rows, then you may want to do that in 1000 row batches.
Using SQL, you would be doing an INSERT SELECT from one table into another.
How are these data arriving into your system - DTS, Linked server, flat
file?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"Ice" <ice@.nospam.com> wrote in message
news:%23MY27B%23JFHA.484@.TK2MSFTNGP15.phx.gbl...
Thanks for your quick reply.
I know that "Bulk Inserts" will get this done fast. But I'm looking for a
generic solution that would work for different databases without having to
use their specific implementations to solve the problem. I think using the
"staging" idea in this scenario is probably adding more work than might be
necessary. When you say "via SQL" what are you suggesting I use?
With regards to concurrency and downtime, I want to operate under assumption
that there will be no downtime. So based on that, it would seem that you
are suggesting that inserting ~1000 records in a single transaction is
better than ~1000 individual transactions. I am correct with this
deduction?
ice
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:#4LF2W9JFHA.1280@.TK2MSFTNGP09.phx.gbl...
> A 1000 rows at a time is not much of a challenge. I don't understand why
> you've ruled out bulk inserts. They can be very fast. If you don't want
to
> insert your data directly into the target tables via bulk insert, you
could
> load staging tables that way and then transfer over via SQL.
> As for larger inserts, it boils down to how much concurrency do you need?
> If you can take some downtime, then it is faster to do things in a single
> chunk (some exceptions do apply). However, if you can't afford to be down
> for too long, then you may have to do a bunch of min-transactions with,
say,
> 100 - 1000 rows at a time.
> HTH
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
> .
> "Ice" <ice@.nospam.com> wrote in message
> news:uR0lcN9JFHA.3500@.TK2MSFTNGP14.phx.gbl...
> All -
> I've read up quite a bit on simply doing batch inserts where the scenario
> involves inserting a good number of records (maybe a 1000?) at single
time.
> I definitely do not want to use bulk inserts, so let's rule that out
> immediately. It is also my opinion that is not efficient to pass the
> "values" as string (or whatever) and have a loop in a stored proc that
> parses the string an the insert repeatedly (if anyone disagrees with this
> statement, please let me know).
> Having concluded the above, it leaves me with two options:
> 1.Concate a string of insert/values statements
> 2.Concate a string of insert/select/union all statements where the
> "select/union all" represents my values.
> First of all does anyone have any metrics as to which of the approaches
> above peforms better?
> Secondly, in a multi-user, concurrent environment, which is better?
> a)Putting all inserts in a single transaction
> b)Putting each insert in a single transaction
> I understand that there is a lot more I/O with option (b), but I'm of the
> opinion that option (a) can lead to table locking etc. Which should I
worry
> about more?
> For now, these are my questions. I'm not looking for the magic bullet
here,
> I will do some testing myself, but I wanted to poll the minds of the smart
> folk who read these posts to see if anyone had already done this or had
> opinions
> Thanks.
> ice
>|||Ok, thanks for the information.
I thought about the INSERT SELECT option, but I don't think it'll get me
much.
I will do some additional testing.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:uLpaQFAKFHA.3340@.TK2MSFTNGP14.phx.gbl...
> Doing 1000 individual transaction will take longer end-to-end. However,
> each little insert is likely to take not much time by itself. A 1000 row
> insert would take longer than a single-row insert. However, a 1000 row
> insert should be of the order of a second or two. If you want to insert,
> say, a million rows, then you may want to do that in 1000 row batches.
> Using SQL, you would be doing an INSERT SELECT from one table into
another.
> How are these data arriving into your system - DTS, Linked server, flat
> file?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
> .
> "Ice" <ice@.nospam.com> wrote in message
> news:%23MY27B%23JFHA.484@.TK2MSFTNGP15.phx.gbl...
> Thanks for your quick reply.
> I know that "Bulk Inserts" will get this done fast. But I'm looking for a
> generic solution that would work for different databases without having to
> use their specific implementations to solve the problem. I think using
the
> "staging" idea in this scenario is probably adding more work than might be
> necessary. When you say "via SQL" what are you suggesting I use?
> With regards to concurrency and downtime, I want to operate under
assumption
> that there will be no downtime. So based on that, it would seem that you
> are suggesting that inserting ~1000 records in a single transaction is
> better than ~1000 individual transactions. I am correct with this
> deduction?
> ice
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:#4LF2W9JFHA.1280@.TK2MSFTNGP09.phx.gbl...
why
want
> to
> could
need?
single
down
> say,
scenario
> time.
this
the
> worry
> here,
smart
>|||Interesting. Were all the inserts in one transaction or were you setting
"nocount" per individual insert?
ice
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:#orP1F#JFHA.2764@.tk2msftngp13.phx.gbl...
> To add to Tom's response, I ran a quick test from Query Analyzer and found
> the separate inserts outperformed the UNION ALL method when SET NOCOUNT ON
> was specified.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Ice" <ice@.nospam.com> wrote in message
> news:uR0lcN9JFHA.3500@.TK2MSFTNGP14.phx.gbl...
scenario
this
the
smart
>|||The inserts were done as individual statements/transactions. The single
batch was 1000 insert statements was a script like:
SET NOCOUNT ON
INSERT INTO MyTable VALUES(1)
INSERT INTO MyTable VALUES(2)
--etc.
The UNION ALL script as basically:
SET NOCOUNT ON
INSERT INTO MyTable
SELECT 1
UNION ALL SELECT 2
--etc.
I ran this on my home PC so the write caching IDE controller mitigates the
effect of multiple transactions and log I/O. I'd expect a single
transaction to be a little faster.
Hope this helps.
Dan Guzman
SQL Server MVP
"Ice" <ice@.nospam.com> wrote in message
news:%23QENHJBKFHA.1476@.TK2MSFTNGP09.phx.gbl...
> Interesting. Were all the inserts in one transaction or were you setting
> "nocount" per individual insert?
> ice
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:#orP1F#JFHA.2764@.tk2msftngp13.phx.gbl...
> scenario
> this
> the
> smart
>|||Is "SET NOCOUNT" a connection specific setting? How If i send 10 different
individual statements using ADO.NET and I set "no count" on before send the
1st of the 10, is it on for all 10? Any ideas?
ice
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:u2c#OWBKFHA.2752@.TK2MSFTNGP10.phx.gbl...
> The inserts were done as individual statements/transactions. The single
> batch was 1000 insert statements was a script like:
> SET NOCOUNT ON
> INSERT INTO MyTable VALUES(1)
> INSERT INTO MyTable VALUES(2)
> --etc.
> The UNION ALL script as basically:
> SET NOCOUNT ON
> INSERT INTO MyTable
> SELECT 1
> UNION ALL SELECT 2
> --etc.
> I ran this on my home PC so the write caching IDE controller mitigates the
> effect of multiple transactions and log I/O. I'd expect a single
> transaction to be a little faster.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Ice" <ice@.nospam.com> wrote in message
> news:%23QENHJBKFHA.1476@.TK2MSFTNGP09.phx.gbl...
setting
approaches
that
approaches
I
bullet
had
>|||ignore my last post, just found some documentation on this in the framework
SDK.
ice
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:u2c#OWBKFHA.2752@.TK2MSFTNGP10.phx.gbl...
> The inserts were done as individual statements/transactions. The single
> batch was 1000 insert statements was a script like:
> SET NOCOUNT ON
> INSERT INTO MyTable VALUES(1)
> INSERT INTO MyTable VALUES(2)
> --etc.
> The UNION ALL script as basically:
> SET NOCOUNT ON
> INSERT INTO MyTable
> SELECT 1
> UNION ALL SELECT 2
> --etc.
> I ran this on my home PC so the write caching IDE controller mitigates the
> effect of multiple transactions and log I/O. I'd expect a single
> transaction to be a little faster.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Ice" <ice@.nospam.com> wrote in message
> news:%23QENHJBKFHA.1476@.TK2MSFTNGP09.phx.gbl...
setting
approaches
that
approaches
I
bullet
had
>