Quick Answer Please.

Sorry, I need to run backups to a shared drive on another server, and
everything I have tried for the last hour will not work..
I have full access to the drive, can copy and paste anything into it, but
when I run this Query
BACKUP DATABASE ArnesPriceBook
TO DISK = 'L:\ArnesPriceBook_db.BAK'
WITH INIT, SKIP
It comes back with "Cannot open backup device 'I:\ArnesPriceBook_db.BAK'.
Device error or device off-line"
The local drives are compressed and there are space issues. How can I back
this up to a mapped or UNC location'
Thank you :-)WANNABE wrote:
> Sorry, I need to run backups to a shared drive on another server, and
> everything I have tried for the last hour will not work..
> I have full access to the drive, can copy and paste anything into it, but
> when I run this Query
> BACKUP DATABASE ArnesPriceBook
> TO DISK = 'L:\ArnesPriceBook_db.BAK'
> WITH INIT, SKIP
> It comes back with "Cannot open backup device 'I:\ArnesPriceBook_db.BAK'.
> Device error or device off-line"
> The local drives are compressed and there are space issues. How can I bac
k
> this up to a mapped or UNC location'
> Thank you :-)
>
*YOU* may have access to that share, but does SQL? Look at the
properties of the SQL Server and SQL Agent services - are they running
as "Local System" or as a domain user? To access network resources,
they must run as a domain user, and that user must have access to the
network resources.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Yes, I am logged in as Administrator, and YES it's a bad thing SQL server
and the agent run off the domain administrator ID.. The Id has full access
to all it needs and more. After I create a device for that UNC location the
script works when backing up to the device across the network, but That will
require many changes to what the plan for tonight was, and I am not sure how
to restore a device back up to a different server. ANY further suggestions
and help would be much appreciated.
========================================
==
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:44EA1E4D.2070305@.realsqlguy.com...
> WANNABE wrote:
> *YOU* may have access to that share, but does SQL? Look at the properties
> of the SQL Server and SQL Agent services - are they running as "Local
> System" or as a domain user? To access network resources, they must run
> as a domain user, and that user must have access to the network resources.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||It looks to me like you are using a mapped drive which you
don't want to use with services. Use a UNC path to a share
that the service account for SQL Server has access to. If
you created a share on the other server named SQLBackups,
you would use something like:
\\ServerName\SQLBackups\
to reference the directory. So the backup would be:
BACKUP DATABASE ArnesPriceBook
TO DISK = '\\ServerName\SQLBackups\ArnesPriceBook_
db.BAK'
WITH INIT, SKIP
-Sue
On Mon, 21 Aug 2006 16:53:15 -0500, "WANNABE" <breichenbach
AT istate DOT com> wrote:

>Yes, I am logged in as Administrator, and YES it's a bad thing SQL server
>and the agent run off the domain administrator ID.. The Id has full access
>to all it needs and more. After I create a device for that UNC location th
e
>script works when backing up to the device across the network, but That wil
l
>require many changes to what the plan for tonight was, and I am not sure ho
w
>to restore a device back up to a different server. ANY further suggestions
>and help would be much appreciated.
> ========================================
==
>"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
>news:44EA1E4D.2070305@.realsqlguy.com...
>|||WANNABE wrote:
> Yes, I am logged in as Administrator, and YES it's a bad thing SQL server
> and the agent run off the domain administrator ID.. The Id has full acces
s
> to all it needs and more. After I create a device for that UNC location t
he
> script works when backing up to the device across the network, but That wi
ll
> require many changes to what the plan for tonight was, and I am not sure h
ow
> to restore a device back up to a different server. ANY further suggestion
s
> and help would be much appreciated.
I just noticed you're using mapped drives... Try backup up to the UNC
path name instead, i.e. \\servername\sharename\ArnesPriceBook.BAK
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I know I had tried that, but I just tried it again, and using the UNC
worked.. Thanks, I must have fat fingered something the first time
..whatever... Thank you very much!!!
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:96bke2d6p571jnmidup6bld6li16thvm48@.
4ax.com...
> It looks to me like you are using a mapped drive which you
> don't want to use with services. Use a UNC path to a share
> that the service account for SQL Server has access to. If
> you created a share on the other server named SQLBackups,
> you would use something like:
> \\ServerName\SQLBackups\
> to reference the directory. So the backup would be:
> BACKUP DATABASE ArnesPriceBook
> TO DISK = '\\ServerName\SQLBackups\ArnesPriceBook_
db.BAK'
> WITH INIT, SKIP
> -Sue
> On Mon, 21 Aug 2006 16:53:15 -0500, "WANNABE" <breichenbach
> AT istate DOT com> wrote:
>
>|||It's more robust to use UNC than use a mapped drive letter. But if you must,
you can run EXEC master..xp_cmdshell 'net use L: \\theServer\theShare' to ma
p
the drive before running the backup step in the same T-SQL script.
Linchi
"WANNABE" wrote:

> Yes, I am logged in as Administrator, and YES it's a bad thing SQL server
> and the agent run off the domain administrator ID.. The Id has full acces
s
> to all it needs and more. After I create a device for that UNC location t
he
> script works when backing up to the device across the network, but That wi
ll
> require many changes to what the plan for tonight was, and I am not sure h
ow
> to restore a device back up to a different server. ANY further suggestion
s
> and help would be much appreciated.
> ========================================
==
> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
> news:44EA1E4D.2070305@.realsqlguy.com...
>
>

Quick Answer Please.

