More on merge replication an FK constraints

So, let me relate to my earlier post in this group about merge replication
and foreign keys.
As I've mentioned, I have one publication that pushes 'customers' table to
the subscribers.
I have another publication that pushes 'invoices' and 'invoices_det' tables
to the subscribers. In table invoices there is cust_id field wich is foreign
key to the customers table primary key 'cust_id'. I have created all
publications like so that first table is DROPed and then recreated. I have
'checked' options for creating primary keys and referential integrity.
FIrst I push CUSTOMERS publication to the subsrciber, it's ok. customer
table is created on the subscriber, primary key is set, everything is ok.
Then I push INVOICES publication to the subscriber. That's not ok. I don't
have foreign key to customers table in the table invoices.
Enterprise manage wizzard suggests that I include customers table in
INVOICES publication to avoid things like mentioned above. But, I have
problem with that approach. That would mean that I'd have only as many
publications as I have subscribers (one subscriber - one publication). That
means that I'd have to have, for instance, table 'customers' published dozen
times, if I have dozen subscribers.
How do I solve this one?
As far as I see it, i could:
1) drop the foreign key constraints
- I'd hate to do this!
2) set up publication snapshot so it 'delete the data on the subscriber':
This seems ok, but! If I, for any reason, need to break replication,
when setting it back agan, I'll still have data on the subscriber. I then
push CUSTOMERS publication, the snapshot tries to DELETE data from
'customers' table, but, since it's FKeyd to 'invoices' table, it will bring
up an error.
3) set up publication snapshot so it 'leaves the existing table unchanged'.
This is the best bet I could have, as far as I see. I only need to
make sure that the database on the remote is 'fresh and clear'. Wich means,
do the script to do the DDL on the subscriber (with all the constraints!),
and then push the snapshot in 'right' order (meaning, first the customers,
then the invoices, in the above example).
Any help here would be much appreciated.
Mike
"I can do it quick. I can do it cheap. I can do it well. Pick any two."
Mario Splivalo
msplival@.jagor.srce.hr
For foreign keys can you not set them all to "Not for replication", combine
CUSTOMERS, INVOICES and INVOICES_DET in one publication?
Jim.
"Mario Splivalo" wrote:

