Agents and thread limits from SQL 2005 Distributor machine

Hi DBA's
We have one machime with 4 core processors and 2 GB of RAM. This machine have
3 sql 2005 instances and It's responsable form many replications. More of
them, have pull distribution agent, so we can controll these number over
project growing, but one instance don't.
All distribution agents were configured like "PUSH" and I don't intend to
use "PULL" with these because probably we'll affect subscribers with many
jobs (and thread distribution agents), that the same way, more difficult to
administrate.
How do we see these limits? If so, we can scale to another instance and
controll them!
Plese, if anyone has any article or "better pratical", we appreciate!
Numbers is the focus. We need numbers resgiters like x distrib.exe y logread.
exe and thus for ahead.
Thanks!
Krisnamourt
Message posted via http://www.droptable.com
select logreader=sum(case when program_name like '%logreader%'then 1 else 0
end),distrib=sum(case when program_name like '%'+rtrim(@.@.servername)+'%-%'
then 1 else 0 end) from sys.sysprocesses
http://www.zetainteractive.com - Shift Happens!
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
"Krisnamourt via droptable.com" <u21487@.uwe> wrote in message
news:7bfea23d5f150@.uwe...
> Hi DBA's
> We have one machime with 4 core processors and 2 GB of RAM. This machine
> have
> 3 sql 2005 instances and It's responsable form many replications. More of
> them, have pull distribution agent, so we can controll these number over
> project growing, but one instance don't.
> All distribution agents were configured like "PUSH" and I don't intend to
> use "PULL" with these because probably we'll affect subscribers with many
> jobs (and thread distribution agents), that the same way, more difficult
> to
> administrate.
> How do we see these limits? If so, we can scale to another instance and
> controll them!
> Plese, if anyone has any article or "better pratical", we appreciate!
> Numbers is the focus. We need numbers resgiters like x distrib.exe y
> logread.
> exe and thus for ahead.
> Thanks!
> Krisnamourt
> --
> Message posted via http://www.droptable.com
>
|||Thanks Hilary,
This article below show us some limitation from sql 2000 about log reader
threads. I did some research and didn't find any limitation about sql 2005.
So, is it true 100 log readers or what is the limitation of this agent?
http://support.microsoft.com/kb/306457/en-us
Hilary Cotter wrote:[vbcol=seagreen]
>select logreader=sum(case when program_name like '%logreader%'then 1 else 0
>end),distrib=sum(case when program_name like '%'+rtrim(@.@.servername)+'%-%'
>then 1 else 0 end) from sys.sysprocesses
>[quoted text clipped - 22 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server-replication/200712/1
|||I am unaware of any such limitation for Standard and Enterprise Editions.
Note that in the SQL 2000 article it refers to the number of simultaneous
agents which could be simultaneously running, its not necessarily a hard
limit as to the number of log reader agents/distribution agents that can be
on your system.
http://www.zetainteractive.com - Shift Happens!
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
"Krisnamourt via droptable.com" <u21487@.uwe> wrote in message
news:7c2e263b3461b@.uwe...
> Thanks Hilary,
> This article below show us some limitation from sql 2000 about log reader
> threads. I did some research and didn't find any limitation about sql
> 2005.
> So, is it true 100 log readers or what is the limitation of this agent?
> http://support.microsoft.com/kb/306457/en-us
> Hilary Cotter wrote:
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums.aspx/sql-server-replication/200712/1
>

Agent AutoStart in Win98SE

With MSDE 2000 installs, I have tried a couple of things to get the Agent to autostart when the machine is booted. So far, I've been unsuccessful. Does anyone have experience with a working solution?

I have tried:

1. sp_set_sqlagent_properties @.auto_start = 1
2. Placing the following in the Registry: "scm.exe -Action 1 -Service SQLServerAgent -SvcStartType 2 -Silent 1" under HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\Curr entVersion\Run

The checkbox in the SQL Server Service manager to "Auto-start service when OS starts" is greyed out for the Agent service. Does this mean that it just cannot be done?

-LoyalOriginally posted by loyalp
With MSDE 2000 installs, I have tried a couple of things to get the Agent to autostart when the machine is booted. So far, I've been unsuccessful. Does anyone have experience with a working solution?

I have tried:

1. sp_set_sqlagent_properties @.auto_start = 1
2. Placing the following in the Registry: "scm.exe -Action 1 -Service SQLServerAgent -SvcStartType 2 -Silent 1" under HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\Curr entVersion\Run

The checkbox in the SQL Server Service manager to "Auto-start service when OS starts" is greyed out for the Agent service. Does this mean that it just cannot be done?

-Loyal

Here is what Microsoft has to say about it.

Windows 98 Limitations
Windows 98 does not provide support for the following features:

Windows NT authentication
Fiber mode scheduling
Asynchronous I/O
Read ahead
Performance Monitor counters
Scatter/Gather I/O
Named pipes server network library
Appletalk server or client network library
Banyan Vines server network library

Read more: http://www.microsoft.com/sql/techinfo/development/2000/MSDE2000.asp

HTH!|||Thanks Krusty.

Just this morning I tried adding an EXEC statement to Win98 machines at startup. This statement uses xp_servicecontrol to START the SQLServerAgent and appears to have successfully worked around the problem.

[QUOTE][SIZE=1]Originally posted by KrustyDeKlown
Here is what Microsoft has to say about it.

Again: eMail subscription

I got a clue what might be wrong on my server machines which do not allow
eMail subscription due to a "configuration error"
I have an XP machine here with a local SQL server and a local RS client and
server. If I look at the property page of a given report there is always
something like that (I have to translate it from german, so maybe it is a
bit different in english versions):
changed at: (datetime)
changed by: SPONGEBOB\roland
created at: (datetime)
created by: SPONGEBOB\roland
size: 8kb
SPONGEBOB is the name of the machine; roland the user account.
Whereas my server machines show up the following:
changed at: (datetime)
changed by: TACCO\Administrator (unknown)
created at: (datetime)
created by: TACCO\Administrator (unknown)
size: 8kb
TACCO is the server name; administrator the user account. My concern is the
"unknown", what exactly causes the problem with eMail subscription. What
does cause RS to print "unknown" there and how to come over it?
TIA
rolandHallo Roland,
ich hatte ein ähnliches Problem, nachdem ich das Konto unter dem der Dienst läuft geändert hatte, ich mu�te es in den Orginalzustand zurücksetzen dann gingen auch wieder die eMail Abbonements.
Hi Roland,
I had a similar problem, but after resetting the account of the servce back to the one I've used while installing, the email works!
hth
sequal
"Roland" wrote:
> I got a clue what might be wrong on my server machines which do not allow
> eMail subscription due to a "configuration error"
> I have an XP machine here with a local SQL server and a local RS client and
> server. If I look at the property page of a given report there is always
> something like that (I have to translate it from german, so maybe it is a
> bit different in english versions):
> changed at: (datetime)
> changed by: SPONGEBOB\roland
> created at: (datetime)
> created by: SPONGEBOB\roland
> size: 8kb
> SPONGEBOB is the name of the machine; roland the user account.
> Whereas my server machines show up the following:
> changed at: (datetime)
> changed by: TACCO\Administrator (unknown)
> created at: (datetime)
> created by: TACCO\Administrator (unknown)
> size: 8kb
> TACCO is the server name; administrator the user account. My concern is the
> "unknown", what exactly causes the problem with eMail subscription. What
> does cause RS to print "unknown" there and how to come over it?
> TIA
> roland
>
>|||Me again, let it run under: "NT AUTHORITY\NetworkService"
sequal
"Roland" wrote:
> I got a clue what might be wrong on my server machines which do not allow
> eMail subscription due to a "configuration error"
> I have an XP machine here with a local SQL server and a local RS client and
> server. If I look at the property page of a given report there is always
> something like that (I have to translate it from german, so maybe it is a
> bit different in english versions):
> changed at: (datetime)
> changed by: SPONGEBOB\roland
> created at: (datetime)
> created by: SPONGEBOB\roland
> size: 8kb
> SPONGEBOB is the name of the machine; roland the user account.
> Whereas my server machines show up the following:
> changed at: (datetime)
> changed by: TACCO\Administrator (unknown)
> created at: (datetime)
> created by: TACCO\Administrator (unknown)
> size: 8kb
> TACCO is the server name; administrator the user account. My concern is the
> "unknown", what exactly causes the problem with eMail subscription. What
> does cause RS to print "unknown" there and how to come over it?
> TIA
> roland
>
>|||Hallo sequal,
wir haben es sowohl unter System als auch unter NetworkServices installiert.
Leider hat bei uns zumindest nichts geholfen. Unsere Server gehören zu
keiner Domain, ist das bei Euch anders?
we tried to install RS under "NTAUTHORITY\System" as well as NetworkService.
Nothing did help. Our servers don't belong to a domain, is that different at
your side?
r.|||Hallo Roland,
checken Sie doch bitte mal Ihre LogFiles, \Programme\Microsoft SQL Server\MSSQL\Reporting Services\LogFiles. Ich hatte exact die selbe Fehlemeldung nachdem ich den User unter dem die RS laufen sollten geändert habe, die Daten in der RepServ DB werden verschlüsselt, und im Logfile finden Sie zur geplanten Laufzeit der e-Mailsubscribtion eine entsprechende Fehlermeldung.
Unsere System läuft in einem ActivDirectory (Domäne) ich hatte alles in einer Testumgebung aufgesetzt (keine Domäne) und dann ins Echtnetzwerk übertragen, als ich die User unter welchen solche Dienste bei uns in der Firma laufen nachgepflegt habe ging plötzlich die e-Mail Subs. nicht mehr, aber alles andere lief.
Grüsse s.
P.S.: Anbei ein Auszug aus meinem Logfile welches mir den HinweiÃ? gab:
ReportingServicesService!crypto!d18!25.05.2004-16:08:20:: i INFO: Initializing crypto as user: NT-AUTORITÃ?T\SYSTEM
ReportingServicesService!crypto!d18!25.05.2004-16:08:20:: i INFO: Exporting public key
ReportingServicesService!crypto!d18!25.05.2004-16:08:20:: i INFO: Performing sku validation
ReportingServicesService!crypto!d18!25.05.2004-16:08:20:: i INFO: Importing existing encryption key
ReportingServicesService!library!d18!25.05.2004-16:08:20:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerDisabledException: Der Berichtsserver kann den symmetrischen Schlüssel nicht entschlüsseln, der zum Zugreifen auf geschützte oder verschlüsselte Daten in einer Berichtsserver-Datenbank verwendet wird. Sie müssen entweder einen Sicherungsschlüssel wiederherstellen oder den gesamten verschlüsselten Inhalt löschen und den Dienst dann neu starten. Weitere Informationen finden Sie in der Dokumentation., ;
Info: Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerDisabledException: Der Berichtsserver kann den symmetrischen Schlüssel nicht entschlüsseln, der zum Zugreifen auf geschützte oder verschlüsselte Daten in einer Berichtsserver-Datenbank verwendet wird. Sie müssen entweder einen Sicherungsschlüssel wiederherstellen oder den gesamten verschlüsselten Inhalt löschen und den Dienst dann neu starten. Weitere Informationen finden Sie in der Dokumentation. --> System.Runtime.InteropServices.COMException (0x80090005): Ungültige Daten
"Roland" wrote:
> Hallo sequal,
> wir haben es sowohl unter System als auch unter NetworkServices installiert.
> Leider hat bei uns zumindest nichts geholfen. Unsere Server gehören zu
> keiner Domain, ist das bei Euch anders?
> we tried to install RS under "NTAUTHORITY\System" as well as NetworkService.
> Nothing did help. Our servers don't belong to a domain, is that different at
> your side?
> r.
>
>|||Sequal,
Danke für Deine/Ihre Bemühungen.
In
news:ucIk$9uVEHA.1152@.TK2MSFTNGP09.phx.gbl
habe ich mal die Inhalte der Logdateien dargestellt. Irgendwie matchen die
nicht Deine/Ihre Logs :)
Grüsse und Vielen Dank
Roland
"sequal" <sequal@.discussions.microsoft.com> schrieb im Newsbeitrag
news:67196D21-20B2-4B62-A09A-32E9F7DEFEA7@.microsoft.com...
> Hallo Roland,
> checken Sie doch bitte mal Ihre LogFiles, \Programme\Microsoft SQL
Server\MSSQL\Reporting Services\LogFiles. Ich hatte exact die selbe
Fehlemeldung nachdem ich den User unter dem die RS laufen sollten geändert
habe, die Daten in der RepServ DB werden verschlüsselt, und im Logfile
finden Sie zur geplanten Laufzeit der e-Mailsubscribtion eine entsprechende
Fehlermeldung.
> Unsere System läuft in einem ActivDirectory (Domäne) ich hatte alles in
einer Testumgebung aufgesetzt (keine Domäne) und dann ins Echtnetzwerk
übertragen, als ich die User unter welchen solche Dienste bei uns in der
Firma laufen nachgepflegt habe ging plötzlich die e-Mail Subs. nicht mehr,
aber alles andere lief.
> Grüsse s.
> P.S.: Anbei ein Auszug aus meinem Logfile welches mir den Hinweiß gab:
> ReportingServicesService!crypto!d18!25.05.2004-16:08:20:: i INFO:
Initializing crypto as user: NT-AUTORITÄT\SYSTEM
> ReportingServicesService!crypto!d18!25.05.2004-16:08:20:: i INFO:
Exporting public key
> ReportingServicesService!crypto!d18!25.05.2004-16:08:20:: i INFO:
Performing sku validation
> ReportingServicesService!crypto!d18!25.05.2004-16:08:20:: i INFO:
Importing existing encryption key
> ReportingServicesService!library!d18!25.05.2004-16:08:20:: e ERROR:
Throwing
Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerDisabledExcept
ion: Der Berichtsserver kann den symmetrischen Schlüssel nicht
entschlüsseln, der zum Zugreifen auf geschützte oder verschlüsselte Daten in
einer Berichtsserver-Datenbank verwendet wird. Sie müssen entweder einen
Sicherungsschlüssel wiederherstellen oder den gesamten verschlüsselten
Inhalt löschen und den Dienst dann neu starten. Weitere Informationen finden
Sie in der Dokumentation., ;
> Info:
Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerDisabledExcept
ion: Der Berichtsserver kann den symmetrischen Schlüssel nicht
entschlüsseln, der zum Zugreifen auf geschützte oder verschlüsselte Daten in
einer Berichtsserver-Datenbank verwendet wird. Sie müssen entweder einen
Sicherungsschlüssel wiederherstellen oder den gesamten verschlüsselten
Inhalt löschen und den Dienst dann neu starten. Weitere Informationen finden
Sie in der Dokumentation. --> System.Runtime.InteropServices.COMException
(0x80090005): Ungültige Daten
>
> "Roland" wrote:
> > Hallo sequal,
> >
> > wir haben es sowohl unter System als auch unter NetworkServices
installiert.
> > Leider hat bei uns zumindest nichts geholfen. Unsere Server gehören zu
> > keiner Domain, ist das bei Euch anders?
> >
> > we tried to install RS under "NTAUTHORITY\System" as well as
NetworkService.
> > Nothing did help. Our servers don't belong to a domain, is that
different at
> > your side?
> >
> > r.
> >
> >
> >