Sorry, I need to run backups to a shared drive on another server, and
everything I have tried for the last hour will not work..
I have full access to the drive, can copy and paste anything into it, but
when I run this Query
BACKUP DATABASE ArnesPriceBook
TO DISK = 'L:\ArnesPriceBook_db.BAK'
WITH INIT, SKIP
It comes back with "Cannot open backup device 'I:\ArnesPriceBook_db.BAK'.
Device error or device off-line"
The local drives are compressed and there are space issues. How can I back
this up to a mapped or UNC location'
Thank you :-)WANNABE wrote:
> Sorry, I need to run backups to a shared drive on another server, and
> everything I have tried for the last hour will not work..
> I have full access to the drive, can copy and paste anything into it, but
> when I run this Query
> BACKUP DATABASE ArnesPriceBook
> TO DISK = 'L:\ArnesPriceBook_db.BAK'
> WITH INIT, SKIP
> It comes back with "Cannot open backup device 'I:\ArnesPriceBook_db.BAK'.
> Device error or device off-line"
> The local drives are compressed and there are space issues. How can I back
> this up to a mapped or UNC location'
> Thank you :-)
>
*YOU* may have access to that share, but does SQL? Look at the
properties of the SQL Server and SQL Agent services - are they running
as "Local System" or as a domain user? To access network resources,
they must run as a domain user, and that user must have access to the
network resources.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Yes, I am logged in as Administrator, and YES it's a bad thing SQL server
and the agent run off the domain administrator ID.. The Id has full access
to all it needs and more. After I create a device for that UNC location the
script works when backing up to the device across the network, but That will
require many changes to what the plan for tonight was, and I am not sure how
to restore a device back up to a different server. ANY further suggestions
and help would be much appreciated.
=========================================="Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:44EA1E4D.2070305@.realsqlguy.com...
> WANNABE wrote:
>> Sorry, I need to run backups to a shared drive on another server, and
>> everything I have tried for the last hour will not work..
>> I have full access to the drive, can copy and paste anything into it, but
>> when I run this Query
>> BACKUP DATABASE ArnesPriceBook
>> TO DISK = 'L:\ArnesPriceBook_db.BAK'
>> WITH INIT, SKIP
>> It comes back with "Cannot open backup device 'I:\ArnesPriceBook_db.BAK'.
>> Device error or device off-line"
>> The local drives are compressed and there are space issues. How can I
>> back this up to a mapped or UNC location'
>> Thank you :-)
> *YOU* may have access to that share, but does SQL? Look at the properties
> of the SQL Server and SQL Agent services - are they running as "Local
> System" or as a domain user? To access network resources, they must run
> as a domain user, and that user must have access to the network resources.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||It looks to me like you are using a mapped drive which you
don't want to use with services. Use a UNC path to a share
that the service account for SQL Server has access to. If
you created a share on the other server named SQLBackups,
you would use something like:
\\ServerName\SQLBackups\
to reference the directory. So the backup would be:
BACKUP DATABASE ArnesPriceBook
TO DISK = '\\ServerName\SQLBackups\ArnesPriceBook_db.BAK'
WITH INIT, SKIP
-Sue
On Mon, 21 Aug 2006 16:53:15 -0500, "WANNABE" <breichenbach
AT istate DOT com> wrote:
>Yes, I am logged in as Administrator, and YES it's a bad thing SQL server
>and the agent run off the domain administrator ID.. The Id has full access
>to all it needs and more. After I create a device for that UNC location the
>script works when backing up to the device across the network, but That will
>require many changes to what the plan for tonight was, and I am not sure how
>to restore a device back up to a different server. ANY further suggestions
>and help would be much appreciated.
>==========================================>"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
>news:44EA1E4D.2070305@.realsqlguy.com...
>> WANNABE wrote:
>> Sorry, I need to run backups to a shared drive on another server, and
>> everything I have tried for the last hour will not work..
>> I have full access to the drive, can copy and paste anything into it, but
>> when I run this Query
>> BACKUP DATABASE ArnesPriceBook
>> TO DISK = 'L:\ArnesPriceBook_db.BAK'
>> WITH INIT, SKIP
>> It comes back with "Cannot open backup device 'I:\ArnesPriceBook_db.BAK'.
>> Device error or device off-line"
>> The local drives are compressed and there are space issues. How can I
>> back this up to a mapped or UNC location'
>> Thank you :-)
>> *YOU* may have access to that share, but does SQL? Look at the properties
>> of the SQL Server and SQL Agent services - are they running as "Local
>> System" or as a domain user? To access network resources, they must run
>> as a domain user, and that user must have access to the network resources.
>>
>> --
>> Tracy McKibben
>> MCDBA
>> http://www.realsqlguy.com
>|||WANNABE wrote:
> Yes, I am logged in as Administrator, and YES it's a bad thing SQL server
> and the agent run off the domain administrator ID.. The Id has full access
> to all it needs and more. After I create a device for that UNC location the
> script works when backing up to the device across the network, but That will
> require many changes to what the plan for tonight was, and I am not sure how
> to restore a device back up to a different server. ANY further suggestions
> and help would be much appreciated.
I just noticed you're using mapped drives... Try backup up to the UNC
path name instead, i.e. \\servername\sharename\ArnesPriceBook.BAK
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I know I had tried that, but I just tried it again, and using the UNC
worked.. Thanks, I must have fat fingered something the first time
..whatever... Thank you very much!!!
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:96bke2d6p571jnmidup6bld6li16thvm48@.4ax.com...
> It looks to me like you are using a mapped drive which you
> don't want to use with services. Use a UNC path to a share
> that the service account for SQL Server has access to. If
> you created a share on the other server named SQLBackups,
> you would use something like:
> \\ServerName\SQLBackups\
> to reference the directory. So the backup would be:
> BACKUP DATABASE ArnesPriceBook
> TO DISK = '\\ServerName\SQLBackups\ArnesPriceBook_db.BAK'
> WITH INIT, SKIP
> -Sue
> On Mon, 21 Aug 2006 16:53:15 -0500, "WANNABE" <breichenbach
> AT istate DOT com> wrote:
>>Yes, I am logged in as Administrator, and YES it's a bad thing SQL server
>>and the agent run off the domain administrator ID.. The Id has full
>>access
>>to all it needs and more. After I create a device for that UNC location
>>the
>>script works when backing up to the device across the network, but That
>>will
>>require many changes to what the plan for tonight was, and I am not sure
>>how
>>to restore a device back up to a different server. ANY further
>>suggestions
>>and help would be much appreciated.
>>==========================================>>"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
>>news:44EA1E4D.2070305@.realsqlguy.com...
>> WANNABE wrote:
>> Sorry, I need to run backups to a shared drive on another server, and
>> everything I have tried for the last hour will not work..
>> I have full access to the drive, can copy and paste anything into it,
>> but
>> when I run this Query
>> BACKUP DATABASE ArnesPriceBook
>> TO DISK = 'L:\ArnesPriceBook_db.BAK'
>> WITH INIT, SKIP
>> It comes back with "Cannot open backup device
>> 'I:\ArnesPriceBook_db.BAK'.
>> Device error or device off-line"
>> The local drives are compressed and there are space issues. How can I
>> back this up to a mapped or UNC location'
>> Thank you :-)
>> *YOU* may have access to that share, but does SQL? Look at the
>> properties
>> of the SQL Server and SQL Agent services - are they running as "Local
>> System" or as a domain user? To access network resources, they must run
>> as a domain user, and that user must have access to the network
>> resources.
>>
>> --
>> Tracy McKibben
>> MCDBA
>> http://www.realsqlguy.com
>|||It's more robust to use UNC than use a mapped drive letter. But if you must,
you can run EXEC master..xp_cmdshell 'net use L: \\theServer\theShare' to map
the drive before running the backup step in the same T-SQL script.
Linchi
"WANNABE" wrote:
> Yes, I am logged in as Administrator, and YES it's a bad thing SQL server
> and the agent run off the domain administrator ID.. The Id has full access
> to all it needs and more. After I create a device for that UNC location the
> script works when backing up to the device across the network, but That will
> require many changes to what the plan for tonight was, and I am not sure how
> to restore a device back up to a different server. ANY further suggestions
> and help would be much appreciated.
> ==========================================> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
> news:44EA1E4D.2070305@.realsqlguy.com...
> > WANNABE wrote:
> >> Sorry, I need to run backups to a shared drive on another server, and
> >> everything I have tried for the last hour will not work..
> >> I have full access to the drive, can copy and paste anything into it, but
> >> when I run this Query
> >> BACKUP DATABASE ArnesPriceBook
> >> TO DISK = 'L:\ArnesPriceBook_db.BAK'
> >> WITH INIT, SKIP
> >> It comes back with "Cannot open backup device 'I:\ArnesPriceBook_db.BAK'.
> >> Device error or device off-line"
> >> The local drives are compressed and there are space issues. How can I
> >> back this up to a mapped or UNC location'
> >> Thank you :-)
> >
> > *YOU* may have access to that share, but does SQL? Look at the properties
> > of the SQL Server and SQL Agent services - are they running as "Local
> > System" or as a domain user? To access network resources, they must run
> > as a domain user, and that user must have access to the network resources.
> >
> >
> > --
> > Tracy McKibben
> > MCDBA
> > http://www.realsqlguy.com
>
>

Quick and Easy SQL Question . . .

I don't have much experience with writing Sql, which is why i'm not sure.

But basically, I have a MSSQL 2005 Database that a person can list a property with,

and if the property listing expires by date, the user can relist the listing.

The records on the search function are automatically sorted by date, and I want the relisted records to update the datecreated.

I added "DateCreated" to the relisting storedprocedure to change DateCreated to todays date, but I am guessing.

Specials = @.Specials,

ExpirationDate = @.ExpirationDate ,

DateCreated =GetDate(),

DateApproved = @.DateApproved,

This seems to work, but I just wanted to check to make sure it's in the proper format.

Thank you

Daniel Meis

darkknight187:

DateCreated =GetDate()

That is the right way to do it.

|||

Thank you for your reply, just wanted to be sure.

Quick and Easy Replication

This summary is not available. Please click here to view the post.

Quick and easy question, Sql update method . . .

I am new to Sql, so I think this is a really easy question.

But I am working on a 2005 MSSQL database.

When I add columns to an existing application with data and tables already being used, and the new column will be set to Database Null.

What is an easy way to quickly add the data to all the rows in the table.

For example if I am adding a checkbox, I've been doing it manually, add the column, change all the rows to False, one by one.

And then I can change it to Disallow DBNull.

As I get more and more users this could be a very time consuming process.

So the name of the Table is classifeds_Ads and let's say the column I want to add is Bonus and it needs to be filled with False.

How do I do this?

Thank you in advance

Daniel Meis

You can create a new query and then just run this:

UPDATE classifieds_Ads Set Bonus = 0

Or an easier approach is to use the Column Properties pane to set the initial properties for the column - Allow Nulls: No, Default Value or Binding: 0

|||

Works great, Thank you.

Quick and Easy question

I want to present an option to one of my clients to
switch from Exchange to an SQL database.
They are using Public folder for a lot and we get blamed
everytime something gets deleted by a user. They do have
a lot of data in PF. I also know that when something goes
awol and we have to restore the PF by dismounting PF and
taking everyone down for however long.
My questions are these:
How quickly can you restore SQL. Do you have to take the
whole database down to restore; can you restore just
certain tables, data, etc ..; can the users still work
away while restoring any portion of the database?
Thanks for all the help!!
Kevin
A database is unavailable while it is being restored (although it can be in
use while being backed-up). You can control the placement of individual
tables and indexes on filegroups and then backup or restore those filegroups
individually. You can also do a point-in-time restore which allows you to
roll back to a point before any data loss or corruption occurred.
However, you seem to be asking the wrong question. I don't know Exchange but
in SQLServer the solution to your problem is to implement user/object based
permissions so as to prevent users from deleting data that they are not
authorized to delete.
David Portas
SQL Server MVP

Quick and Easy question

I want to present an option to one of my clients to
switch from Exchange to an SQL database.
They are using Public folder for a lot and we get blamed
everytime something gets deleted by a user. They do have
a lot of data in PF. I also know that when something goes
awol and we have to restore the PF by dismounting PF and
taking everyone down for however long.
My questions are these:
How quickly can you restore SQL. Do you have to take the
whole database down to restore; can you restore just
certain tables, data, etc ..; can the users still work
away while restoring any portion of the database?
Thanks for all the help!!
KevinA database is unavailable while it is being restored (although it can be in
use while being backed-up). You can control the placement of individual
tables and indexes on filegroups and then backup or restore those filegroups
individually. You can also do a point-in-time restore which allows you to
roll back to a point before any data loss or corruption occurred.
However, you seem to be asking the wrong question. I don't know Exchange but
in SQLServer the solution to your problem is to implement user/object based
permissions so as to prevent users from deleting data that they are not
authorized to delete.
--
David Portas
SQL Server MVP
--

Quick and Easy question

I want to present an option to one of my clients to
switch from Exchange to an SQL database.
They are using Public folder for a lot and we get blamed
everytime something gets deleted by a user. They do have
a lot of data in PF. I also know that when something goes
awol and we have to restore the PF by dismounting PF and
taking everyone down for however long.
My questions are these:
How quickly can you restore SQL. Do you have to take the
whole database down to restore; can you restore just
certain tables, data, etc ..; can the users still work
away while restoring any portion of the database?
Thanks for all the help!!
KevinA database is unavailable while it is being restored (although it can be in
use while being backed-up). You can control the placement of individual
tables and indexes on filegroups and then backup or restore those filegroups
individually. You can also do a point-in-time restore which allows you to
roll back to a point before any data loss or corruption occurred.
However, you seem to be asking the wrong question. I don't know Exchange but
in SQLServer the solution to your problem is to implement user/object based
permissions so as to prevent users from deleting data that they are not
authorized to delete.
David Portas
SQL Server MVP
--

quick and easy install question