> So, let me relate to my earlier post in this group about merge replication
> and foreign keys.
> As I've mentioned, I have one publication that pushes 'customers' table to
> the subscribers.
> I have another publication that pushes 'invoices' and 'invoices_det' tables
> to the subscribers. In table invoices there is cust_id field wich is foreign
> key to the customers table primary key 'cust_id'. I have created all
> publications like so that first table is DROPed and then recreated. I have
> 'checked' options for creating primary keys and referential integrity.
> FIrst I push CUSTOMERS publication to the subsrciber, it's ok. customer
> table is created on the subscriber, primary key is set, everything is ok.
> Then I push INVOICES publication to the subscriber. That's not ok. I don't
> have foreign key to customers table in the table invoices.
> Enterprise manage wizzard suggests that I include customers table in
> INVOICES publication to avoid things like mentioned above. But, I have
> problem with that approach. That would mean that I'd have only as many
> publications as I have subscribers (one subscriber - one publication). That
> means that I'd have to have, for instance, table 'customers' published dozen
> times, if I have dozen subscribers.
> How do I solve this one?
> As far as I see it, i could:
> 1) drop the foreign key constraints
> - I'd hate to do this!
> 2) set up publication snapshot so it 'delete the data on the subscriber':
> This seems ok, but! If I, for any reason, need to break replication,
> when setting it back agan, I'll still have data on the subscriber. I then
> push CUSTOMERS publication, the snapshot tries to DELETE data from
> 'customers' table, but, since it's FKeyd to 'invoices' table, it will bring
> up an error.
> 3) set up publication snapshot so it 'leaves the existing table unchanged'.
> This is the best bet I could have, as far as I see. I only need to
> make sure that the database on the remote is 'fresh and clear'. Wich means,
> do the script to do the DDL on the subscriber (with all the constraints!),
> and then push the snapshot in 'right' order (meaning, first the customers,
> then the invoices, in the above example).
> Any help here would be much appreciated.
> Mike
> --
> "I can do it quick. I can do it cheap. I can do it well. Pick any two."
> Mario Splivalo
> msplival@.jagor.srce.hr
>
|||On 2004-12-06, Jim Breffni <JimBreffni@.discussions.microsoft.com> wrote:
> For foreign keys can you not set them all to "Not for replication", combine
> CUSTOMERS, INVOICES and INVOICES_DET in one publication?
I use 'NOT FOR REPLICATION' for foreign keys.
Combining all those into one publication now seems like a good thing. But, I
have dozen of publication of wich 8 are non-filtered publications. Customers
is an example of such publication. I have 30 stores around the country, and
I need to be able for all the stores to enter new customers, and that new
customer should be visible in all locations. Invoices, for instance, is
exmaple of filtered publication (i have 4 like those), only the cetnral
database (the publisher) needs to see all the invoices (for the reporting
purposes, and financial stuff). Other subscribers don't see other invoices.
Same goes for inventory lists, some other financial stuff I don't know
english names, and so on.
So, your approach leaves me with ONE publication for each subscriber! I
don't like that approach, it just creates mess. I'd like if someone could
tell me now 'stop, you're wrong here'.
My database well normalized (is that the term when you reference data trough
foreign keys?), for instance, in invoices I have payement methods (cash,
credit card,...) wich is FK to the table wich holds payement methods. That
table is updated ONLY at the publisher. Another example is stocks. Each
invoice holds stock_id, wich is foreign key to table l_stocks. Then users.
Then tax rates. Then a lot of other things. All those tables are updated at
the publisher only, so I created snapshot replication to push that data to
subscribers. You suggest I put all that tables into merge publication, just
because they're referenced by FK from tables invoices and such? What is
wrong with my understanding of replication if I belive that is not the way?
Mike
"I can do it quick. I can do it cheap. I can do it well. Pick any two."
Mario Splivalo
msplival@.jagor.srce.hr
|||If you have a set of tables that rely on each other using foreign keys and
they are likely to be updated together then they should all be in the same
publication.
As I understand it, you have them separated and are getting foreign key
errors because items are not present when you need them.
You may have very good reasons for splitting them that I am not aware of but
I see no other solution to your problem.
Jim.
"Mario Splivalo" wrote:

> On 2004-12-06, Jim Breffni <JimBreffni@.discussions.microsoft.com> wrote:
> I use 'NOT FOR REPLICATION' for foreign keys.
> Combining all those into one publication now seems like a good thing. But, I
> have dozen of publication of wich 8 are non-filtered publications. Customers
> is an example of such publication. I have 30 stores around the country, and
> I need to be able for all the stores to enter new customers, and that new
> customer should be visible in all locations. Invoices, for instance, is
> exmaple of filtered publication (i have 4 like those), only the cetnral
> database (the publisher) needs to see all the invoices (for the reporting
> purposes, and financial stuff). Other subscribers don't see other invoices.
> Same goes for inventory lists, some other financial stuff I don't know
> english names, and so on.
> So, your approach leaves me with ONE publication for each subscriber! I
> don't like that approach, it just creates mess. I'd like if someone could
> tell me now 'stop, you're wrong here'.
> My database well normalized (is that the term when you reference data trough
> foreign keys?), for instance, in invoices I have payement methods (cash,
> credit card,...) wich is FK to the table wich holds payement methods. That
> table is updated ONLY at the publisher. Another example is stocks. Each
> invoice holds stock_id, wich is foreign key to table l_stocks. Then users.
> Then tax rates. Then a lot of other things. All those tables are updated at
> the publisher only, so I created snapshot replication to push that data to
> subscribers. You suggest I put all that tables into merge publication, just
> because they're referenced by FK from tables invoices and such? What is
> wrong with my understanding of replication if I belive that is not the way?
> Mike
> --
> "I can do it quick. I can do it cheap. I can do it well. Pick any two."
> Mario Splivalo
> msplival@.jagor.srce.hr
>
|||On 2004-12-07, Jim Breffni <JimBreffni@.discussions.microsoft.com> wrote:
> If you have a set of tables that rely on each other using foreign keys and
> they are likely to be updated together then they should all be in the same
> publication.
Yes. That goes for master/detail tables. But, what if I have a table with,
let's say, tax rates, and in invocie table I have reference via foreign key
to the tax rates table? I'm publishing tax rates table (along with others)
via snapshot publication. So, why would I include those in merge
publication? And tables in merge publication still reference those fields in
tax rates table. So, when deploying initial snapshot the invoices table
WON'T have FK constraint to tax rates table. How do I deal with that?

