Mass Insert

[zeusToronto]

Hi, I am trying to help my IT department by developing a simple vb.net app where I am going through EventLog on XP and Vista Boxes and uploading all EventEntries to a db hosted on MSSQL-Express. The executable sits that sits on a client PC and is compiled as a console app that gets fired up through scheduled task. My app attempts to insert new records for every record found in EventLog on each PC on the network to db at around noon (noon is the time when scheduled task fires up my app.). I have about 50PCs on the network and I use Procedure to insert new records. My question is, how many simultaneous connections MSSQL-EXPRESS can handle and what happens when I attempt to insert into the same table from more than 1 client. What would you recommend if I needed to increase number of workstations that upload to DB. Appretiate your help and advise.

[Mycroft Holmes]

I don't know the format for the log files but would assume either fixed length or hopefully delimited. I would use Bulkcopy (I assume express supports it) if the files are delimited. Read all the log into a datatable in the client, bulkcopy the datatable to the database, run the process. I would expect the ETL to take less than 1 minute so would schedule the systems to fire at i minute intervals (this will be a PITA so have the client app use an identifier to decide the order and have the client wait #n minutes). Another option, have your clients send the log files to a centralised folder. Another app (SSIS maybe) reads all files from the folder and loads then. This allows you to use BCP (even faster and supports format files for fixed length) and there is no possibility of a clash caused by multiple loaders. Oh, SSIS is probably not in SQLExpress and BCP may not be supported.

[David Skelly]

A comparison of the various editions of SQL Server is given here: http://www.microsoft.com/Sqlserver/2005/en/us/compare-features.aspx[^] There is no limit to the number of connections for SQL Express, other than the limits imposed by the restriction to a single CPU and 1GB of RAM. As far as inserting into the same table from more than 1 client goes, SQL Express is no different from any other database. Depending on what your application is doing (insert only or are any reads or updates involved?), the transaction scope you use (do you insert each row in its own transaction, or do you block the whole lot into one big transaction?) and what isolation level you are using (the default - read committed - sounds as if it should be OK for your application if it's insert only), you may or may not see blocking occurring between the clients. What do you do if SQL Express tops out and can't cope with the load? Well, one answer is: put your hand in your pocket and buy a full version of SQL Server. If you don't have the budget for that, you could either look to use one of the free databases that are available (MySQL, Firebird, PostgreSQL, SAPDB, there are loads of them around) or you could think laterally. Don't run all the scheduled jobs at noon, stagger them through the day. Or don't write directly into the main database, write each clients' log into its own staging area then have a single central job which comes round and collates it all together later. Think of it as a chance to get creative and flex your architectural design skills.

[Henry Minute]

You might5 find this[^] article on BatchSize useful.

Henry Minute Do not read medical books! You could die of a misprint. - Mark Twain Girl: (staring) "Why do you need an icy cucumber?" “I want to report a fraud. The government is lying to us all.”

[PIEBALDconsult]

I have Sql Server 2008 Express with Advanced Services[^] installed on this system and it does have BCP. I've never used SSIS so I don't know.