I would like to install SQL Server 2000 Personal Edition, but I don't know
where to find the install file? Is it on the SQL Server Enterprise cd? Is
it on its own cd?
Thanks,
KeithOn its own CD, which you need to find somewhere. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Keith" <Keith@.noemail.com> wrote in message
news:eMJuLpEDEHA.3348@.TK2MSFTNGP11.phx.gbl...
> I would like to install SQL Server 2000 Personal Edition, but I don't know
> where to find the install file? Is it on the SQL Server Enterprise cd?
Is
> it on its own cd?
> Thanks,
> Keith
>

Quick 6.5 question

Does anyone know the maximum number of rows a SQL 6.5
table can hold..
I've got an old 3rd party app suddenly throwing out lots
of these errors - 'The server could not expand a table
because the table reached the maximum size. '
Thanks in advance.Does the NT event log say, Event ID 2009'
//Ralph
>--Original Message--
>Does anyone know the maximum number of rows a SQL 6.5
>table can hold..
>I've got an old 3rd party app suddenly throwing out lots
>of these errors - 'The server could not expand a table
>because the table reached the maximum size. '
>
>Thanks in advance.
>.
>|||ok,
It doesn't neccessary need to be table in SQL server,
according to articles I found it is when creating a table
in the memory (system).
http://www.eventid.net/display.asp?eventid=2009&source=
The last section of that page you got links and
explanations about the error....
Good Luck.
//Ralph
>--Original Message--
>yes it does..
>
>>--Original Message--
>>Does the NT event log say, Event ID 2009'
>>//Ralph
>>--Original Message--
>>Does anyone know the maximum number of rows a SQL 6.5
>>table can hold..
>>I've got an old 3rd party app suddenly throwing out
lots
>>of these errors - 'The server could not expand a table
>>because the table reached the maximum size. '
>>
>>Thanks in advance.
>>.
>>.
>.
>|||Ralph, thanks - looks like a very useful link...
Cheers, steve
>--Original Message--
>ok,
>It doesn't neccessary need to be table in SQL server,
>according to articles I found it is when creating a table
>in the memory (system).
>http://www.eventid.net/display.asp?eventid=2009&source=
>The last section of that page you got links and
>explanations about the error....
>Good Luck.
>//Ralph
>
>>--Original Message--
>>yes it does..
>>
>>--Original Message--
>>Does the NT event log say, Event ID 2009'
>>//Ralph
>>--Original Message--
>>Does anyone know the maximum number of rows a SQL 6.5
>>table can hold..
>>I've got an old 3rd party app suddenly throwing out
>lots
>>of these errors - 'The server could not expand a table
>>because the table reached the maximum size. '
>>
>>Thanks in advance.
>>.
>>.
>>.
>.
>

Quick ? on Backup

Is there a way to run a T-SQL or scheduled task backup, to incorporate only
some tables? I want to do a backup of a DB, but exclude a table or two. Is
that possible? If so, where do I get the info on it?
Thanks.Hi,
Not directly.
Since Filegroup backup is possible you can create seperate file groups and
create the required tables to backup in the seperate file group. So as you
can backup the file group.
Thnks
Hari
MCDBA
"CQL User" <foo@.cqlcorp.com> wrote in message
news:OvZCa7UKEHA.1192@.TK2MSFTNGP11.phx.gbl...
> Is there a way to run a T-SQL or scheduled task backup, to incorporate
only
> some tables? I want to do a backup of a DB, but exclude a table or two.
Is
> that possible? If so, where do I get the info on it?
> Thanks.
>

Quick ? on Backup

Is there a way to run a T-SQL or scheduled task backup, to incorporate only
some tables? I want to do a backup of a DB, but exclude a table or two. Is
that possible? If so, where do I get the info on it?
Thanks.Hi,
Not directly.
Since Filegroup backup is possible you can create seperate file groups and
create the required tables to backup in the seperate file group. So as you
can backup the file group.
Thnks
Hari
MCDBA
"CQL User" <foo@.cqlcorp.com> wrote in message
news:OvZCa7UKEHA.1192@.TK2MSFTNGP11.phx.gbl...
> Is there a way to run a T-SQL or scheduled task backup, to incorporate
only
> some tables? I want to do a backup of a DB, but exclude a table or two.
Is
> that possible? If so, where do I get the info on it?
> Thanks.
>

Quick ? on Backup

Is there a way to run a T-SQL or scheduled task backup, to incorporate only
some tables? I want to do a backup of a DB, but exclude a table or two. Is
that possible? If so, where do I get the info on it?
Thanks.
Hi,
Not directly.
Since Filegroup backup is possible you can create seperate file groups and
create the required tables to backup in the seperate file group. So as you
can backup the file group.
Thnks
Hari
MCDBA
"CQL User" <foo@.cqlcorp.com> wrote in message
news:OvZCa7UKEHA.1192@.TK2MSFTNGP11.phx.gbl...
> Is there a way to run a T-SQL or scheduled task backup, to incorporate
only
> some tables? I want to do a backup of a DB, but exclude a table or two.
Is
> that possible? If so, where do I get the info on it?
> Thanks.
>

Quey plan from profiler

Hi DB gurus,

I was using Profiler to capture query plan, but I could not see the actual plan though. In the EventClass it shows "Show Plan Text" but the data column with TextData is blank.

Can somebody explain how to do this. I am trying to see what query plan is used by sql coming from application.

Any help is highly appreciated.

Thanks
RachaelBetter to use Query Analyzer than profiler.

quey help

Excuse me for not putting a better name to this but I cannot think of a
suitable subject title!

anyway, here's what I am trying to do

At work we have a fair amount of data that is to do with people and what
jobs they have completed and at what date
so for example
Joe Bloggs did design work at 12/12/2004 and spent 5 hours on it.
Now, we have a lot of jobs that people haven't put any hours down for at
all,
and I want to find all those jobs which have had no hours put to them since
the 30/09/2004
I had written a query like so
SELECT name, job, date
FROM table
WHERE hours = 0 AND date >='30/09/2004'
But this doesn't work as some jobs may come up which have had zero hours on
some particular dates but on some dates that job does have hours on.
What I want is to show since 30/09/2004 any jobs which have had no hours at
all booked to them consistently.
So in effect all the jobs we know longer use and haven't since that date.

How would I do that?
I know you can probably only give me a vague idea as I have provided no
table structure etc.. but I only want a hint really...

Thanks

JayneOn Tue, 26 Apr 2005 20:32:23 +0100, Little PussyCat wrote:

(snip)
>What I want is to show since 30/09/2004 any jobs which have had no hours at
>all booked to them consistently.
>So in effect all the jobs we know longer use and haven't since that date.
>How would I do that?

Hi Jayne,

Try if this works:

SELECT job
FROM table
WHERE date >= '20040930'-- Note the unambiguous date format!
GROUP BY job
HAVING SUM(hours) = 0

However, this requires that there are rows for the job in the table, with
a number of hours equal to 0. If the table has no rows in the specified
date range for the the job, then this query won't find them.

>I know you can probably only give me a vague idea as I have provided no
>table structure etc.. but I only want a hint really...

If the above doesn't work and you need better hints, then check out this
site before posting a followup: www.aspfaq.com/5006.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hi Jayne,

Would something like this work?

SELECT
t.name,
t.job,
t.date
FROM
table t join
(select name, job, sum(hours) as hours from table where date
>='30/09/2004' group by name, job having sum(hours)=0)dt
on t.name=dt.name and t.job=dt.job
WHERE
t.date >='30/09/2004'

Queuing Files

I have a requirement of instantiating a job which will copy and move the
newly created file once a file has been created in a folder. How can I
perform this in SQL Server. Can you please give your thoughts about this.
What I was planning was to write a visual basic code which will pool the
files getting created into the folder and accordingly copy and paste into
another folder. I feel this is not a better method.
I really wanted a queuing system to be implemented.Hi, there's a script I am using to copy files:
USE [msdb]
GO
/****** Object: Job [CopyToCluster] Script Date: 03/20/2006 15:24:31 ******/
BEGIN TRANSACTION
DECLARE @.ReturnCode INT
SELECT @.ReturnCode = 0
/****** Object: JobCategory [Database Maintenance] Script Date: 03/20/2006
15:24:31 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Database
Maintenance' AND category_class=1)
BEGIN
EXEC @.ReturnCode = msdb.dbo.sp_add_category @.class=N'JOB', @.type=N'LOCAL',
@.name=N'Database Maintenance'
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @.jobId BINARY(16)
EXEC @.ReturnCode = msdb.dbo.sp_add_job @.job_name=N'CopyToCluster',
@.enabled=1,
@.notify_level_eventlog=2,
@.notify_level_email=0,
@.notify_level_netsend=0,
@.notify_level_page=0,
@.delete_level=0,
@.description=N'Copies all files rom the local backup directory to
\\datacluster\\Backup',
@.category_name=N'Database Maintenance',
@.owner_login_name=N'YOUR_USER', @.job_id = @.jobId OUTPUT
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Copy files] Script Date: 03/20/2006 15:24:31 ******/
EXEC @.ReturnCode = msdb.dbo.sp_add_jobstep @.job_id=@.jobId, @.step_name=N'Copy
files',
@.step_id=1,
@.cmdexec_success_code=0,
@.on_success_action=1,
@.on_success_step_id=0,
@.on_fail_action=2,
@.on_fail_step_id=0,
@.retry_attempts=2,
@.retry_interval=0,
@.os_run_priority=0, @.subsystem=N'CmdExec',
@.command=N'c:\copybackups.cmd $(DATE)$(TIME)',
@.flags=4
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
EXEC @.ReturnCode = msdb.dbo.sp_update_job @.job_id = @.jobId, @.start_step_id =
1
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
EXEC @.ReturnCode = msdb.dbo.sp_add_jobschedule @.job_id=@.jobId,
@.name=N'CopyBackupFilesSched',
@.enabled=1,
@.freq_type=4,
@.freq_interval=1,
@.freq_subday_type=1,
@.freq_subday_interval=0,
@.freq_relative_interval=0,
@.freq_recurrence_factor=0,
@.active_start_date=20060106,
@.active_end_date=99991231,
@.active_start_time=50000,
@.active_end_time=235959
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
EXEC @.ReturnCode = msdb.dbo.sp_add_jobserver @.job_id = @.jobId, @.server_name
= N'(local)'
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@.@.TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
and this is copypackups.cmd:
rem PR 2006-01-09
rem This batch file copies daily backups of databases to network location
rem
@.echo . > c:\copybackups.log
@.echo %1 Copying files from c:\SQLBackup to \\datacluster\Backup... >>
c:\copybackups.log
xcopy c:\SQLBackup\*.* \\datacluster\Backup\*.* /L /R /H /D /V /Y /F /C >>
c:\copybackups.log
xcopy c:\SQLBackup\*.* \\datacluster\Backup\*.* /R /H /D /V /Y /F /C >>
c:\copybackups.log
@.echo done >> c:\copybackups.log
@.echo . >> c:\copybackups.log
Note that this file does not use date and time passed by the job. I have
another job that removes older files from local directory.
HTH
Peter|||Thank you very much for your response.
What I need is watch for newly added files to a folder. The moment a new
file is added, I need to copy and paste into another location. This is
something like a service which keeps on monitoring a folder for new files
coming in.
Finally once the day is fininshed I will have a copy of the master folder in
another location also. I need to do this on receipt of each file, not finall
y
at the end of a day.
Thanks & Regards,
VB Babunath
"Rogas69" wrote:

> Hi, there's a script I am using to copy files:
> USE [msdb]
> GO
> /****** Object: Job [CopyToCluster] Script Date: 03/20/2006 15:24:31 ******/
> BEGIN TRANSACTION
> DECLARE @.ReturnCode INT
> SELECT @.ReturnCode = 0
> /****** Object: JobCategory [Database Maintenance] Script Date: 03/20/2006
> 15:24:31 ******/
> IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Databa
se
> Maintenance' AND category_class=1)
> BEGIN
> EXEC @.ReturnCode = msdb.dbo.sp_add_category @.class=N'JOB', @.type=N'LOCAL',
> @.name=N'Database Maintenance'
> IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
> END
> DECLARE @.jobId BINARY(16)
> EXEC @.ReturnCode = msdb.dbo.sp_add_job @.job_name=N'CopyToCluster',
> @.enabled=1,
> @.notify_level_eventlog=2,
> @.notify_level_email=0,
> @.notify_level_netsend=0,
> @.notify_level_page=0,
> @.delete_level=0,
> @.description=N'Copies all files rom the local backup directory to
> \\datacluster\\Backup',
> @.category_name=N'Database Maintenance',
> @.owner_login_name=N'YOUR_USER', @.job_id = @.jobId OUTPUT
> IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
> /****** Object: Step [Copy files] Script Date: 03/20/2006 15:24:31 ******/
> EXEC @.ReturnCode = msdb.dbo.sp_add_jobstep @.job_id=@.jobId, @.step_name=N'Co
py
> files',
> @.step_id=1,
> @.cmdexec_success_code=0,
> @.on_success_action=1,
> @.on_success_step_id=0,
> @.on_fail_action=2,
> @.on_fail_step_id=0,
> @.retry_attempts=2,
> @.retry_interval=0,
> @.os_run_priority=0, @.subsystem=N'CmdExec',
> @.command=N'c:\copybackups.cmd $(DATE)$(TIME)',
> @.flags=4
> IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
> EXEC @.ReturnCode = msdb.dbo.sp_update_job @.job_id = @.jobId, @.start_step_id
=
> 1
> IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
> EXEC @.ReturnCode = msdb.dbo.sp_add_jobschedule @.job_id=@.jobId,
> @.name=N'CopyBackupFilesSched',
> @.enabled=1,
> @.freq_type=4,
> @.freq_interval=1,
> @.freq_subday_type=1,
> @.freq_subday_interval=0,
> @.freq_relative_interval=0,
> @.freq_recurrence_factor=0,
> @.active_start_date=20060106,
> @.active_end_date=99991231,
> @.active_start_time=50000,
> @.active_end_time=235959
> IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
> EXEC @.ReturnCode = msdb.dbo.sp_add_jobserver @.job_id = @.jobId, @.server_nam
e
> = N'(local)'
> IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
> COMMIT TRANSACTION
> GOTO EndSave
> QuitWithRollback:
> IF (@.@.TRANCOUNT > 0) ROLLBACK TRANSACTION
> EndSave:
>
> and this is copypackups.cmd:
> rem PR 2006-01-09
> rem This batch file copies daily backups of databases to network location
> rem
> @.echo . > c:\copybackups.log
> @.echo %1 Copying files from c:\SQLBackup to \\datacluster\Backup... >>
> c:\copybackups.log
> xcopy c:\SQLBackup\*.* \\datacluster\Backup\*.* /L /R /H /D /V /Y /F /C >>
> c:\copybackups.log
> xcopy c:\SQLBackup\*.* \\datacluster\Backup\*.* /R /H /D /V /Y /F /C >>
> c:\copybackups.log
> @.echo done >> c:\copybackups.log
> @.echo . >> c:\copybackups.log
>
> Note that this file does not use date and time passed by the job. I have
> another job that removes older files from local directory.
> HTH
> Peter
>
>

QueueSizeMultiplier - Merge Agent Profile Parameter SQL 2005

I see a parameter in the "high volume server-to-server" merge agent profile
named QueueSizeMultiplier.
Can anyone tell me...
The purpose of this paramter
The possible values and their meaning.
Its seems to be undocumented. Default is null, maximum listed seems to be 5.
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
"Pat Clough" <Pat Clough@.discussions.microsoft.com> wrote in message
news:B4E5DBAB-9734-4AC2-BCA3-E85F512F22F0@.microsoft.com...
>I see a parameter in the "high volume server-to-server" merge agent profile
> named QueueSizeMultiplier.
> Can anyone tell me...
> The purpose of this paramter
> The possible values and their meaning.

Queues and Filegroups

Hi

We can create a queue on a filegroup for performance reasons however what about the sys.transmission_queue which could have messages backing up when the target is down.

What are your thoughts.

Cheers

Currently there is no way to change the filegroup of the sys.transmission_queue.

Traditionaly, all system tables are allocated on the primary filegroup. The sys.transmission_queue is a system table, the database cannot function w/o it. Allowing for it to be moved a user filegroup opens the door to lossing it if the filegroup is lost. Allowing sys.transmission_queue to partition itself across filegroups causes similar problems. But your concern is valid, that this is an example of a system table than can grow large and cause problems on the primary filegroup. You can use the Product Feedback to make a sugestion if you feel this is important. http://lab.msdn.microsoft.com/productfeedback/

Regards,
~ Remus

|||

Remus

Thanks.

queueing xml output to msmq

does anyone have an idea how can i queue xml output to msmq from the sql
query itselfSee KB article
http://support.microsoft.com/defaul...kb;en-us;555070
Sending a message to MSMQ from SQL requires writing a COM object and then
calling that COM object from a T-SQL procedure.
Mike
"kamal" <kamal@.discussions.microsoft.com> wrote in message
news:4B29558A-2504-4DC5-90FA-B80436406DF8@.microsoft.com...
> does anyone have an idea how can i queue xml output to msmq from the sql
> query itself
>|||I think in most cases you will find using an external application to
retrieve the XML and then send the MSMQ message will be more efficient
because the stored procedure will have to open the connection to MSMQ every
time it runs.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Mike Jansen" <mjansen_nntp@.mail.com> wrote in message
news:uJtjAmNVFHA.3760@.TK2MSFTNGP15.phx.gbl...
> See KB article
> http://support.microsoft.com/defaul...kb;en-us;555070
> Sending a message to MSMQ from SQL requires writing a COM object and then
> calling that COM object from a T-SQL procedure.
> Mike
> "kamal" <kamal@.discussions.microsoft.com> wrote in message
> news:4B29558A-2504-4DC5-90FA-B80436406DF8@.microsoft.com...
>|||I've got a synchronization application (toned-down version of replication
with a few special requirements in it) that does that, but eliminates
polling by the application. I wrote an extended stored proc that sets a
named Windows event whenever another process needs to act. Code in a trigger
(in my case) calls the extended proc to set the event. The other process
waits on the named Windows event; when the event is fired, it queries the
database and does what it needs to do, which in this case actually happens
to be sending messages via MSMQ. You can then do all the performance
optimizations in the app, like Roger was saying, with the added benefit of
not having to poll. Of course, the drawback is that you are using a custom
extended procedure.
Mike
"Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
news:eWex$gRVFHA.928@.TK2MSFTNGP15.phx.gbl...
> I think in most cases you will find using an external application to
> retrieve the XML and then send the MSMQ message will be more efficient
> because the stored procedure will have to open the connection to MSMQ
every
> time it runs.
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Mike Jansen" <mjansen_nntp@.mail.com> wrote in message
> news:uJtjAmNVFHA.3760@.TK2MSFTNGP15.phx.gbl...
then
sql
>

queueing xml output to msmq