> As I understand it, you have them separated and are getting foreign key
> errors because items are not present when you need them.
Initial snapshot won't create foreign key constraints for tables that are
not in that publication. That is my problem.

> You may have very good reasons for splitting them that I am not aware of but
> I see no other solution to your problem.
Do you have any reference on the web where I could read more about this? I
see no point in puting all the database tables in one publication just
because almost all tables are FKey with each other. Or, to put it more
simple, how do I establish snapshot and merge publication so that tables in
merge publication have foreign keys to tables in snapshot publication?
Mike
"I can do it quick. I can do it cheap. I can do it well. Pick any two."
Mario Splivalo
msplival@.jagor.srce.hr
|||Yes, what you are doing is logical ok - I just don't know of a way to prevent
FK errors when you split things like that.
For me, even if the table rarely changes, I include everything in a
publication. The only price I pay for that is a larger initial snapshot.
The benefit I get is simplicity and easier administration.
|||On 2004-12-07, Jim Breffni <JimBreffni@.discussions.microsoft.com> wrote:
> Yes, what you are doing is logical ok - I just don't know of a way to prevent
> FK errors when you split things like that.
> For me, even if the table rarely changes, I include everything in a
> publication. The only price I pay for that is a larger initial snapshot.
> The benefit I get is simplicity and easier administration.
So, you'd generaly be ok with this: put rarely changed tables as articles
into many publications, although you could have only one snapshot
publication for those?
Mike
"I can do it quick. I can do it cheap. I can do it well. Pick any two."
Mario Splivalo
msplival@.jagor.srce.hr
|||I try to keep it one publication, one snapshot. I then have many subscribers
- it is very easy to administer.
"Mario Splivalo" wrote:

> On 2004-12-07, Jim Breffni <JimBreffni@.discussions.microsoft.com> wrote:
> So, you'd generaly be ok with this: put rarely changed tables as articles
> into many publications, although you could have only one snapshot
> publication for those?
> Mike
> --
> "I can do it quick. I can do it cheap. I can do it well. Pick any two."
> Mario Splivalo
> msplival@.jagor.srce.hr
>
|||On 2004-12-08, Jim Breffni <JimBreffni@.discussions.microsoft.com> wrote:
> I try to keep it one publication, one snapshot. I then have many subscribers
> - it is very easy to administer.
>
Thnx. I'll see with what i'll come up at the end, and post here...
Mike
"I can do it quick. I can do it cheap. I can do it well. Pick any two."
Mario Splivalo
msplival@.jagor.srce.hr
|||Mario,
I'm looking for the same answer as you, I have basically the same problem
with inicial snapshot with transactional replication, as you, I have also
Unchecked the enforce for replication in the relationship's dialog for the
foreign keys.
If I have some answers or more info I will post it here...
Adrian Parra.
"Mario Splivalo" wrote:

> On 2004-12-06, Jim Breffni <JimBreffni@.discussions.microsoft.com> wrote:
> I use 'NOT FOR REPLICATION' for foreign keys.
> Combining all those into one publication now seems like a good thing. But, I
> have dozen of publication of wich 8 are non-filtered publications. Customers
> is an example of such publication. I have 30 stores around the country, and
> I need to be able for all the stores to enter new customers, and that new
> customer should be visible in all locations. Invoices, for instance, is
> exmaple of filtered publication (i have 4 like those), only the cetnral
> database (the publisher) needs to see all the invoices (for the reporting
> purposes, and financial stuff). Other subscribers don't see other invoices.
> Same goes for inventory lists, some other financial stuff I don't know
> english names, and so on.
> So, your approach leaves me with ONE publication for each subscriber! I
> don't like that approach, it just creates mess. I'd like if someone could
> tell me now 'stop, you're wrong here'.
> My database well normalized (is that the term when you reference data trough
> foreign keys?), for instance, in invoices I have payement methods (cash,
> credit card,...) wich is FK to the table wich holds payement methods. That
> table is updated ONLY at the publisher. Another example is stocks. Each
> invoice holds stock_id, wich is foreign key to table l_stocks. Then users.
> Then tax rates. Then a lot of other things. All those tables are updated at
> the publisher only, so I created snapshot replication to push that data to
> subscribers. You suggest I put all that tables into merge publication, just
> because they're referenced by FK from tables invoices and such? What is
> wrong with my understanding of replication if I belive that is not the way?
> Mike
> --
> "I can do it quick. I can do it cheap. I can do it well. Pick any two."
> Mario Splivalo
> msplival@.jagor.srce.hr
>