After Full SQL Server 2005 Install SSIS is still not a service on the machine?

I have done a full Sql Server install on a server and the SSIS service is not available. Normally when I load Sql Server I get “SQL Server Integration Services" as a service that I can start. On this machine it is not listed after the install. I don't get any errors through out the install.

Please Help!

Steve

Setup forum may have more helpful for this issue:
http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=95&SiteID=1

After changing the comupter's name

I installed SQL Server 2005 in a windows xp machine. The ogrinal name of the machine is pc1.
After installing SqlServer 2005, I change the machine name to pc2.

I find that many login names in the Manager Studio is still prefixed with pc1, like pc1/ASPNET.

Must I change the prefix to pc2 manually?Yes, you will need to update the login names.

I discussed this issue and a possible solution on my blog at:

http://blogs.msdn.com/lcris/archive/2005/09/27/474505.aspx

Let me know if you need additional help.

Thanks
Laurentiu

After change the computer name.

I installed SQL Server 2005 in a windows xp machine. The ogrinal name of the
machine is pc1.
After installing SqlServer 2005, I change the machine name to pc2.
I find that many login names in the Manager Studio is still prefixed with
pc1, like pc1/ASPNET.
Must I change the prefix to pc2 manually?
Hello, ad
See: http://msdn2.microsoft.com/en-us/library/ms143799.aspx
Razvan

