Anyone have experience with SYSFILES?

According to Microsoft, sysfiles.status:

0x80 = File has been written to since last backup.

Unfortunately, this query is not returning what I expect. That is, if I have inserted/updated/deleted any records, I belive that some underlying file must be written to.

So, I tried:

Code Snippet

select * from sysfiles where status & 0x80 <> 0

I updated several fields in my database, doubled and tripled the size etc. I can't get the above query to return any results.

Does anyone have any experience with this particular flag? I was hoping to check the sysfiles and know if a backup was needed or not.

Thanks mucho.

Interesting. I just tried this with 7.0, 2000, and 2005, and that bit is never set. And 6.5 uses sysdevices rather than sysfiles. I wonder if somewhere in the development cycle they decided not to implement that, and it was never taken out of the documentation drafts. Either that or it's a very long standing, little-known bug. ;-)

There might be some ways to take advantage of the differential changed map to determine if a database has changed since the last backup, but the only way I know to read it isn't well suited for inclusion in a batch job.

Code Snippet

DBCC TRACEON(3604)
DBCC PAGE(databasename, 1, 6, 3)

Granted, a database larger than about 4 GB is probably going to have more than one dcm page, and I'm not entirely sure how to determine where subsequent pages are located.

Anyone else finding this painful?

Although I am sure I will grow to love this new product I can honestly say that everyday has been a painful experience. Most of this, once again, is the learning curve to the new product. Moreover, each accomplishment leaves us feeling as if we are doing things the wrong way.

Here is an example... We are populating lookup tables from a staging environment. This is pretty straight forward, insert records that don't already exist in the destination. Unfortunately, the only way we could get this to work properly, outside of just writing T-SQL for everything, was to use a lookup transformation and redirect the error rows into the table. Although we have ran numerous tests against it with no problems it certainly doesn't let you walk away with a very comfortable feeling and certainly isn't a choice I would recommend. Are others having this same struggle and uncomfortableness?

Select aspirin
into Body
from Ailments
where condition like '%SSIS%'

-Krusty

Krusty,

Don't fret. That's a legitimate approach and ndeed is the commonly accepted approach. Fair enough - it doesn't look right that good rows are coming down the Error output but don't worry - its not a problem.

-Jamie

|||I feel your pain; but Jamie is right. This is a pretty common technique. I was not aware of the Slowly Changing Dimension Wizard which does a variation of the same thing with a twist that I haven't figured out yet. The wizard can be found in the data flow task by the same name and might be worth looking at sometime - for now, we do it exactly the way you are doing it. I did not realize I had a Slowly Changing Dimension... so I wrote this data flow on my own. The Wizard does it for you.|||

Note that the SCD uses a LOOKUP under the covers so its basically the same thing with a few bells and whistles.

-Jamie

|||

Thanks for the comments it is somewhat comforting to know that others are using the same approach. I guess after being comfortable in the 2000-DTS world for so long it is tough to walk into an environment that puts you back at the beginning.

-Krusty

PS: Thanks for all the blogging Jamie... between here and there I have managed to make some progress.

|||

Hi,

Do not you find that the Slowly Changing Dimension transform works a little too slow?

Sure it saves you tons of code but I would love it to be faster.

Philippe

|||Hey Krusty - I tend to agree with you, there should be an easier and cleaner way to accomplish your task. The techniques described above do work, but it feels like a hack. There should be a transform or something that allows you to "insert where not exists". This is fairly common scenario in my experience.|||

An Upsert estination adapter would be fantastic. Insert it if its not there, update it otherwise.

-Jamie

|||

There would need to be more metatdata entered and persisted in the package to support that type of functionality (the unique identifiers that determine whether a row is a "new" row or exists) so that SSIS could "know" which operation to perform. Solende's Warehouse Workbench has exactly this functionality in the GUI. I loved it.

Of course also it should examine the actual data in each column of source vs. target if the decision is to update ,as none of the data may actually have changed. In that case you would probably not want to perform an update on the target for no real reason that may also update a timestamp column or some other indicator in many systems that indicates when rows have been updated. Many systems that have such a column also have logic to make sure an update is performed only when data actually is changed.

Ken

|||I think it is a very common scenario. My current SSIS project is

pulling updated/new rows from Oracle and transferring to staging

tables. I played with the Lookup transforms, but balked at writing

basically the same DataFlow stuff many times over for many different

tables.

In the end I just wrote minimal DataFlows for the transfer to staging,

then produced update/insert tsql stored in files and used a ForEach

loop to iterate over them. It means as I implement new table transfers,

I can just drop new script files into a particular folder (location

configurable with variable) and know they will be executed in the

loop. I took the idea from the AdventureWorks DataWarehouse SSIS

example.

- Jerzy|||That is a very interesting idea... I will have to take a look at that option. Thanks.

Any JDeveloper/BC4J/ADF experience here ?

Hi all,
I am evaluating JDeveloper 10g(+ADF) to be used with SQL Server 2000
backend.
I am curious, has anybody here already done such combination
succesfully at production stage ?
Thank you for any info,
Krist
You might want to post this on the JDeveloper discussion forum on OTN
also.
http://forums.oracle.com/forums/thread.jsp?forum=83