Monsterlog file - short term solution?

Hi,
Ive inherited admin of a database of which I was a user, so I'm new to this.
The log file has grown to an enormous size. This has happened in the past
(only a couple of times in a year) and the db admin has fixed it. From
reading other posts I think I will set the recovery to SIMPLE (it is a data
warehouse). (However whatever scheme was in put in place by the admin
normally worked OK, even with recovery set to FULL, so I am not sure what has
changed.)
So, I still have the problem right now of the huge log file and a database
that will not respond. Can I fix the problem without backing up the log file
(to be honest the log file is of no use)? There is not enough space on disk
media to backup the log file. Is there a way of "fooling" SQL Server into
thinking it has been backed up?
Any help appreciated. And yes I will read up on BOL
Les R
Sounds like you have your recovery plan set to FULL. If you
truly do not need the contents of the log file then you can
truncate it
backup log <your db> with truncate_only
go
This will delete the inactive portion of the log
You can then issue a DBCC SHRINKFILE command
to reclaim disk space the log may have requested from
the operating system in the course of it's growth:
use <your db>
go
dbcc shrinkfile(<log name>, <size>)
go
Make sure you allocate enough initial space to the log
doesn't have to keep autogrowing (assuming it's set
to autogrow).
If you're using the FULL recovery model, also set it
to SIMPLE.
Before you do anything, make sure that you in fact
do not need the log info. And read up on the
backup log and dbcc shrinkfile before you run
them so you understand them.
"Les Russell" <LesRussell@.discussions.microsoft.com> wrote in message
news:453CEC6E-4E5F-4FDB-BE4E-20AE8CB91C16@.microsoft.com...
> Hi,
> Ive inherited admin of a database of which I was a user, so I'm new to
this.
> The log file has grown to an enormous size. This has happened in the past
> (only a couple of times in a year) and the db admin has fixed it. From
> reading other posts I think I will set the recovery to SIMPLE (it is a
data
> warehouse). (However whatever scheme was in put in place by the admin
> normally worked OK, even with recovery set to FULL, so I am not sure what
has
> changed.)
> So, I still have the problem right now of the huge log file and a database
> that will not respond. Can I fix the problem without backing up the log
file
> (to be honest the log file is of no use)? There is not enough space on
disk
> media to backup the log file. Is there a way of "fooling" SQL Server into
> thinking it has been backed up?
> Any help appreciated. And yes I will read up on BOL
> Les R

Monsterlog file - short term solution?