After change the computer name.

I installed SQL Server 2005 in a windows xp machine. The ogrinal name of the
machine is pc1.
After installing SqlServer 2005, I change the machine name to pc2.
I find that many login names in the Manager Studio is still prefixed with
pc1, like pc1/ASPNET.
Must I change the prefix to pc2 manually?Hello, ad
See: http://msdn2.microsoft.com/en-us/library/ms143799.aspx
Razvan

After change the computer name.

I installed SQL Server 2005 in a Windows XP machine. The ogrinal name of the
machine is pc1.
After installing SqlServer 2005, I change the machine name to pc2.
I find that many login names in the Manager Studio is still prefixed with
pc1, like pc1/ASPNET.
Must I change the prefix to pc2 manually?Hello, ad
See: http://msdn2.microsoft.com/en-us/library/ms143799.aspx
Razvan

affinity mask

Hi,
We want to test the setting of affinity mask on out
queries. we have 2 xeon processor machine so it looks like
4 processors....i want to use both the physical
processors and not the logical processor so we are setting
the value of affinity mask to 3. But how can we make sure
that it is using 2 physical processor and not 2 logical
processors of same physical processor...where can we see
this in windows task manager or performance monitor(i
didn't find any option that differentiate between logical
and physical processors)
Thanks
--Harvinderi think you are probably safer disabling HT for now,
some queries may not run well when using two separate
threads on one physical processor, other may run better on
one than two.
i have seen performance problems with index rebuilds with
HT. Until there is a comprehensive analysis of what runs
well in HT and what does not, you are probably better off
not using this feature
>--Original Message--
>Hi,
>We want to test the setting of affinity mask on out
>queries. we have 2 xeon processor machine so it looks
like
>4 processors....i want to use both the physical
>processors and not the logical processor so we are
setting
>the value of affinity mask to 3. But how can we make sure
>that it is using 2 physical processor and not 2 logical
>processors of same physical processor...where can we see
>this in windows task manager or performance monitor(i
>didn't find any option that differentiate between logical
>and physical processors)
>Thanks
>--Harvinder
>.
>|||Assuming the BIOS is written to Intels specifications, it will number the
processors starting with the first logical processor on each physical
processor, so in your case 0,1
Have a look at
http://www.microsoft.com/windows2000/docs/hyperthreading.doc
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"harvinder" <hs@.metratech.com> wrote in message
news:0bd201c3633b$8a754f70$a501280a@.phx.gbl...
Hi,
We want to test the setting of affinity mask on out
queries. we have 2 xeon processor machine so it looks like
4 processors....i want to use both the physical
processors and not the logical processor so we are setting
the value of affinity mask to 3. But how can we make sure
that it is using 2 physical processor and not 2 logical
processors of same physical processor...where can we see
this in windows task manager or performance monitor(i
didn't find any option that differentiate between logical
and physical processors)
Thanks
--Harvinder|||Thanks Jasper..Did the value of 3 looks good to u for
using only both physical processors that are logical 0,1
Thanks
--Harvinder
>--Original Message--
>Assuming the BIOS is written to Intels specifications, it
will number the
>processors starting with the first logical processor on
each physical
>processor, so in your case 0,1
>Have a look at
>http://www.microsoft.com/windows2000/docs/hyperthreading.d
oc
>--
>HTH
>Jasper Smith (SQL Server MVP)
>I support PASS - the definitive, global
>community for SQL Server professionals -
>http://www.sqlpass.org
>"harvinder" <hs@.metratech.com> wrote in message
>news:0bd201c3633b$8a754f70$a501280a@.phx.gbl...
>Hi,
>We want to test the setting of affinity mask on out
>queries. we have 2 xeon processor machine so it looks like
>4 processors....i want to use both the physical
>processors and not the logical processor so we are setting
>the value of affinity mask to 3. But how can we make sure
>that it is using 2 physical processor and not 2 logical
>processors of same physical processor...where can we see
>this in windows task manager or performance monitor(i
>didn't find any option that differentiate between logical
>and physical processors)
>Thanks
>--Harvinder
>
>.
>|||Yeah that's right
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"harvinder" <hs@.metratech.com> wrote in message
news:011801c3635e$7183bb00$a601280a@.phx.gbl...
Thanks Jasper..Did the value of 3 looks good to u for
using only both physical processors that are logical 0,1
Thanks
--Harvinder
>--Original Message--
>Assuming the BIOS is written to Intels specifications, it
will number the
>processors starting with the first logical processor on
each physical
>processor, so in your case 0,1
>Have a look at
>http://www.microsoft.com/windows2000/docs/hyperthreading.d
oc
>--
>HTH
>Jasper Smith (SQL Server MVP)
>I support PASS - the definitive, global
>community for SQL Server professionals -
>http://www.sqlpass.org
>"harvinder" <hs@.metratech.com> wrote in message
>news:0bd201c3633b$8a754f70$a501280a@.phx.gbl...
>Hi,
>We want to test the setting of affinity mask on out
>queries. we have 2 xeon processor machine so it looks like
>4 processors....i want to use both the physical
>processors and not the logical processor so we are setting
>the value of affinity mask to 3. But how can we make sure
>that it is using 2 physical processor and not 2 logical
>processors of same physical processor...where can we see
>this in windows task manager or performance monitor(i
>didn't find any option that differentiate between logical
>and physical processors)
>Thanks
>--Harvinder
>
>.
>