Any experience with SQLLITE

Anyone has ever used the new 'SQLLITE' tool? Looks like it reduces the backup time and size by almost 70%. Any feedback?Yup, was tasked with it, and tested the living heck out of it. The maximum database size I tested it with was 400G, - the resulting backup size with no compression was 82G vs. native backup - 381G.

Also wrote a wrapper that would take all possible parameters that SQLLiteSpeed supports and produce filenames for backup devices that are similar to the ones produced by SQLMAINT utility.

What else would you like to know? Oh, they also are coming up with a GUI for it, so that you can restore just like in EM.|||So your 400 GB DB was compressed to 82 GB? That's a good ratio, any improvement with backup and restore time?
Also have you noticed any performance degradation on your box running the SQL Server?|||I didn't retain time statistics, our convern was the space constraints, but I know that it was taking less than native. As for performance, - it is CPU-intensive, on both backup and restore. But if you're using default compression and no encryption, - it may max out 2 out of 4 CPU's for short periods of time.

Any experience with really large, high transaction databases?

Most of my experience is with what I consider small, low transaction rate
databases. For example, the largest table in one DB has fewer than 100,000
rows (other tables have far fewer rows) with anywhere from 1 to 15 people
posting queries and updates at any given time. SQL Server handles this just
fine.
We are beginning to design a database for a new project. We haven't
determined how big the tables will be but at peak usage will have perhaps
100 people logged on at a time. There is pressure to use Oracle because of a
belief that SQL Server will not be up to the demands.
Solid, unbiased comparisons between Oracle and SQL Server are hard to come
by. Does anyone here have experience with large systems that would show what
SQL Server is really capable of?
Much obliged.
I can't give out many proprietary details but we are a SQL Server shop and
we handle databases > 1 TB with over 1000 simultaneous connections.
IMHO, your bottleneck is going to be the architecture and design of the
database and application, and more importantly hardware. You will not be
bound by the vendor choice.
http://www.aspfaq.com/
(Reverse address to reply.)
"Geoff Pennington" <Geoffrey.Pennington@.tma.osd.mil.nospam> wrote in message
news:eJtzZ#dZEHA.3092@.tk2msftngp13.phx.gbl...
> Most of my experience is with what I consider small, low transaction rate
> databases. For example, the largest table in one DB has fewer than 100,000
> rows (other tables have far fewer rows) with anywhere from 1 to 15 people
> posting queries and updates at any given time. SQL Server handles this
just
> fine.
> We are beginning to design a database for a new project. We haven't
> determined how big the tables will be but at peak usage will have perhaps
> 100 people logged on at a time. There is pressure to use Oracle because of
a
> belief that SQL Server will not be up to the demands.
> Solid, unbiased comparisons between Oracle and SQL Server are hard to come
> by. Does anyone here have experience with large systems that would show
what
> SQL Server is really capable of?
> Much obliged.
>
|||100 concurrent users is piddly these days for SQL Server. Three years ago I
was doing over 100 Million Inserts / Updates or Deletes against a db with
over 1 Billion rows on what would be considered an obsolete box these days
with no problem what so ever. Here are some links but these are for VLDB's.
We are talking thousands of trans per second and up with Terabyte db's.
http://www.microsoft.com/sql/techinf...calability.asp
Andrew J. Kelly SQL MVP
"Geoff Pennington" <Geoffrey.Pennington@.tma.osd.mil.nospam> wrote in message
news:eJtzZ%23dZEHA.3092@.tk2msftngp13.phx.gbl...
> Most of my experience is with what I consider small, low transaction rate
> databases. For example, the largest table in one DB has fewer than 100,000
> rows (other tables have far fewer rows) with anywhere from 1 to 15 people
> posting queries and updates at any given time. SQL Server handles this
just
> fine.
> We are beginning to design a database for a new project. We haven't
> determined how big the tables will be but at peak usage will have perhaps
> 100 people logged on at a time. There is pressure to use Oracle because of
a
> belief that SQL Server will not be up to the demands.
> Solid, unbiased comparisons between Oracle and SQL Server are hard to come
> by. Does anyone here have experience with large systems that would show
what
> SQL Server is really capable of?
> Much obliged.
>
|||Geoff,
The best example I can think of is Monster.com. They use SQL Server for
their entire jobs database, and get a lot more traffic than 100 people
logged on at a time!
One of the reasons, IMO, Oracle is often considered to be "more scalable"
than SQL Server is that companies tend to buy cheaper servers to run SQL
Server than to run Oracle. For instance, a recent employer of mine based
their entire product on SQL Server and our servers were dual Xeons with 2 gb
of RAM. The servers ran without too many problems, but management would
never spend a cent to upgrade them when we did have issues.
A consultant was hired for a totally new project and convinced management to
take it forward with Oracle. A pair of servers was purchased (QA and
Production), each with 8 Itanium processors and 64 gb of RAM. This, for an
unproven product that was making no money.
Why? Because for some reason, due to the way SQL Server is marketed vs. the
way Oracle is marketed, it's assumed that Oracle "should" sit on a huge
server whereas SQL Server will do better on a glorified desktop box. I'm
not sure how to best convince people of the truth... Good luck!
"Geoff Pennington" <Geoffrey.Pennington@.tma.osd.mil.nospam> wrote in message
news:eJtzZ%23dZEHA.3092@.tk2msftngp13.phx.gbl...
> Most of my experience is with what I consider small, low transaction rate
> databases. For example, the largest table in one DB has fewer than 100,000
> rows (other tables have far fewer rows) with anywhere from 1 to 15 people
> posting queries and updates at any given time. SQL Server handles this
just
> fine.
> We are beginning to design a database for a new project. We haven't
> determined how big the tables will be but at peak usage will have perhaps
> 100 people logged on at a time. There is pressure to use Oracle because of
a
> belief that SQL Server will not be up to the demands.
> Solid, unbiased comparisons between Oracle and SQL Server are hard to come
> by. Does anyone here have experience with large systems that would show
what
> SQL Server is really capable of?
> Much obliged.
>
|||I agree with Aaron here, the architecture and design are key. We have over
1TB of data here and tables with over 1 billion records (horizontally
partitioned.) SQL Server can handle the workload if the project is done
correctly. Make sure to note if you don't have the enterprise version of
SQL you can't use more than 2 gigs of memory.
-John Oakes
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:ui$E6CeZEHA.3304@.TK2MSFTNGP09.phx.gbl...
> I can't give out many proprietary details but we are a SQL Server shop and
> we handle databases > 1 TB with over 1000 simultaneous connections.
> IMHO, your bottleneck is going to be the architecture and design of the
> database and application, and more importantly hardware. You will not be
> bound by the vendor choice.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Geoff Pennington" <Geoffrey.Pennington@.tma.osd.mil.nospam> wrote in
message[vbcol=seagreen]
> news:eJtzZ#dZEHA.3092@.tk2msftngp13.phx.gbl...
rate[vbcol=seagreen]
100,000[vbcol=seagreen]
people[vbcol=seagreen]
> just
perhaps[vbcol=seagreen]
of[vbcol=seagreen]
> a
come
> what
>
|||Thanks Adam - Monster.com is a good example. Could you tell me how you know
they use SQL Server?
I wonder if the reason everyone thinks of SQL Server as being lower end is
two-fold: (1) it is cheaper to get a SQL license, and (2) Oracle has always
been associated with large servers, while MS is associated with the desktop.
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:eQZjlOeZEHA.2776@.TK2MSFTNGP10.phx.gbl...
> Geoff,
> The best example I can think of is Monster.com. They use SQL Server for
> their entire jobs database, and get a lot more traffic than 100 people
> logged on at a time!
> One of the reasons, IMO, Oracle is often considered to be "more scalable"
> than SQL Server is that companies tend to buy cheaper servers to run SQL
> Server than to run Oracle. For instance, a recent employer of mine based
> their entire product on SQL Server and our servers were dual Xeons with 2
gb
> of RAM. The servers ran without too many problems, but management would
> never spend a cent to upgrade them when we did have issues.
> A consultant was hired for a totally new project and convinced management
to
> take it forward with Oracle. A pair of servers was purchased (QA and
> Production), each with 8 Itanium processors and 64 gb of RAM. This, for
an
> unproven product that was making no money.
> Why? Because for some reason, due to the way SQL Server is marketed vs.
the
> way Oracle is marketed, it's assumed that Oracle "should" sit on a huge
> server whereas SQL Server will do better on a glorified desktop box. I'm
> not sure how to best convince people of the truth... Good luck!
>
> "Geoff Pennington" <Geoffrey.Pennington@.tma.osd.mil.nospam> wrote in
message[vbcol=seagreen]
> news:eJtzZ%23dZEHA.3092@.tk2msftngp13.phx.gbl...
rate[vbcol=seagreen]
100,000[vbcol=seagreen]
people[vbcol=seagreen]
> just
perhaps[vbcol=seagreen]
of[vbcol=seagreen]
> a
come
> what
>
|||"Geoff Pennington" <Geoffrey.Pennington@.tma.osd.mil.nospam> wrote in message
news:%23RVJG9eZEHA.912@.TK2MSFTNGP10.phx.gbl...
> Thanks Adam - Monster.com is a good example. Could you tell me how you
know
> they use SQL Server?
I interviewed there last year for a data architect position.
|||There are many larger sites with lots of users that use SQL Server.
Dell.com, CareerBuilder.com, Barnes & Nobles etc...
Andrew J. Kelly SQL MVP
"Geoff Pennington" <Geoffrey.Pennington@.tma.osd.mil.nospam> wrote in message
news:%23RVJG9eZEHA.912@.TK2MSFTNGP10.phx.gbl...
> Thanks Adam - Monster.com is a good example. Could you tell me how you
know
> they use SQL Server?
> I wonder if the reason everyone thinks of SQL Server as being lower end is
> two-fold: (1) it is cheaper to get a SQL license, and (2) Oracle has
always
> been associated with large servers, while MS is associated with the
desktop.[vbcol=seagreen]
>
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:eQZjlOeZEHA.2776@.TK2MSFTNGP10.phx.gbl...
scalable"[vbcol=seagreen]
based[vbcol=seagreen]
2[vbcol=seagreen]
> gb
management[vbcol=seagreen]
> to
> an
> the
I'm[vbcol=seagreen]
> message
> rate
> 100,000
> people
> perhaps
because[vbcol=seagreen]
> of
> come
show
>
|||SQL Server is not a punk anymore.
many of the ORACLE Folks still think of SQL Server as SQL Server 6.5.
if you want a Non-Biased opinion check out the TPC site.
www.tpc.org
The results speak for themselves (SQL Server can DEFINATELY Hold it's own AS
Can ORACLE and DB2).
Greg Jackson
PDX, Oregon
|||We HAVE had performance issues. HOWEVER every one of them were
"Architectural" in nature.
Cheers
Greg Jackson
PDX, Oregon