Hi,
Ive inherited admin of a database of which I was a user, so I'm new to this.
The log file has grown to an enormous size. This has happened in the past
(only a couple of times in a year) and the db admin has fixed it. From
reading other posts I think I will set the recovery to SIMPLE (it is a data
warehouse). (However whatever scheme was in put in place by the admin
normally worked OK, even with recovery set to FULL, so I am not sure what ha
s
changed.)
So, I still have the problem right now of the huge log file and a database
that will not respond. Can I fix the problem without backing up the log fil
e
(to be honest the log file is of no use)? There is not enough space on disk
media to backup the log file. Is there a way of "fooling" SQL Server into
thinking it has been backed up?
Any help appreciated. And yes I will read up on BOL
Les RSounds like you have your recovery plan set to FULL. If you
truly do not need the contents of the log file then you can
truncate it
backup log <your db> with truncate_only
go
This will delete the inactive portion of the log
You can then issue a DBCC SHRINKFILE command
to reclaim disk space the log may have requested from
the operating system in the course of it's growth:
use <your db>
go
dbcc shrinkfile(<log name>, <size> )
go
Make sure you allocate enough initial space to the log
doesn't have to keep autogrowing (assuming it's set
to autogrow).
If you're using the FULL recovery model, also set it
to SIMPLE.
Before you do anything, make sure that you in fact
do not need the log info. And read up on the
backup log and dbcc shrinkfile before you run
them so you understand them.
"Les Russell" <LesRussell@.discussions.microsoft.com> wrote in message
news:453CEC6E-4E5F-4FDB-BE4E-20AE8CB91C16@.microsoft.com...
> Hi,
> Ive inherited admin of a database of which I was a user, so I'm new to
this.
> The log file has grown to an enormous size. This has happened in the past
> (only a couple of times in a year) and the db admin has fixed it. From
> reading other posts I think I will set the recovery to SIMPLE (it is a
data
> warehouse). (However whatever scheme was in put in place by the admin
> normally worked OK, even with recovery set to FULL, so I am not sure what
has
> changed.)
> So, I still have the problem right now of the huge log file and a database
> that will not respond. Can I fix the problem without backing up the log
file
> (to be honest the log file is of no use)? There is not enough space on
disk
> media to backup the log file. Is there a way of "fooling" SQL Server into
> thinking it has been backed up?
> Any help appreciated. And yes I will read up on BOL
> Les Rsql

Monsterlog file - short term solution?

Hi,
Ive inherited admin of a database of which I was a user, so I'm new to this.
The log file has grown to an enormous size. This has happened in the past
(only a couple of times in a year) and the db admin has fixed it. From
reading other posts I think I will set the recovery to SIMPLE (it is a data
warehouse). (However whatever scheme was in put in place by the admin
normally worked OK, even with recovery set to FULL, so I am not sure what has
changed.)
So, I still have the problem right now of the huge log file and a database
that will not respond. Can I fix the problem without backing up the log file
(to be honest the log file is of no use)? There is not enough space on disk
media to backup the log file. Is there a way of "fooling" SQL Server into
thinking it has been backed up?
Any help appreciated. And yes I will read up on BOL :)
Les RSounds like you have your recovery plan set to FULL. If you
truly do not need the contents of the log file then you can
truncate it
backup log <your db> with truncate_only
go
This will delete the inactive portion of the log
You can then issue a DBCC SHRINKFILE command
to reclaim disk space the log may have requested from
the operating system in the course of it's growth:
use <your db>
go
dbcc shrinkfile(<log name>, <size>)
go
Make sure you allocate enough initial space to the log
doesn't have to keep autogrowing (assuming it's set
to autogrow).
If you're using the FULL recovery model, also set it
to SIMPLE.
Before you do anything, make sure that you in fact
do not need the log info. And read up on the
backup log and dbcc shrinkfile before you run
them so you understand them.
"Les Russell" <LesRussell@.discussions.microsoft.com> wrote in message
news:453CEC6E-4E5F-4FDB-BE4E-20AE8CB91C16@.microsoft.com...
> Hi,
> Ive inherited admin of a database of which I was a user, so I'm new to
this.
> The log file has grown to an enormous size. This has happened in the past
> (only a couple of times in a year) and the db admin has fixed it. From
> reading other posts I think I will set the recovery to SIMPLE (it is a
data
> warehouse). (However whatever scheme was in put in place by the admin
> normally worked OK, even with recovery set to FULL, so I am not sure what
has
> changed.)
> So, I still have the problem right now of the huge log file and a database
> that will not respond. Can I fix the problem without backing up the log
file
> (to be honest the log file is of no use)? There is not enough space on
disk
> media to backup the log file. Is there a way of "fooling" SQL Server into
> thinking it has been backed up?
> Any help appreciated. And yes I will read up on BOL :)
> Les R

monitoring sql server 2000 for problems