does anyone have an idea how can i queue xml output to msmq from the sql
query itself
See KB article
http://support.microsoft.com/default...b;en-us;555070
Sending a message to MSMQ from SQL requires writing a COM object and then
calling that COM object from a T-SQL procedure.
Mike
"kamal" <kamal@.discussions.microsoft.com> wrote in message
news:4B29558A-2504-4DC5-90FA-B80436406DF8@.microsoft.com...
> does anyone have an idea how can i queue xml output to msmq from the sql
> query itself
>
|||I think in most cases you will find using an external application to
retrieve the XML and then send the MSMQ message will be more efficient
because the stored procedure will have to open the connection to MSMQ every
time it runs.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Mike Jansen" <mjansen_nntp@.mail.com> wrote in message
news:uJtjAmNVFHA.3760@.TK2MSFTNGP15.phx.gbl...
> See KB article
> http://support.microsoft.com/default...b;en-us;555070
> Sending a message to MSMQ from SQL requires writing a COM object and then
> calling that COM object from a T-SQL procedure.
> Mike
> "kamal" <kamal@.discussions.microsoft.com> wrote in message
> news:4B29558A-2504-4DC5-90FA-B80436406DF8@.microsoft.com...
>
|||I've got a synchronization application (toned-down version of replication
with a few special requirements in it) that does that, but eliminates
polling by the application. I wrote an extended stored proc that sets a
named Windows event whenever another process needs to act. Code in a trigger
(in my case) calls the extended proc to set the event. The other process
waits on the named Windows event; when the event is fired, it queries the
database and does what it needs to do, which in this case actually happens
to be sending messages via MSMQ. You can then do all the performance
optimizations in the app, like Roger was saying, with the added benefit of
not having to poll. Of course, the drawback is that you are using a custom
extended procedure.
Mike
"Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
news:eWex$gRVFHA.928@.TK2MSFTNGP15.phx.gbl...
> I think in most cases you will find using an external application to
> retrieve the XML and then send the MSMQ message will be more efficient
> because the stored procedure will have to open the connection to MSMQ
every
> time it runs.
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.[vbcol=seagreen]
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Mike Jansen" <mjansen_nntp@.mail.com> wrote in message
> news:uJtjAmNVFHA.3760@.TK2MSFTNGP15.phx.gbl...
then[vbcol=seagreen]
sql
>

Queueing log messages

I have a large web application with several web servers and several sql
servers. For debugging and usage tracking, the web applications write log
messages to a common log database on one of the sql servers. This traffic can
get quite extensive and, although desirable, it should not interfere with the
mainline user processing if possible.
In a previous project, I used an MSMQ to decouple the logging activity from
the mainline processing. The web applications wrote their messages to the
queue, which was a very fast operation, and the queue buffered the entry of
the log data into the database queue. In this case, we wrote a custom service
that read the queue and inserted the results into the database table.
With all the new fancy features of SS 2005 and .NET 2.0, I wonder if there
isn't a better way to do this. For example, would using Service Broker queues
be an option? Although most of the examples I've seen talk about sending
messages from one database to another, it looks like you can write C# code to
send SSB messages from an external application - ie. the web app. Then we'd
write an activation method on the queue that would simply write the log
messages into the database.
How would this work? Or is inserting someting into an SSB queue about the
same as just inserting the log record into the table itself, from a
performance point of view?
Are SSB queues implemented with MSMQ? or something else?
Or should I stick with MSMQ itself? Writing the application end (ie. the
sending end) of the queue is easy; but is there a better way to handle the
receiving end than building a whole service? For example, is there some way
to use CLR integration - or something else - to directly read from the queue
and insert rows into the log table?
All suggestions gratefully accepted
...Mike
> How would this work? Or is inserting someting into an SSB queue about the
> same as just inserting the log record into the table itself, from a
> performance point of view?
I would expect that inserting directly into the log table would generally be
more efficient than a Service Broker queue or a transactional MSMQ for such
a simple task. The issue is that a synchronous physical write is required
to guarantee message delivery regardless of the technology. Service Broker,
MSMQ or a regular table insert must all wait for a write to complete before
returning to back to the client. Service Broker's sweet spot is more for
asynchronous processing and scale-out.
If you don't need guaranteed delivery, I think a non-transactional MSMQ
would probably provide the best response time. However, if your log table
is optimized for writes, I think it would be a close call between the
inserts and MSMQ. I suggest you run performance tests to determine the best
approach for your environment.

> Are SSB queues implemented with MSMQ? or something else?
Queues are schema-owned objects much like regular tables.
Hope this helps.
Dan Guzman
SQL Server MVP
"Mike Kraley" <mkraley@.community.nospam> wrote in message
news:24174429-AD79-47DF-AE08-E2C2D62ACFF6@.microsoft.com...
>I have a large web application with several web servers and several sql
> servers. For debugging and usage tracking, the web applications write log
> messages to a common log database on one of the sql servers. This traffic
> can
> get quite extensive and, although desirable, it should not interfere with
> the
> mainline user processing if possible.
> In a previous project, I used an MSMQ to decouple the logging activity
> from
> the mainline processing. The web applications wrote their messages to the
> queue, which was a very fast operation, and the queue buffered the entry
> of
> the log data into the database queue. In this case, we wrote a custom
> service
> that read the queue and inserted the results into the database table.
> With all the new fancy features of SS 2005 and .NET 2.0, I wonder if there
> isn't a better way to do this. For example, would using Service Broker
> queues
> be an option? Although most of the examples I've seen talk about sending
> messages from one database to another, it looks like you can write C# code
> to
> send SSB messages from an external application - ie. the web app. Then
> we'd
> write an activation method on the queue that would simply write the log
> messages into the database.
> How would this work? Or is inserting someting into an SSB queue about the
> same as just inserting the log record into the table itself, from a
> performance point of view?
> Are SSB queues implemented with MSMQ? or something else?
> Or should I stick with MSMQ itself? Writing the application end (ie. the
> sending end) of the queue is easy; but is there a better way to handle the
> receiving end than building a whole service? For example, is there some
> way
> to use CLR integration - or something else - to directly read from the
> queue
> and insert rows into the log table?
> All suggestions gratefully accepted
> --
> ...Mike
|||Thanks to both Dan and Charles for your replies. A few followups:
The issue I'm really worried about here is the backup of inserting many
items into a potentially large table, ie. the log table. I don't want this
overhead to be in series with the "real" transactions.
1. What do you think about using a direct insert into the log table, but
using an async "fire and forget" call?
2. If I do go with the MSMQ solution, is there a better way than an external
service? Is there some way to use SQL Server's CLR to read from the queue? I
suspect this is possible, but I haven't found a good example of this
anywhere. Any suggestions?
3. Dan, you mentioned "if your log table is optimized for writes" - what did
you have in mind here?
thanks...Mike
|||Hi Mike,
For your questions:
> 1. What do you think about using a direct insert into the log table, but
> using an async "fire and forget" call?
If there is no explicit performance issues, it is no problem to use a
direct insert into a log table. Actually this is the most common way in
normal systems. For your system, I think that async "fire and forget" may
work more efficient since your system has intensive logging activities.

> 2. If I do go with the MSMQ solution, is there a better way than an
external
> service? Is there some way to use SQL Server's CLR to read from the
queue? I
> suspect this is possible, but I haven't found a good example of this
> anywhere. Any suggestions?
As far as I know, it is not possible to use SQL Server CLR to read from the
queue since SQL Server CLR cannot reference non-SQL Server projects. Could
you please let me know why you want to use SQL Server CLR to read from the
queue?
Best regards,
Charles Wang
Microsoft Online Community Support
================================================== ===
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====
|||Hi Mike,
For your questions:
> 1. What do you think about using a direct insert into the log table, but
> using an async "fire and forget" call?
If there is no explicit performance issues, it is no problem to use a
direct insert into a log table. Actually this is the most common way in
normal systems. For your system, I think that async "fire and forget" may
work more efficient since your system has intensive logging activities.

> 2. If I do go with the MSMQ solution, is there a better way than an
external
> service? Is there some way to use SQL Server's CLR to read from the
queue? I
> suspect this is possible, but I haven't found a good example of this
> anywhere. Any suggestions?
As far as I know, it is not possible to use SQL Server CLR to read from the
queue since SQL Server CLR cannot reference non-SQL Server projects. Could
you please let me know why you want to use SQL Server CLR to read from the
queue?
Best regards,
Charles Wang
Microsoft Online Community Support
================================================== ===
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====
|||> 1. What do you think about using a direct insert into the log table, but
> using an async "fire and forget" call?
I think an asynch call is a good idea. I don't know about the "forget" part
though; I think you'd want to know if it succeeded ;-)