Any experience with really large, high transaction databases?

Most of my experience is with what I consider small, low transaction rate
databases. For example, the largest table in one DB has fewer than 100,000
rows (other tables have far fewer rows) with anywhere from 1 to 15 people
posting queries and updates at any given time. SQL Server handles this just
fine.
We are beginning to design a database for a new project. We haven't
determined how big the tables will be but at peak usage will have perhaps
100 people logged on at a time. There is pressure to use Oracle because of a
belief that SQL Server will not be up to the demands.
Solid, unbiased comparisons between Oracle and SQL Server are hard to come
by. Does anyone here have experience with large systems that would show what
SQL Server is really capable of?
Much obliged.I can't give out many proprietary details but we are a SQL Server shop and
we handle databases > 1 TB with over 1000 simultaneous connections.
IMHO, your bottleneck is going to be the architecture and design of the
database and application, and more importantly hardware. You will not be
bound by the vendor choice.
http://www.aspfaq.com/
(Reverse address to reply.)
"Geoff Pennington" <Geoffrey.Pennington@.tma.osd.mil.nospam> wrote in message
news:eJtzZ#dZEHA.3092@.tk2msftngp13.phx.gbl...
> Most of my experience is with what I consider small, low transaction rate
> databases. For example, the largest table in one DB has fewer than 100,000
> rows (other tables have far fewer rows) with anywhere from 1 to 15 people
> posting queries and updates at any given time. SQL Server handles this
just
> fine.
> We are beginning to design a database for a new project. We haven't
> determined how big the tables will be but at peak usage will have perhaps
> 100 people logged on at a time. There is pressure to use Oracle because of
a
> belief that SQL Server will not be up to the demands.
> Solid, unbiased comparisons between Oracle and SQL Server are hard to come
> by. Does anyone here have experience with large systems that would show
what
> SQL Server is really capable of?
> Much obliged.
>|||100 concurrent users is piddly these days for SQL Server. Three years ago I
was doing over 100 Million Inserts / Updates or Deletes against a db with
over 1 Billion rows on what would be considered an obsolete box these days
with no problem what so ever. Here are some links but these are for VLDB's.
We are talking thousands of trans per second and up with Terabyte db's.
http://www.microsoft.com/sql/techin...scalability.asp
Andrew J. Kelly SQL MVP
"Geoff Pennington" <Geoffrey.Pennington@.tma.osd.mil.nospam> wrote in message
news:eJtzZ%23dZEHA.3092@.tk2msftngp13.phx.gbl...
> Most of my experience is with what I consider small, low transaction rate
> databases. For example, the largest table in one DB has fewer than 100,000
> rows (other tables have far fewer rows) with anywhere from 1 to 15 people
> posting queries and updates at any given time. SQL Server handles this
just
> fine.
> We are beginning to design a database for a new project. We haven't
> determined how big the tables will be but at peak usage will have perhaps
> 100 people logged on at a time. There is pressure to use Oracle because of
a
> belief that SQL Server will not be up to the demands.
> Solid, unbiased comparisons between Oracle and SQL Server are hard to come
> by. Does anyone here have experience with large systems that would show
what
> SQL Server is really capable of?
> Much obliged.
>|||Geoff,
The best example I can think of is Monster.com. They use SQL Server for
their entire jobs database, and get a lot more traffic than 100 people
logged on at a time!
One of the reasons, IMO, Oracle is often considered to be "more scalable"
than SQL Server is that companies tend to buy cheaper servers to run SQL
Server than to run Oracle. For instance, a recent employer of mine based
their entire product on SQL Server and our servers were dual Xeons with 2 gb
of RAM. The servers ran without too many problems, but management would
never spend a cent to upgrade them when we did have issues.
A consultant was hired for a totally new project and convinced management to
take it forward with Oracle. A pair of servers was purchased (QA and
Production), each with 8 Itanium processors and 64 gb of RAM. This, for an
unproven product that was making no money.
Why? Because for some reason, due to the way SQL Server is marketed vs. the
way Oracle is marketed, it's assumed that Oracle "should" sit on a huge
server whereas SQL Server will do better on a glorified desktop box. I'm
not sure how to best convince people of the truth... Good luck!
"Geoff Pennington" <Geoffrey.Pennington@.tma.osd.mil.nospam> wrote in message
news:eJtzZ%23dZEHA.3092@.tk2msftngp13.phx.gbl...
> Most of my experience is with what I consider small, low transaction rate
> databases. For example, the largest table in one DB has fewer than 100,000
> rows (other tables have far fewer rows) with anywhere from 1 to 15 people
> posting queries and updates at any given time. SQL Server handles this
just
> fine.
> We are beginning to design a database for a new project. We haven't
> determined how big the tables will be but at peak usage will have perhaps
> 100 people logged on at a time. There is pressure to use Oracle because of
a
> belief that SQL Server will not be up to the demands.
> Solid, unbiased comparisons between Oracle and SQL Server are hard to come
> by. Does anyone here have experience with large systems that would show
what
> SQL Server is really capable of?
> Much obliged.
>|||I agree with Aaron here, the architecture and design are key. We have over
1TB of data here and tables with over 1 billion records (horizontally
partitioned.) SQL Server can handle the workload if the project is done
correctly. Make sure to note if you don't have the enterprise version of
SQL you can't use more than 2 gigs of memory.
-John Oakes
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:ui$E6CeZEHA.3304@.TK2MSFTNGP09.phx.gbl...
> I can't give out many proprietary details but we are a SQL Server shop and
> we handle databases > 1 TB with over 1000 simultaneous connections.
> IMHO, your bottleneck is going to be the architecture and design of the
> database and application, and more importantly hardware. You will not be
> bound by the vendor choice.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Geoff Pennington" <Geoffrey.Pennington@.tma.osd.mil.nospam> wrote in
message
> news:eJtzZ#dZEHA.3092@.tk2msftngp13.phx.gbl...
rate[vbcol=seagreen]
100,000[vbcol=seagreen]
people[vbcol=seagreen]
> just
perhaps[vbcol=seagreen]
of[vbcol=seagreen]
> a
come[vbcol=seagreen]
> what
>|||Thanks Adam - Monster.com is a good example. Could you tell me how you know
they use SQL Server?
I wonder if the reason everyone thinks of SQL Server as being lower end is
two-fold: (1) it is cheaper to get a SQL license, and (2) Oracle has always
been associated with large servers, while MS is associated with the desktop.
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:eQZjlOeZEHA.2776@.TK2MSFTNGP10.phx.gbl...
> Geoff,
> The best example I can think of is Monster.com. They use SQL Server for
> their entire jobs database, and get a lot more traffic than 100 people
> logged on at a time!
> One of the reasons, IMO, Oracle is often considered to be "more scalable"
> than SQL Server is that companies tend to buy cheaper servers to run SQL
> Server than to run Oracle. For instance, a recent employer of mine based
> their entire product on SQL Server and our servers were dual Xeons with 2
gb
> of RAM. The servers ran without too many problems, but management would
> never spend a cent to upgrade them when we did have issues.
> A consultant was hired for a totally new project and convinced management
to
> take it forward with Oracle. A pair of servers was purchased (QA and
> Production), each with 8 Itanium processors and 64 gb of RAM. This, for
an
> unproven product that was making no money.
> Why? Because for some reason, due to the way SQL Server is marketed vs.
the
> way Oracle is marketed, it's assumed that Oracle "should" sit on a huge
> server whereas SQL Server will do better on a glorified desktop box. I'm
> not sure how to best convince people of the truth... Good luck!
>
> "Geoff Pennington" <Geoffrey.Pennington@.tma.osd.mil.nospam> wrote in
message
> news:eJtzZ%23dZEHA.3092@.tk2msftngp13.phx.gbl...
rate[vbcol=seagreen]
100,000[vbcol=seagreen]
people[vbcol=seagreen]
> just
perhaps[vbcol=seagreen]
of[vbcol=seagreen]
> a
come[vbcol=seagreen]
> what
>|||"Geoff Pennington" <Geoffrey.Pennington@.tma.osd.mil.nospam> wrote in message
news:%23RVJG9eZEHA.912@.TK2MSFTNGP10.phx.gbl...
> Thanks Adam - Monster.com is a good example. Could you tell me how you
know
> they use SQL Server?
I interviewed there last year for a data architect position.|||There are many larger sites with lots of users that use SQL Server.
Dell.com, CareerBuilder.com, Barnes & Nobles etc...
Andrew J. Kelly SQL MVP
"Geoff Pennington" <Geoffrey.Pennington@.tma.osd.mil.nospam> wrote in message
news:%23RVJG9eZEHA.912@.TK2MSFTNGP10.phx.gbl...
> Thanks Adam - Monster.com is a good example. Could you tell me how you
know
> they use SQL Server?
> I wonder if the reason everyone thinks of SQL Server as being lower end is
> two-fold: (1) it is cheaper to get a SQL license, and (2) Oracle has
always
> been associated with large servers, while MS is associated with the
desktop.
>
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:eQZjlOeZEHA.2776@.TK2MSFTNGP10.phx.gbl...
scalable"[vbcol=seagreen]
based[vbcol=seagreen]
2[vbcol=seagreen]
> gb
management[vbcol=seagreen]
> to
> an
> the
I'm[vbcol=seagreen]
> message
> rate
> 100,000
> people
> perhaps
because[vbcol=seagreen]
> of
> come
show[vbcol=seagreen]
>|||SQL Server is not a punk anymore.
many of the ORACLE Folks still think of SQL Server as SQL Server 6.5.
if you want a Non-Biased opinion check out the TPC site.
www.tpc.org
The results speak for themselves (SQL Server can DEFINATELY Hold it's own AS
Can ORACLE and DB2).
Greg Jackson
PDX, Oregon|||We HAVE had performance issues. HOWEVER every one of them were
"Architectural" in nature.
Cheers
Greg Jackson
PDX, Oregon