I've developed and am deploying a very important enterprise wide app for a
client. This app controls virtually everything the client does, and has
been thoroughly tested. However, I am a bit concerned about sql server
2000.
I noticed just the other day - still testing - that the server, when opened
in EM, said 'suspect'. I had been doing some fooling around and I know what
caused it. But my concern is, how can I monitor the server for this or
other failures to be able to react quickly - with a backup when necessary -
whenever something serious occurs? Is there a way, either inside vb .net or
by other means of continually polling the server for its health?
Thanks for any recommendations.
Regards,
Bernie Yaeger
Start a service application to continously run SELECT
DATABASEPROPERTYEX('pubs', 'Status') and check it's results?
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Try MiniSQLBackup
"Bernie Yaeger" <berniey@.cherwellinc.com> wrote in message
news:WaqAc.39196$0b.10585827@.news4.srv.hcvlny.cv.n et...
> I've developed and am deploying a very important enterprise wide app for a
> client. This app controls virtually everything the client does, and has
> been thoroughly tested. However, I am a bit concerned about sql server
> 2000.
> I noticed just the other day - still testing - that the server, when
opened
> in EM, said 'suspect'. I had been doing some fooling around and I know
what
> caused it. But my concern is, how can I monitor the server for this or
> other failures to be able to react quickly - with a backup when
necessary -
> whenever something serious occurs? Is there a way, either inside vb .net
or
> by other means of continually polling the server for its health?
> Thanks for any recommendations.
> Regards,
> Bernie Yaeger
>

Monitoring of Replication using dev tools

What are the methods by which I can monitor replication/data row changes
programmatically?
I have looked at the Mergexlib but perhaps I've missed something but that
*appears* to be for setting up and then monitoring (which is "ok" except how
do I monitor previously configured subscribers? I cant see how I can set my
mergeobj to an existing subscription as the DMO/MergeLib appear to be very
different...). Will RMO in SQL2005 solve this and if so how?
Is there something I can do with the notifications services when rows are
inserted/updated/deleted?
I'm looking at SQL2000 (and will be moving to SQL2005) and using the .net
environment and looking for an event based solution - I am not interested in
polling tables and running arcane sp's.
tia
You could modify the merge replication triggers to write to an audit table,
or simple use new triggers which will write to an audit table.
Another option is to query msmerge_contents and msmerge_tombstone.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Nigel" <nige66_spam_free@.hotmail.com> wrote in message
news:ecYkuTrYFHA.3132@.TK2MSFTNGP09.phx.gbl...
> What are the methods by which I can monitor replication/data row changes
> programmatically?
> I have looked at the Mergexlib but perhaps I've missed something but that
> *appears* to be for setting up and then monitoring (which is "ok" except
> how do I monitor previously configured subscribers? I cant see how I can
> set my mergeobj to an existing subscription as the DMO/MergeLib appear to
> be very different...). Will RMO in SQL2005 solve this and if so how?
> Is there something I can do with the notifications services when rows are
> inserted/updated/deleted?
> I'm looking at SQL2000 (and will be moving to SQL2005) and using the .net
> environment and looking for an event based solution - I am not interested
> in polling tables and running arcane sp's.
> tia
>
|||Thanks but I do not see how that meets the requirements as I *still*
have to poll tables (although they are now tables that I create rather than
using system tables in the case of msmerge_contents which is a no no).
I did look at triggers but the problem is *how* to get the trigger to fire
an event on an application outside of SQL. I know the CLR support in SQL2005
lets me use the SQLpipe to send data back upto the server, but how about
raising events on other applications?
(Note that I'm *not* interested in yuckky solutions with triggers writing
marker
files/event logs with .Net doing file or event log watching).
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:u6KR%23xRZFHA.1040@.TK2MSFTNGP10.phx.gbl...
> You could modify the merge replication triggers to write to an audit
> table, or simple use new triggers which will write to an audit table.
> Another option is to query msmerge_contents and msmerge_tombstone.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Nigel" <nige66_spam_free@.hotmail.com> wrote in message
> news:ecYkuTrYFHA.3132@.TK2MSFTNGP09.phx.gbl...
>
|||Nigel -
SQLNS provides a couple of functionalities that may do what you're
seeking.
First the built-in SQL Server Event Provider periodically checks a
table to see if there have been any changes to the rows. (Yes, it's
technically polling but you don't have to worry with the interval,
etc, SQLNS handles it for you.) Using T-SQL you as the developer
determine how those changes are to be recognized. The second way
involves using some SQLNS provided procedures in a trigger for the
data table.
Either way, events are raised in the SQLNS application and subscribers
can be notified via the built-in SMTP protocol or a custom protocol
that you develop (i.e. a protocol that would communicate some
application).
HTH...
Joe Webb
SQL Server MVP
~~~
Get up to speed quickly with SQLNS
http://www.amazon.com/exec/obidos/tg...l/-/0972688811
I support PASS, the Professional Association for SQL Server.
(www.sqlpass.org)
On Tue, 31 May 2005 16:42:34 +0100, "Nigel"
<nige66_spam_free@.hotmail.com> wrote:

>Thanks but I do not see how that meets the requirements as I *still*
>have to poll tables (although they are now tables that I create rather than
>using system tables in the case of msmerge_contents which is a no no).
>I did look at triggers but the problem is *how* to get the trigger to fire
>an event on an application outside of SQL. I know the CLR support in SQL2005
>lets me use the SQLpipe to send data back upto the server, but how about
>raising events on other applications?
>(Note that I'm *not* interested in yuckky solutions with triggers writing
>marker
>files/event logs with .Net doing file or event log watching).
>
>"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
>news:u6KR%23xRZFHA.1040@.TK2MSFTNGP10.phx.gbl...
>
|||Thanks Joe, I'm looking at your proposed method at the moment.
(In further digging I have come across custom resolvers using the
replrec.dll but seem to have hit a wall on that because theres so little
information about it, especially to develop for it in a .Net way. If anyone
has any experience good or bad......)
"Joe Webb" <joew@.webbtechsolutions.com> wrote in message
news:7suaa15nj9qa8bpgu4ooeok9riei1530r7@.4ax.com...
> Nigel -
> SQLNS provides a couple of functionalities that may do what you're
> seeking.
> First the built-in SQL Server Event Provider periodically checks a
> table to see if there have been any changes to the rows. (Yes, it's
> technically polling but you don't have to worry with the interval,
> etc, SQLNS handles it for you.) Using T-SQL you as the developer
> determine how those changes are to be recognized. The second way
> involves using some SQLNS provided procedures in a trigger for the
> data table.
> Either way, events are raised in the SQLNS application and subscribers
> can be notified via the built-in SMTP protocol or a custom protocol
> that you develop (i.e. a protocol that would communicate some
> application).
> HTH...
> --
> Joe Webb
> SQL Server MVP
>
> ~~~
> Get up to speed quickly with SQLNS
> http://www.amazon.com/exec/obidos/tg...l/-/0972688811
> I support PASS, the Professional Association for SQL Server.
> (www.sqlpass.org)
>
> On Tue, 31 May 2005 16:42:34 +0100, "Nigel"
> <nige66_spam_free@.hotmail.com> wrote:
>

Monitoring log usage

Hi Everyone,
I'mn setting up a proposal for a new hard drive for one of our SQL servers.
In an effort to get supporting data, I've defined performance counter logs for both %Disk and %Idle. Currently, %Idle is a minimum of 74.161 and a maximum of 100.081. After I get the new drive installed and move the logs over to that drive, I expect the
se percentages to change.
Question: Are there any other counter(s) I should be using to get more exact data that exposes the benefits of having the log files on a different hard drive?
Thanks in advance
Larry
You should always have the log on a separate physcial drive from the data
for 2 reasons ( irregardless of stats) if there is going to be anything more
than minimal use of the database.
1. separate random IO from Serial IO... Log is serial(mostly) and data files
are random (mostly).
2. If you need up to the minute recovery without loss of data, you'd better
have your log on a separate drive...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Larry" <Larry@.discussions.microsoft.com> wrote in message
news:8A16AAEB-D472-4037-90EE-1D9B9EB5A202@.microsoft.com...
> Hi Everyone,
> I'mn setting up a proposal for a new hard drive for one of our SQL
servers.
> In an effort to get supporting data, I've defined performance counter logs
for both %Disk and %Idle. Currently, %Idle is a minimum of 74.161 and a
maximum of 100.081. After I get the new drive installed and move the logs
over to that drive, I expect these percentages to change.
> Question: Are there any other counter(s) I should be using to get more
exact data that exposes the benefits of having the log files on a different
hard drive?
> Thanks in advance
> Larry