> 2. If I do go with the MSMQ solution, is there a better way than an
> external
> service? Is there some way to use SQL Server's CLR to read from the queue?
> I
> suspect this is possible, but I haven't found a good example of this
> anywhere. Any suggestions?
A quick Google search turned up a MSMQ example at
(http://www.codeproject.com/useritems/SqlMSMQ.asp) but I haven't looked at
it. Although it may be possible for the SQL CLR to host the app, I don't
see much value in doing so. The service method you mentioned is the most
elegant but you could easily invoke a command-line utility using a
continuously running SQL Agent job that launches at startup.

> 3. Dan, you mentioned "if your log table is optimized for writes" - what
> did
> you have in mind here?
Specifically, I meant a table that has only a clustered index with a
increasing key (e.g. IDENTITY column or log datetime). This will perform
very well regardless of table size. I've successfully used that approach
with tables containing billions of rows.
You might be able to get away with additional non-clustered indexes too
depending on the table size and your i/o subsystem. Once you reach a
certain threshold, consider partitioning the table by date. This will keep
the current day data working set small and mitigate the overhead of
maintaining those non-clustered indexes while improve manageability.
Without table partitioning, you still have the option of moving data via a
daily process into an appropriately indexed reporting table. This works
best when current data are infrequently accessed and most queries are done
against historical data. You can use a view (perhaps partitioned) to make
the implementation abstract.
Hope this helps.
Dan Guzman
SQL Server MVP
"Mike Kraley" <mkraley@.community.nospam> wrote in message
news:48AC1D2D-73DE-4934-AE4A-57EFAB36333F@.microsoft.com...
> Thanks to both Dan and Charles for your replies. A few followups:
> The issue I'm really worried about here is the backup of inserting many
> items into a potentially large table, ie. the log table. I don't want this
> overhead to be in series with the "real" transactions.
> 1. What do you think about using a direct insert into the log table, but
> using an async "fire and forget" call?
> 2. If I do go with the MSMQ solution, is there a better way than an
> external
> service? Is there some way to use SQL Server's CLR to read from the queue?
> I
> suspect this is possible, but I haven't found a good example of this
> anywhere. Any suggestions?
> 3. Dan, you mentioned "if your log table is optimized for writes" - what
> did
> you have in mind here?
> thanks...Mike
|||Thanks again, both of you.
- why not use a service? Just because it is yet another "thing" that has to
be built, tested, deployed, maintained, operated, etc. If it can all be
magically included in the database, that seems a bit easier. Of course the
service is a workable solution if the DB can't do the job.
The SQL Agent task is another good idea.
The tips on table organization are also very helpful
...Mike
|||MSMQ will give you decoupling and not much more.
Service Broker will give you integrated storage of messages and data (i.e.
you have only one product/database to backup/restore), integration with
database clustering and database mirroring. It also gives you activation of
T-SQL or CLR procedures to process the messages. It provides guaranteed EOIO
(Exactly Once In Order) semantics for your messages and reliable
communication shutdown and error (think TCP (broker) vs. UDP (msmq)),
decouples physical location from logical destination (databases containing
Service Broker queues can be moved to new hosts and continue the existing
messaging sessions)
For what you describe there are two usual patterns:
- one SQL Server all applications connect to and send a message (using T-SQL
SEND verb), which is usefull when the goal is to return quickly control to
the application and let the processing happen asynchronously. This gives
decoupling from processing, but not from availability, i.e. if the SQL
server is down the application cannot log
- Each application (Web server) has a local SQL Express instance to which it
connects and issues the SEND verb and lest the Express isntance handle the
delivery of the message to the central log. This gives decoupling both from
processing and availability. The SQL Express availability is usually same as
the Web server availability. The trouble of deploying a SQL Express instance
on each web host is about the same as deploying a msmq queue, since this is
not a full blown SQL Server instance that needs maintenance and
administration, once deployed it can pretty much go on auto-pilot mode.
Have a look at the slides at
http://blogs.msdn.com/remusrusanu/archive/2007/04/03/orlando-slides-and-code.aspx
HTH,
~ Remus
"Mike Kraley" <mkraley@.community.nospam> wrote in message
news:24174429-AD79-47DF-AE08-E2C2D62ACFF6@.microsoft.com...
>I have a large web application with several web servers and several sql
> servers. For debugging and usage tracking, the web applications write log
> messages to a common log database on one of the sql servers. This traffic
> can
> get quite extensive and, although desirable, it should not interfere with
> the
> mainline user processing if possible.
> In a previous project, I used an MSMQ to decouple the logging activity
> from
> the mainline processing. The web applications wrote their messages to the
> queue, which was a very fast operation, and the queue buffered the entry
> of
> the log data into the database queue. In this case, we wrote a custom
> service
> that read the queue and inserted the results into the database table.
> With all the new fancy features of SS 2005 and .NET 2.0, I wonder if there
> isn't a better way to do this. For example, would using Service Broker
> queues
> be an option? Although most of the examples I've seen talk about sending
> messages from one database to another, it looks like you can write C# code
> to
> send SSB messages from an external application - ie. the web app. Then
> we'd
> write an activation method on the queue that would simply write the log
> messages into the database.
> How would this work? Or is inserting someting into an SSB queue about the
> same as just inserting the log record into the table itself, from a
> performance point of view?
> Are SSB queues implemented with MSMQ? or something else?
> Or should I stick with MSMQ itself? Writing the application end (ie. the
> sending end) of the queue is easy; but is there a better way to handle the
> receiving end than building a whole service? For example, is there some
> way
> to use CLR integration - or something else - to directly read from the
> queue
> and insert rows into the log table?
> All suggestions gratefully accepted
> --
> ...Mike

Queueing log messages

I have a large web application with several web servers and several sql
servers. For debugging and usage tracking, the web applications write log
messages to a common log database on one of the sql servers. This traffic ca
n
get quite extensive and, although desirable, it should not interfere with th
e
mainline user processing if possible.
In a previous project, I used an MSMQ to decouple the logging activity from
the mainline processing. The web applications wrote their messages to the
queue, which was a very fast operation, and the queue buffered the entry of
the log data into the database queue. In this case, we wrote a custom servic
e
that read the queue and inserted the results into the database table.
With all the new fancy features of SS 2005 and .NET 2.0, I wonder if there
isn't a better way to do this. For example, would using Service Broker queue
s
be an option? Although most of the examples I've seen talk about sending
messages from one database to another, it looks like you can write C# code t
o
send SSB messages from an external application - ie. the web app. Then we'd
write an activation method on the queue that would simply write the log
messages into the database.
How would this work? Or is inserting someting into an SSB queue about the
same as just inserting the log record into the table itself, from a
performance point of view?
Are SSB queues implemented with MSMQ? or something else?
Or should I stick with MSMQ itself? Writing the application end (ie. the
sending end) of the queue is easy; but is there a better way to handle the
receiving end than building a whole service? For example, is there some way
to use CLR integration - or something else - to directly read from the queue
and insert rows into the log table?
All suggestions gratefully accepted
...Mike> How would this work? Or is inserting someting into an SSB queue about the
> same as just inserting the log record into the table itself, from a
> performance point of view?
I would expect that inserting directly into the log table would generally be
more efficient than a Service Broker queue or a transactional MSMQ for such
a simple task. The issue is that a synchronous physical write is required
to guarantee message delivery regardless of the technology. Service Broker,
MSMQ or a regular table insert must all wait for a write to complete before
returning to back to the client. Service Broker's sweet spot is more for
asynchronous processing and scale-out.
If you don't need guaranteed delivery, I think a non-transactional MSMQ
would probably provide the best response time. However, if your log table
is optimized for writes, I think it would be a close call between the
inserts and MSMQ. I suggest you run performance tests to determine the best
approach for your environment.

> Are SSB queues implemented with MSMQ? or something else?
Queues are schema-owned objects much like regular tables.
Hope this helps.
Dan Guzman
SQL Server MVP
"Mike Kraley" <mkraley@.community.nospam> wrote in message
news:24174429-AD79-47DF-AE08-E2C2D62ACFF6@.microsoft.com...
>I have a large web application with several web servers and several sql
> servers. For debugging and usage tracking, the web applications write log
> messages to a common log database on one of the sql servers. This traffic
> can
> get quite extensive and, although desirable, it should not interfere with
> the
> mainline user processing if possible.
> In a previous project, I used an MSMQ to decouple the logging activity
> from
> the mainline processing. The web applications wrote their messages to the
> queue, which was a very fast operation, and the queue buffered the entry
> of
> the log data into the database queue. In this case, we wrote a custom
> service
> that read the queue and inserted the results into the database table.
> With all the new fancy features of SS 2005 and .NET 2.0, I wonder if there
> isn't a better way to do this. For example, would using Service Broker
> queues
> be an option? Although most of the examples I've seen talk about sending
> messages from one database to another, it looks like you can write C# code
> to
> send SSB messages from an external application - ie. the web app. Then
> we'd
> write an activation method on the queue that would simply write the log
> messages into the database.
> How would this work? Or is inserting someting into an SSB queue about the
> same as just inserting the log record into the table itself, from a
> performance point of view?
> Are SSB queues implemented with MSMQ? or something else?
> Or should I stick with MSMQ itself? Writing the application end (ie. the
> sending end) of the queue is easy; but is there a better way to handle the
> receiving end than building a whole service? For example, is there some
> way
> to use CLR integration - or something else - to directly read from the
> queue
> and insert rows into the log table?
> All suggestions gratefully accepted
> --
> ...Mike|||Hi Mike,
Did you encounter any issue of using MSMQ? If MSMQ worked fine, I think
that it is no need to change your current implementation. SQL Server
Service Broker also provides a queue for messages; however it is not MSMQ.
The explicit difference between Service Broker and MSMQ is that for Service
Broker you can use T-SQL to send messages while for MSMQ you need to run
MSMQ API to send messages.
For more detailed information, y ou may refer to:
Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043.aspx
From your description, I saw that your MSMQ resolution seemed efficient and
worked fine now, so my recommendation here is just keeping it there until
it does not satisfy your requirements.
If you have any other questions or concerns, please feel free to let me
know. Have a good day!
Best regards,
Charles Wang
Microsoft Online Community Support
========================================
=============
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscript...t/default.aspx.
========================================
==============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============|||Thanks to both Dan and Charles for your replies. A few followups:
The issue I'm really worried about here is the backup of inserting many
items into a potentially large table, ie. the log table. I don't want this
overhead to be in series with the "real" transactions.
1. What do you think about using a direct insert into the log table, but
using an async "fire and forget" call?
2. If I do go with the MSMQ solution, is there a better way than an external
service? Is there some way to use SQL Server's CLR to read from the queue? I
suspect this is possible, but I haven't found a good example of this
anywhere. Any suggestions?
3. Dan, you mentioned "if your log table is optimized for writes" - what did
you have in mind here?
thanks...Mike|||Hi Mike,
For your questions:
> 1. What do you think about using a direct insert into the log table, but
> using an async "fire and forget" call?
If there is no explicit performance issues, it is no problem to use a
direct insert into a log table. Actually this is the most common way in
normal systems. For your system, I think that async "fire and forget" may
work more efficient since your system has intensive logging activities.

> 2. If I do go with the MSMQ solution, is there a better way than an
external
> service? Is there some way to use SQL Server's CLR to read from the
queue? I
> suspect this is possible, but I haven't found a good example of this
> anywhere. Any suggestions?
As far as I know, it is not possible to use SQL Server CLR to read from the
queue since SQL Server CLR cannot reference non-SQL Server projects. Could
you please let me know why you want to use SQL Server CLR to read from the
queue?
Best regards,
Charles Wang
Microsoft Online Community Support
========================================
=============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============|||Hi Mike,
For your questions:
> 1. What do you think about using a direct insert into the log table, but
> using an async "fire and forget" call?
If there is no explicit performance issues, it is no problem to use a
direct insert into a log table. Actually this is the most common way in
normal systems. For your system, I think that async "fire and forget" may
work more efficient since your system has intensive logging activities.

> 2. If I do go with the MSMQ solution, is there a better way than an
external
> service? Is there some way to use SQL Server's CLR to read from the
queue? I
> suspect this is possible, but I haven't found a good example of this
> anywhere. Any suggestions?
As far as I know, it is not possible to use SQL Server CLR to read from the
queue since SQL Server CLR cannot reference non-SQL Server projects. Could
you please let me know why you want to use SQL Server CLR to read from the
queue?
Best regards,
Charles Wang
Microsoft Online Community Support
========================================
=============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============|||> 1. What do you think about using a direct insert into the log table, but
> using an async "fire and forget" call?
I think an asynch call is a good idea. I don't know about the "forget" part
though; I think you'd want to know if it succeeded ;-)

> 2. If I do go with the MSMQ solution, is there a better way than an
> external
> service? Is there some way to use SQL Server's CLR to read from the queue?
> I
> suspect this is possible, but I haven't found a good example of this
> anywhere. Any suggestions?
A quick Google search turned up a MSMQ example at
(http://www.codeproject.com/useritems/SqlMSMQ.asp) but I haven't looked at
it. Although it may be possible for the SQL CLR to host the app, I don't
see much value in doing so. The service method you mentioned is the most
elegant but you could easily invoke a command-line utility using a
continuously running SQL Agent job that launches at startup.

> 3. Dan, you mentioned "if your log table is optimized for writes" - what
> did
> you have in mind here?
Specifically, I meant a table that has only a clustered index with a
increasing key (e.g. IDENTITY column or log datetime). This will perform
very well regardless of table size. I've successfully used that approach
with tables containing billions of rows.
You might be able to get away with additional non-clustered indexes too
depending on the table size and your i/o subsystem. Once you reach a
certain threshold, consider partitioning the table by date. This will keep
the current day data working set small and mitigate the overhead of
maintaining those non-clustered indexes while improve manageability.
Without table partitioning, you still have the option of moving data via a
daily process into an appropriately indexed reporting table. This works
best when current data are infrequently accessed and most queries are done
against historical data. You can use a view (perhaps partitioned) to make
the implementation abstract.
Hope this helps.
Dan Guzman
SQL Server MVP
"Mike Kraley" <mkraley@.community.nospam> wrote in message
news:48AC1D2D-73DE-4934-AE4A-57EFAB36333F@.microsoft.com...
> Thanks to both Dan and Charles for your replies. A few followups:
> The issue I'm really worried about here is the backup of inserting many
> items into a potentially large table, ie. the log table. I don't want this
> overhead to be in series with the "real" transactions.
> 1. What do you think about using a direct insert into the log table, but
> using an async "fire and forget" call?
> 2. If I do go with the MSMQ solution, is there a better way than an
> external
> service? Is there some way to use SQL Server's CLR to read from the queue?
> I
> suspect this is possible, but I haven't found a good example of this
> anywhere. Any suggestions?
> 3. Dan, you mentioned "if your log table is optimized for writes" - what
> did
> you have in mind here?
> thanks...Mike|||Thanks again, both of you.
- why not use a service? Just because it is yet another "thing" that has to
be built, tested, deployed, maintained, operated, etc. If it can all be
magically included in the database, that seems a bit easier. Of course the
service is a workable solution if the DB can't do the job.
The SQL Agent task is another good idea.
The tips on table organization are also very helpful
...Mike|||MSMQ will give you decoupling and not much more.
Service Broker will give you integrated storage of messages and data (i.e.
you have only one product/database to backup/restore), integration with
database clustering and database mirroring. It also gives you activation of
T-SQL or CLR procedures to process the messages. It provides guaranteed EOIO
(Exactly Once In Order) semantics for your messages and reliable
communication shutdown and error (think TCP (broker) vs. UDP (msmq)),
decouples physical location from logical destination (databases containing
Service Broker queues can be moved to new hosts and continue the existing
messaging sessions)
For what you describe there are two usual patterns:
- one SQL Server all applications connect to and send a message (using T-SQL
SEND verb), which is usefull when the goal is to return quickly control to
the application and let the processing happen asynchronously. This gives
decoupling from processing, but not from availability, i.e. if the SQL
server is down the application cannot log
- Each application (Web server) has a local SQL Express instance to which it
connects and issues the SEND verb and lest the Express isntance handle the
delivery of the message to the central log. This gives decoupling both from
processing and availability. The SQL Express availability is usually same as
the Web server availability. The trouble of deploying a SQL Express instance
on each web host is about the same as deploying a msmq queue, since this is
not a full blown SQL Server instance that needs maintenance and
administration, once deployed it can pretty much go on auto-pilot mode.
Have a look at the slides at
[url]http://blogs.msdn.com/remusrusanu/archive/2007/04/03/orlando-slides-and-code.aspx[
/url]
HTH,
~ Remus
"Mike Kraley" <mkraley@.community.nospam> wrote in message
news:24174429-AD79-47DF-AE08-E2C2D62ACFF6@.microsoft.com...
>I have a large web application with several web servers and several sql
> servers. For debugging and usage tracking, the web applications write log
> messages to a common log database on one of the sql servers. This traffic
> can
> get quite extensive and, although desirable, it should not interfere with
> the
> mainline user processing if possible.
> In a previous project, I used an MSMQ to decouple the logging activity
> from
> the mainline processing. The web applications wrote their messages to the
> queue, which was a very fast operation, and the queue buffered the entry
> of
> the log data into the database queue. In this case, we wrote a custom
> service
> that read the queue and inserted the results into the database table.
> With all the new fancy features of SS 2005 and .NET 2.0, I wonder if there
> isn't a better way to do this. For example, would using Service Broker
> queues
> be an option? Although most of the examples I've seen talk about sending
> messages from one database to another, it looks like you can write C# code
> to
> send SSB messages from an external application - ie. the web app. Then
> we'd
> write an activation method on the queue that would simply write the log
> messages into the database.
> How would this work? Or is inserting someting into an SSB queue about the
> same as just inserting the log record into the table itself, from a
> performance point of view?
> Are SSB queues implemented with MSMQ? or something else?
> Or should I stick with MSMQ itself? Writing the application end (ie. the
> sending end) of the queue is easy; but is there a better way to handle the
> receiving end than building a whole service? For example, is there some
> way
> to use CLR integration - or something else - to directly read from the
> queue
> and insert rows into the log table?
> All suggestions gratefully accepted
> --
> ...Mike

Queueing log messages

I have a large web application with several web servers and several sql
servers. For debugging and usage tracking, the web applications write log
messages to a common log database on one of the sql servers. This traffic can
get quite extensive and, although desirable, it should not interfere with the
mainline user processing if possible.
In a previous project, I used an MSMQ to decouple the logging activity from
the mainline processing. The web applications wrote their messages to the
queue, which was a very fast operation, and the queue buffered the entry of
the log data into the database queue. In this case, we wrote a custom service
that read the queue and inserted the results into the database table.
With all the new fancy features of SS 2005 and .NET 2.0, I wonder if there
isn't a better way to do this. For example, would using Service Broker queues
be an option? Although most of the examples I've seen talk about sending
messages from one database to another, it looks like you can write C# code to
send SSB messages from an external application - ie. the web app. Then we'd
write an activation method on the queue that would simply write the log
messages into the database.
How would this work? Or is inserting someting into an SSB queue about the
same as just inserting the log record into the table itself, from a
performance point of view?
Are SSB queues implemented with MSMQ? or something else?
Or should I stick with MSMQ itself? Writing the application end (ie. the
sending end) of the queue is easy; but is there a better way to handle the
receiving end than building a whole service? For example, is there some way
to use CLR integration - or something else - to directly read from the queue
and insert rows into the log table?
All suggestions gratefully accepted
--
...Mike> How would this work? Or is inserting someting into an SSB queue about the
> same as just inserting the log record into the table itself, from a
> performance point of view?
I would expect that inserting directly into the log table would generally be
more efficient than a Service Broker queue or a transactional MSMQ for such
a simple task. The issue is that a synchronous physical write is required
to guarantee message delivery regardless of the technology. Service Broker,
MSMQ or a regular table insert must all wait for a write to complete before
returning to back to the client. Service Broker's sweet spot is more for
asynchronous processing and scale-out.
If you don't need guaranteed delivery, I think a non-transactional MSMQ
would probably provide the best response time. However, if your log table
is optimized for writes, I think it would be a close call between the
inserts and MSMQ. I suggest you run performance tests to determine the best
approach for your environment.
> Are SSB queues implemented with MSMQ? or something else?
Queues are schema-owned objects much like regular tables.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Mike Kraley" <mkraley@.community.nospam> wrote in message
news:24174429-AD79-47DF-AE08-E2C2D62ACFF6@.microsoft.com...
>I have a large web application with several web servers and several sql
> servers. For debugging and usage tracking, the web applications write log
> messages to a common log database on one of the sql servers. This traffic
> can
> get quite extensive and, although desirable, it should not interfere with
> the
> mainline user processing if possible.
> In a previous project, I used an MSMQ to decouple the logging activity
> from
> the mainline processing. The web applications wrote their messages to the
> queue, which was a very fast operation, and the queue buffered the entry
> of
> the log data into the database queue. In this case, we wrote a custom
> service
> that read the queue and inserted the results into the database table.
> With all the new fancy features of SS 2005 and .NET 2.0, I wonder if there
> isn't a better way to do this. For example, would using Service Broker
> queues
> be an option? Although most of the examples I've seen talk about sending
> messages from one database to another, it looks like you can write C# code
> to
> send SSB messages from an external application - ie. the web app. Then
> we'd
> write an activation method on the queue that would simply write the log
> messages into the database.
> How would this work? Or is inserting someting into an SSB queue about the
> same as just inserting the log record into the table itself, from a
> performance point of view?
> Are SSB queues implemented with MSMQ? or something else?
> Or should I stick with MSMQ itself? Writing the application end (ie. the
> sending end) of the queue is easy; but is there a better way to handle the
> receiving end than building a whole service? For example, is there some
> way
> to use CLR integration - or something else - to directly read from the
> queue
> and insert rows into the log table?
> All suggestions gratefully accepted
> --
> ...Mike|||Hi Mike,
Did you encounter any issue of using MSMQ? If MSMQ worked fine, I think
that it is no need to change your current implementation. SQL Server
Service Broker also provides a queue for messages; however it is not MSMQ.
The explicit difference between Service Broker and MSMQ is that for Service
Broker you can use T-SQL to send messages while for MSMQ you need to run
MSMQ API to send messages.
For more detailed information, y ou may refer to:
Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043.aspx
From your description, I saw that your MSMQ resolution seemed efficient and
worked fine now, so my recommendation here is just keeping it there until
it does not satisfy your requirements.
If you have any other questions or concerns, please feel free to let me
know. Have a good day!
Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||Thanks to both Dan and Charles for your replies. A few followups:
The issue I'm really worried about here is the backup of inserting many
items into a potentially large table, ie. the log table. I don't want this
overhead to be in series with the "real" transactions.
1. What do you think about using a direct insert into the log table, but
using an async "fire and forget" call?
2. If I do go with the MSMQ solution, is there a better way than an external
service? Is there some way to use SQL Server's CLR to read from the queue? I
suspect this is possible, but I haven't found a good example of this
anywhere. Any suggestions?
3. Dan, you mentioned "if your log table is optimized for writes" - what did
you have in mind here?
thanks...Mike|||Hi Mike,
For your questions:
> 1. What do you think about using a direct insert into the log table, but
> using an async "fire and forget" call?
If there is no explicit performance issues, it is no problem to use a
direct insert into a log table. Actually this is the most common way in
normal systems. For your system, I think that async "fire and forget" may
work more efficient since your system has intensive logging activities.
> 2. If I do go with the MSMQ solution, is there a better way than an
external
> service? Is there some way to use SQL Server's CLR to read from the
queue? I
> suspect this is possible, but I haven't found a good example of this
> anywhere. Any suggestions?
As far as I know, it is not possible to use SQL Server CLR to read from the
queue since SQL Server CLR cannot reference non-SQL Server projects. Could
you please let me know why you want to use SQL Server CLR to read from the
queue?
Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||Hi Mike,
For your questions:
> 1. What do you think about using a direct insert into the log table, but
> using an async "fire and forget" call?
If there is no explicit performance issues, it is no problem to use a
direct insert into a log table. Actually this is the most common way in
normal systems. For your system, I think that async "fire and forget" may
work more efficient since your system has intensive logging activities.
> 2. If I do go with the MSMQ solution, is there a better way than an
external
> service? Is there some way to use SQL Server's CLR to read from the
queue? I
> suspect this is possible, but I haven't found a good example of this
> anywhere. Any suggestions?
As far as I know, it is not possible to use SQL Server CLR to read from the
queue since SQL Server CLR cannot reference non-SQL Server projects. Could
you please let me know why you want to use SQL Server CLR to read from the
queue?
Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||> 1. What do you think about using a direct insert into the log table, but
> using an async "fire and forget" call?
I think an asynch call is a good idea. I don't know about the "forget" part
though; I think you'd want to know if it succeeded ;-)
> 2. If I do go with the MSMQ solution, is there a better way than an
> external
> service? Is there some way to use SQL Server's CLR to read from the queue?
> I
> suspect this is possible, but I haven't found a good example of this
> anywhere. Any suggestions?
A quick Google search turned up a MSMQ example at
(http://www.codeproject.com/useritems/SqlMSMQ.asp) but I haven't looked at
it. Although it may be possible for the SQL CLR to host the app, I don't
see much value in doing so. The service method you mentioned is the most
elegant but you could easily invoke a command-line utility using a
continuously running SQL Agent job that launches at startup.
> 3. Dan, you mentioned "if your log table is optimized for writes" - what
> did
> you have in mind here?
Specifically, I meant a table that has only a clustered index with a
increasing key (e.g. IDENTITY column or log datetime). This will perform
very well regardless of table size. I've successfully used that approach
with tables containing billions of rows.
You might be able to get away with additional non-clustered indexes too
depending on the table size and your i/o subsystem. Once you reach a
certain threshold, consider partitioning the table by date. This will keep
the current day data working set small and mitigate the overhead of
maintaining those non-clustered indexes while improve manageability.
Without table partitioning, you still have the option of moving data via a
daily process into an appropriately indexed reporting table. This works
best when current data are infrequently accessed and most queries are done
against historical data. You can use a view (perhaps partitioned) to make
the implementation abstract.
Hope this helps.
Dan Guzman
SQL Server MVP
"Mike Kraley" <mkraley@.community.nospam> wrote in message
news:48AC1D2D-73DE-4934-AE4A-57EFAB36333F@.microsoft.com...
> Thanks to both Dan and Charles for your replies. A few followups:
> The issue I'm really worried about here is the backup of inserting many
> items into a potentially large table, ie. the log table. I don't want this
> overhead to be in series with the "real" transactions.
> 1. What do you think about using a direct insert into the log table, but
> using an async "fire and forget" call?
> 2. If I do go with the MSMQ solution, is there a better way than an
> external
> service? Is there some way to use SQL Server's CLR to read from the queue?
> I
> suspect this is possible, but I haven't found a good example of this
> anywhere. Any suggestions?
> 3. Dan, you mentioned "if your log table is optimized for writes" - what
> did
> you have in mind here?
> thanks...Mike|||Thanks again, both of you.
- why not use a service? Just because it is yet another "thing" that has to
be built, tested, deployed, maintained, operated, etc. If it can all be
magically included in the database, that seems a bit easier. Of course the
service is a workable solution if the DB can't do the job.
The SQL Agent task is another good idea.
The tips on table organization are also very helpful
...Mike|||MSMQ will give you decoupling and not much more.
Service Broker will give you integrated storage of messages and data (i.e.
you have only one product/database to backup/restore), integration with
database clustering and database mirroring. It also gives you activation of
T-SQL or CLR procedures to process the messages. It provides guaranteed EOIO
(Exactly Once In Order) semantics for your messages and reliable
communication shutdown and error (think TCP (broker) vs. UDP (msmq)),
decouples physical location from logical destination (databases containing
Service Broker queues can be moved to new hosts and continue the existing
messaging sessions)
For what you describe there are two usual patterns:
- one SQL Server all applications connect to and send a message (using T-SQL
SEND verb), which is usefull when the goal is to return quickly control to
the application and let the processing happen asynchronously. This gives
decoupling from processing, but not from availability, i.e. if the SQL
server is down the application cannot log
- Each application (Web server) has a local SQL Express instance to which it
connects and issues the SEND verb and lest the Express isntance handle the
delivery of the message to the central log. This gives decoupling both from
processing and availability. The SQL Express availability is usually same as
the Web server availability. The trouble of deploying a SQL Express instance
on each web host is about the same as deploying a msmq queue, since this is
not a full blown SQL Server instance that needs maintenance and
administration, once deployed it can pretty much go on auto-pilot mode.
Have a look at the slides at
http://blogs.msdn.com/remusrusanu/archive/2007/04/03/orlando-slides-and-code.aspx
HTH,
~ Remus
"Mike Kraley" <mkraley@.community.nospam> wrote in message
news:24174429-AD79-47DF-AE08-E2C2D62ACFF6@.microsoft.com...
>I have a large web application with several web servers and several sql
> servers. For debugging and usage tracking, the web applications write log
> messages to a common log database on one of the sql servers. This traffic
> can
> get quite extensive and, although desirable, it should not interfere with
> the
> mainline user processing if possible.
> In a previous project, I used an MSMQ to decouple the logging activity
> from
> the mainline processing. The web applications wrote their messages to the
> queue, which was a very fast operation, and the queue buffered the entry
> of
> the log data into the database queue. In this case, we wrote a custom
> service
> that read the queue and inserted the results into the database table.
> With all the new fancy features of SS 2005 and .NET 2.0, I wonder if there
> isn't a better way to do this. For example, would using Service Broker
> queues
> be an option? Although most of the examples I've seen talk about sending
> messages from one database to another, it looks like you can write C# code
> to
> send SSB messages from an external application - ie. the web app. Then
> we'd
> write an activation method on the queue that would simply write the log
> messages into the database.
> How would this work? Or is inserting someting into an SSB queue about the
> same as just inserting the log record into the table itself, from a
> performance point of view?
> Are SSB queues implemented with MSMQ? or something else?
> Or should I stick with MSMQ itself? Writing the application end (ie. the
> sending end) of the queue is easy; but is there a better way to handle the
> receiving end than building a whole service? For example, is there some
> way
> to use CLR integration - or something else - to directly read from the
> queue
> and insert rows into the log table?
> All suggestions gratefully accepted
> --
> ...Mike