Any experience with really large, high transaction databases?

Most of my experience is with what I consider small, low transaction rate
databases. For example, the largest table in one DB has fewer than 100,000
rows (other tables have far fewer rows) with anywhere from 1 to 15 people
posting queries and updates at any given time. SQL Server handles this just
fine.
We are beginning to design a database for a new project. We haven't
determined how big the tables will be but at peak usage will have perhaps
100 people logged on at a time. There is pressure to use Oracle because of a
belief that SQL Server will not be up to the demands.
Solid, unbiased comparisons between Oracle and SQL Server are hard to come
by. Does anyone here have experience with large systems that would show what
SQL Server is really capable of?
Much obliged.I can't give out many proprietary details but we are a SQL Server shop and
we handle databases > 1 TB with over 1000 simultaneous connections.
IMHO, your bottleneck is going to be the architecture and design of the
database and application, and more importantly hardware. You will not be
bound by the vendor choice.
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Geoff Pennington" <Geoffrey.Pennington@.tma.osd.mil.nospam> wrote in message
news:eJtzZ#dZEHA.3092@.tk2msftngp13.phx.gbl...
> Most of my experience is with what I consider small, low transaction rate
> databases. For example, the largest table in one DB has fewer than 100,000
> rows (other tables have far fewer rows) with anywhere from 1 to 15 people
> posting queries and updates at any given time. SQL Server handles this
just
> fine.
> We are beginning to design a database for a new project. We haven't
> determined how big the tables will be but at peak usage will have perhaps
> 100 people logged on at a time. There is pressure to use Oracle because of
a
> belief that SQL Server will not be up to the demands.
> Solid, unbiased comparisons between Oracle and SQL Server are hard to come
> by. Does anyone here have experience with large systems that would show
what
> SQL Server is really capable of?
> Much obliged.
>|||100 concurrent users is piddly these days for SQL Server. Three years ago I
was doing over 100 Million Inserts / Updates or Deletes against a db with
over 1 Billion rows on what would be considered an obsolete box these days
with no problem what so ever. Here are some links but these are for VLDB's.
We are talking thousands of trans per second and up with Terabyte db's.
http://www.microsoft.com/sql/techinfo/administration/2000/scalability.asp
--
Andrew J. Kelly SQL MVP
"Geoff Pennington" <Geoffrey.Pennington@.tma.osd.mil.nospam> wrote in message
news:eJtzZ%23dZEHA.3092@.tk2msftngp13.phx.gbl...
> Most of my experience is with what I consider small, low transaction rate
> databases. For example, the largest table in one DB has fewer than 100,000
> rows (other tables have far fewer rows) with anywhere from 1 to 15 people
> posting queries and updates at any given time. SQL Server handles this
just
> fine.
> We are beginning to design a database for a new project. We haven't
> determined how big the tables will be but at peak usage will have perhaps
> 100 people logged on at a time. There is pressure to use Oracle because of
a
> belief that SQL Server will not be up to the demands.
> Solid, unbiased comparisons between Oracle and SQL Server are hard to come
> by. Does anyone here have experience with large systems that would show
what
> SQL Server is really capable of?
> Much obliged.
>|||Geoff,
The best example I can think of is Monster.com. They use SQL Server for
their entire jobs database, and get a lot more traffic than 100 people
logged on at a time!
One of the reasons, IMO, Oracle is often considered to be "more scalable"
than SQL Server is that companies tend to buy cheaper servers to run SQL
Server than to run Oracle. For instance, a recent employer of mine based
their entire product on SQL Server and our servers were dual Xeons with 2 gb
of RAM. The servers ran without too many problems, but management would
never spend a cent to upgrade them when we did have issues.
A consultant was hired for a totally new project and convinced management to
take it forward with Oracle. A pair of servers was purchased (QA and
Production), each with 8 Itanium processors and 64 gb of RAM. This, for an
unproven product that was making no money.
Why? Because for some reason, due to the way SQL Server is marketed vs. the
way Oracle is marketed, it's assumed that Oracle "should" sit on a huge
server whereas SQL Server will do better on a glorified desktop box. I'm
not sure how to best convince people of the truth... Good luck!
"Geoff Pennington" <Geoffrey.Pennington@.tma.osd.mil.nospam> wrote in message
news:eJtzZ%23dZEHA.3092@.tk2msftngp13.phx.gbl...
> Most of my experience is with what I consider small, low transaction rate
> databases. For example, the largest table in one DB has fewer than 100,000
> rows (other tables have far fewer rows) with anywhere from 1 to 15 people
> posting queries and updates at any given time. SQL Server handles this
just
> fine.
> We are beginning to design a database for a new project. We haven't
> determined how big the tables will be but at peak usage will have perhaps
> 100 people logged on at a time. There is pressure to use Oracle because of
a
> belief that SQL Server will not be up to the demands.
> Solid, unbiased comparisons between Oracle and SQL Server are hard to come
> by. Does anyone here have experience with large systems that would show
what
> SQL Server is really capable of?
> Much obliged.
>|||I agree with Aaron here, the architecture and design are key. We have over
1TB of data here and tables with over 1 billion records (horizontally
partitioned.) SQL Server can handle the workload if the project is done
correctly. Make sure to note if you don't have the enterprise version of
SQL you can't use more than 2 gigs of memory.
-John Oakes
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:ui$E6CeZEHA.3304@.TK2MSFTNGP09.phx.gbl...
> I can't give out many proprietary details but we are a SQL Server shop and
> we handle databases > 1 TB with over 1000 simultaneous connections.
> IMHO, your bottleneck is going to be the architecture and design of the
> database and application, and more importantly hardware. You will not be
> bound by the vendor choice.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Geoff Pennington" <Geoffrey.Pennington@.tma.osd.mil.nospam> wrote in
message
> news:eJtzZ#dZEHA.3092@.tk2msftngp13.phx.gbl...
> > Most of my experience is with what I consider small, low transaction
rate
> > databases. For example, the largest table in one DB has fewer than
100,000
> > rows (other tables have far fewer rows) with anywhere from 1 to 15
people
> > posting queries and updates at any given time. SQL Server handles this
> just
> > fine.
> >
> > We are beginning to design a database for a new project. We haven't
> > determined how big the tables will be but at peak usage will have
perhaps
> > 100 people logged on at a time. There is pressure to use Oracle because
of
> a
> > belief that SQL Server will not be up to the demands.
> >
> > Solid, unbiased comparisons between Oracle and SQL Server are hard to
come
> > by. Does anyone here have experience with large systems that would show
> what
> > SQL Server is really capable of?
> >
> > Much obliged.
> >
> >
>|||Thanks Adam - Monster.com is a good example. Could you tell me how you know
they use SQL Server?
I wonder if the reason everyone thinks of SQL Server as being lower end is
two-fold: (1) it is cheaper to get a SQL license, and (2) Oracle has always
been associated with large servers, while MS is associated with the desktop.
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:eQZjlOeZEHA.2776@.TK2MSFTNGP10.phx.gbl...
> Geoff,
> The best example I can think of is Monster.com. They use SQL Server for
> their entire jobs database, and get a lot more traffic than 100 people
> logged on at a time!
> One of the reasons, IMO, Oracle is often considered to be "more scalable"
> than SQL Server is that companies tend to buy cheaper servers to run SQL
> Server than to run Oracle. For instance, a recent employer of mine based
> their entire product on SQL Server and our servers were dual Xeons with 2
gb
> of RAM. The servers ran without too many problems, but management would
> never spend a cent to upgrade them when we did have issues.
> A consultant was hired for a totally new project and convinced management
to
> take it forward with Oracle. A pair of servers was purchased (QA and
> Production), each with 8 Itanium processors and 64 gb of RAM. This, for
an
> unproven product that was making no money.
> Why? Because for some reason, due to the way SQL Server is marketed vs.
the
> way Oracle is marketed, it's assumed that Oracle "should" sit on a huge
> server whereas SQL Server will do better on a glorified desktop box. I'm
> not sure how to best convince people of the truth... Good luck!
>
> "Geoff Pennington" <Geoffrey.Pennington@.tma.osd.mil.nospam> wrote in
message
> news:eJtzZ%23dZEHA.3092@.tk2msftngp13.phx.gbl...
> > Most of my experience is with what I consider small, low transaction
rate
> > databases. For example, the largest table in one DB has fewer than
100,000
> > rows (other tables have far fewer rows) with anywhere from 1 to 15
people
> > posting queries and updates at any given time. SQL Server handles this
> just
> > fine.
> >
> > We are beginning to design a database for a new project. We haven't
> > determined how big the tables will be but at peak usage will have
perhaps
> > 100 people logged on at a time. There is pressure to use Oracle because
of
> a
> > belief that SQL Server will not be up to the demands.
> >
> > Solid, unbiased comparisons between Oracle and SQL Server are hard to
come
> > by. Does anyone here have experience with large systems that would show
> what
> > SQL Server is really capable of?
> >
> > Much obliged.
> >
> >
>|||"Geoff Pennington" <Geoffrey.Pennington@.tma.osd.mil.nospam> wrote in message
news:%23RVJG9eZEHA.912@.TK2MSFTNGP10.phx.gbl...
> Thanks Adam - Monster.com is a good example. Could you tell me how you
know
> they use SQL Server?
I interviewed there last year for a data architect position.|||There are many larger sites with lots of users that use SQL Server.
Dell.com, CareerBuilder.com, Barnes & Nobles etc...
Andrew J. Kelly SQL MVP
"Geoff Pennington" <Geoffrey.Pennington@.tma.osd.mil.nospam> wrote in message
news:%23RVJG9eZEHA.912@.TK2MSFTNGP10.phx.gbl...
> Thanks Adam - Monster.com is a good example. Could you tell me how you
know
> they use SQL Server?
> I wonder if the reason everyone thinks of SQL Server as being lower end is
> two-fold: (1) it is cheaper to get a SQL license, and (2) Oracle has
always
> been associated with large servers, while MS is associated with the
desktop.
>
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:eQZjlOeZEHA.2776@.TK2MSFTNGP10.phx.gbl...
> > Geoff,
> >
> > The best example I can think of is Monster.com. They use SQL Server for
> > their entire jobs database, and get a lot more traffic than 100 people
> > logged on at a time!
> >
> > One of the reasons, IMO, Oracle is often considered to be "more
scalable"
> > than SQL Server is that companies tend to buy cheaper servers to run SQL
> > Server than to run Oracle. For instance, a recent employer of mine
based
> > their entire product on SQL Server and our servers were dual Xeons with
2
> gb
> > of RAM. The servers ran without too many problems, but management would
> > never spend a cent to upgrade them when we did have issues.
> >
> > A consultant was hired for a totally new project and convinced
management
> to
> > take it forward with Oracle. A pair of servers was purchased (QA and
> > Production), each with 8 Itanium processors and 64 gb of RAM. This, for
> an
> > unproven product that was making no money.
> >
> > Why? Because for some reason, due to the way SQL Server is marketed vs.
> the
> > way Oracle is marketed, it's assumed that Oracle "should" sit on a huge
> > server whereas SQL Server will do better on a glorified desktop box.
I'm
> > not sure how to best convince people of the truth... Good luck!
> >
> >
> > "Geoff Pennington" <Geoffrey.Pennington@.tma.osd.mil.nospam> wrote in
> message
> > news:eJtzZ%23dZEHA.3092@.tk2msftngp13.phx.gbl...
> > > Most of my experience is with what I consider small, low transaction
> rate
> > > databases. For example, the largest table in one DB has fewer than
> 100,000
> > > rows (other tables have far fewer rows) with anywhere from 1 to 15
> people
> > > posting queries and updates at any given time. SQL Server handles this
> > just
> > > fine.
> > >
> > > We are beginning to design a database for a new project. We haven't
> > > determined how big the tables will be but at peak usage will have
> perhaps
> > > 100 people logged on at a time. There is pressure to use Oracle
because
> of
> > a
> > > belief that SQL Server will not be up to the demands.
> > >
> > > Solid, unbiased comparisons between Oracle and SQL Server are hard to
> come
> > > by. Does anyone here have experience with large systems that would
show
> > what
> > > SQL Server is really capable of?
> > >
> > > Much obliged.
> > >
> > >
> >
> >
>|||SQL Server is not a punk anymore.
many of the ORACLE Folks still think of SQL Server as SQL Server 6.5.
if you want a Non-Biased opinion check out the TPC site.
www.tpc.org
The results speak for themselves (SQL Server can DEFINATELY Hold it's own AS
Can ORACLE and DB2).
Greg Jackson
PDX, Oregon|||We HAVE had performance issues. HOWEVER every one of them were
"Architectural" in nature.
Cheers
Greg Jackson
PDX, Oregon|||Thanks in particular for the TPC link, the references to companies that are
using SQL Server for large apps, and to everyone for pointing out the
importance of design. I was also able to find some case-study "success
stories" of companies using SQL Server. Now we'll see how the argument
proceeeds on my project...
"Geoff Pennington" <Geoffrey.Pennington@.tma.osd.mil.nospam> wrote in message
news:eJtzZ%23dZEHA.3092@.tk2msftngp13.phx.gbl...
> Most of my experience is with what I consider small, low transaction rate
> databases. For example, the largest table in one DB has fewer than 100,000
> rows (other tables have far fewer rows) with anywhere from 1 to 15 people
> posting queries and updates at any given time. SQL Server handles this
just
> fine.
> We are beginning to design a database for a new project. We haven't
> determined how big the tables will be but at peak usage will have perhaps
> 100 people logged on at a time. There is pressure to use Oracle because of
a
> belief that SQL Server will not be up to the demands.
> Solid, unbiased comparisons between Oracle and SQL Server are hard to come
> by. Does anyone here have experience with large systems that would show
what
> SQL Server is really capable of?
> Much obliged.
>