Monitoring Disk Consumption/DB Growth

Hello, I have a 125GB SQL DB whose growth I need to monitor. I've been
manually checking it each day and comparing growth of the .bak file from my
DB Maint. Plan. Is there a better way to measure daily growth of the DB.
Also, I have 4 other drives with non-DB image files that are added daily. Is
there an automated way or Administrative Tool to monitor the decline of free
disk space on 4 logical drives? Thanks, Pancho
You can check out DataStudio from AgileInfoSoftware at
http://www.agileinfollc.com, under the performance tab it has database
performance monitoring facility.
"Pancho" <Pancho@.discussions.microsoft.com> wrote in message
news:A3A8C7B3-382D-467E-A8F0-DE80AF1FFF79@.microsoft.com...
> Hello, I have a 125GB SQL DB whose growth I need to monitor. I've been
> manually checking it each day and comparing growth of the .bak file from
> my
> DB Maint. Plan. Is there a better way to measure daily growth of the DB.
> Also, I have 4 other drives with non-DB image files that are added daily.
> Is
> there an automated way or Administrative Tool to monitor the decline of
> free
> disk space on 4 logical drives? Thanks, Pancho
|||Hi
There is a script on
http://www.sqlservercentral.com/Scri...0&categoryid=1
"SQL Server Database File Sizes" which may do what you require.
John
"Pancho" wrote:

> Hello, I have a 125GB SQL DB whose growth I need to monitor. I've been
> manually checking it each day and comparing growth of the .bak file from my
> DB Maint. Plan. Is there a better way to measure daily growth of the DB.
> Also, I have 4 other drives with non-DB image files that are added daily. Is
> there an automated way or Administrative Tool to monitor the decline of free
> disk space on 4 logical drives? Thanks, Pancho
|||Check out AgileInfoSoftware DataStudio for space monitoring.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:25900858-65EE-41B6-9308-761E29428555@.microsoft.com...[vbcol=seagreen]
> Hi
> There is a script on
> http://www.sqlservercentral.com/Scri...0&categoryid=1
> "SQL Server Database File Sizes" which may do what you require.
> John
> "Pancho" wrote:

Monitor Data Access Speeds & Time-Outs

I've been asked to produce charts that show data access & time-out
information for an instance of SQL Server.
I've very little experience with SQL Server.
My background is MI - so only know Analysis Services part of SQL
Server.
It would seem the previous person manually went through error logs
generated & rekeyed appropriate data into Excel to produce charts.
How can I produce the following charts automatically?
1. Data Access - ave. access time per process by day
2. SQL Errors - number of by day
3. Time-outs - number by hour
Or anyway of achieving effectively the same thing without any manual
involvement.
Many thanks.
We probably need more information on what you are trying to collect but here
is a start.
Setup a SQL trace. It will need to run 24hours a day writing to a file(s).
Have it stop and write out every so often 5 mins, 4 hours (depends on system
load and requirements). Start the next trace. Read the trace into a table.
Delete old trace files.
1. Data Access - Assuming you mean how long did each query take to be
serviced. Add RPC:Completed and SQL:Batch Completeed to trace events.
Select the avg(duration) of these events over the time period you need.
2. SQL Errors - Add the specific SQL errors you are looking for in the
trace. Select the cout of them over the time period.
3. Timeouts. Not sure where you are getting timeout information now.
There's lock timeout, remote query time, or connection timeout in SQL
Agent...Need more info.
<duvinrouge@.servihoo.com> wrote in message
news:1129629233.565048.215040@.g44g2000cwa.googlegr oups.com...
> I've been asked to produce charts that show data access & time-out
> information for an instance of SQL Server.
> I've very little experience with SQL Server.
> My background is MI - so only know Analysis Services part of SQL
> Server.
> It would seem the previous person manually went through error logs
> generated & rekeyed appropriate data into Excel to produce charts.
> How can I produce the following charts automatically?
> 1. Data Access - ave. access time per process by day
> 2. SQL Errors - number of by day
> 3. Time-outs - number by hour
> Or anyway of achieving effectively the same thing without any manual
> involvement.
> Many thanks.
>