Loging perfmon to SQL

Hi Guys,
I have tried the standard MS lists for this question and they didnt know
that it was possible. So this might be a DBA type question.
I am looking to have 50 ish servers loging through perfmon to a sql DB. It
seams that you can do this under counter logs then logs you can select a
system DSN. Seams easy enough but I cant find any info/script to create the
DB required for the logging to work. Thought that MS may have been smart
enough if I just pointed it at a blank DB but know such luck.
If you have any ideas that would be great.
Thanks in advance
CliffHi Cliff.
It actually is smart enough to just point at a blank db - it will create the
schema itself as long as you can get it to connect. The trick is to get it
connected at all usually.
What is actually going wrong? Have you checked the Event Log via the Event
Viewer? If the problem is a login issue, you probably need to add the
Windows account group that the "Performance Logs & Alerts" service is
running under as a trusted login under SQL Security / Logins.
Follow these steps:
(a) Open the Services applet, under: Start / Control Panel / Services /
Administrative Tools / Services
(b) Double click on the "Performance Logs & Alerts" service
(c) Click on the "Log On" tab
(d) Identify the account / group (or LocalSystem) that the "Performance Logs
& Alerts" service is logging on as
(e) Open SQL Enterprise Manager
(f) Click on Security
(g) Right Click on Logins - select "Add New" Menu
(h) Use the name ([Domain]\[Account / Group name]) identified in step (d)
(i) Assign whatever permissions it needs to access the database named in
your DSN.
(j) Configure the DSN as a System DSN & ensure that the DSN uses Windows NT
Authentication to Log On.
Then start your trace & you should see the tables created as needed by
PerfMon.
HTH
Regards,
Greg Linwood
SQL Server MVP
"Cliff" <cliff.bree@.bsl.com.au> wrote in message
news:eoxLfvbVDHA.2368@.TK2MSFTNGP09.phx.gbl...
> Hi Guys,
> I have tried the standard MS lists for this question and they didnt know
> that it was possible. So this might be a DBA type question.
> I am looking to have 50 ish servers loging through perfmon to a sql DB.
It
> seams that you can do this under counter logs then logs you can select a
> system DSN. Seams easy enough but I cant find any info/script to create
the
> DB required for the logging to work. Thought that MS may have been smart
> enough if I just pointed it at a blank DB but know such luck.
> If you have any ideas that would be great.
> Thanks in advance
> Cliff
>|||Only in XP, right?
----
The views expressed here are my own
and not of my employer.
----
"Greg Linwood" <g_linwood@.hotmail.com> wrote in message
news:ONWOv8cVDHA.2268@.TK2MSFTNGP11.phx.gbl...
> Hi Cliff.
> It actually is smart enough to just point at a blank db - it will create
the
> schema itself as long as you can get it to connect. The trick is to get it
> connected at all usually.
> What is actually going wrong? Have you checked the Event Log via the Event
> Viewer? If the problem is a login issue, you probably need to add the
> Windows account group that the "Performance Logs & Alerts" service is
> running under as a trusted login under SQL Security / Logins.
> Follow these steps:
> (a) Open the Services applet, under: Start / Control Panel / Services /
> Administrative Tools / Services
> (b) Double click on the "Performance Logs & Alerts" service
> (c) Click on the "Log On" tab
> (d) Identify the account / group (or LocalSystem) that the "Performance
Logs
> & Alerts" service is logging on as
> (e) Open SQL Enterprise Manager
> (f) Click on Security
> (g) Right Click on Logins - select "Add New" Menu
> (h) Use the name ([Domain]\[Account / Group name]) identified in step (d)
> (i) Assign whatever permissions it needs to access the database named in
> your DSN.
> (j) Configure the DSN as a System DSN & ensure that the DSN uses Windows
NT
> Authentication to Log On.
> Then start your trace & you should see the tables created as needed by
> PerfMon.
> HTH
> Regards,
> Greg Linwood
> SQL Server MVP
> "Cliff" <cliff.bree@.bsl.com.au> wrote in message
> news:eoxLfvbVDHA.2368@.TK2MSFTNGP09.phx.gbl...
> > Hi Guys,
> >
> > I have tried the standard MS lists for this question and they didnt know
> > that it was possible. So this might be a DBA type question.
> >
> > I am looking to have 50 ish servers loging through perfmon to a sql DB.
> It
> > seams that you can do this under counter logs then logs you can select a
> > system DSN. Seams easy enough but I cant find any info/script to create
> the
> > DB required for the logging to work. Thought that MS may have been
smart
> > enough if I just pointed it at a blank DB but know such luck.
> >
> > If you have any ideas that would be great.
> >
> > Thanks in advance
> >
> > Cliff
> >
> >
>|||oops, that's what I meant, 2003 or XP. It's also quite easy to get to the
perfmon counters from WMI, and even easier from within .NET.
----
The views expressed here are my own
and not of my employer.
----
"Greg Linwood" <g_linwood@.hotmail.com> wrote in message
news:OhJWx#dVDHA.3332@.tk2msftngp13.phx.gbl...
> Win 2003 does have this feature too.
> It's actually not too hard to write a PDH library wrapper to do this on
> Win2k or earlier if you really need to do this though.
> Regards,
> Greg Linwood
> SQL Server MVP
> "Kevin" <ReplyTo@.Newsgroups.only> wrote in message
> news:ugewv6dVDHA.1832@.TK2MSFTNGP09.phx.gbl...
> > Only in XP, right?
> >
> >
> > --
> > ----
> > The views expressed here are my own
> > and not of my employer.
> > ----
> > "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
> > news:ONWOv8cVDHA.2268@.TK2MSFTNGP11.phx.gbl...
> > > Hi Cliff.
> > >
> > > It actually is smart enough to just point at a blank db - it will
create
> > the
> > > schema itself as long as you can get it to connect. The trick is to
get
> it
> > > connected at all usually.
> > >
> > > What is actually going wrong? Have you checked the Event Log via the
> Event
> > > Viewer? If the problem is a login issue, you probably need to add the
> > > Windows account group that the "Performance Logs & Alerts" service is
> > > running under as a trusted login under SQL Security / Logins.
> > >
> > > Follow these steps:
> > > (a) Open the Services applet, under: Start / Control Panel / Services
/
> > > Administrative Tools / Services
> > > (b) Double click on the "Performance Logs & Alerts" service
> > > (c) Click on the "Log On" tab
> > > (d) Identify the account / group (or LocalSystem) that the
"Performance
> > Logs
> > > & Alerts" service is logging on as
> > > (e) Open SQL Enterprise Manager
> > > (f) Click on Security
> > > (g) Right Click on Logins - select "Add New" Menu
> > > (h) Use the name ([Domain]\[Account / Group name]) identified in step
> (d)
> > > (i) Assign whatever permissions it needs to access the database named
in
> > > your DSN.
> > > (j) Configure the DSN as a System DSN & ensure that the DSN uses
Windows
> > NT
> > > Authentication to Log On.
> > >
> > > Then start your trace & you should see the tables created as needed by
> > > PerfMon.
> > >
> > > HTH
> > >
> > > Regards,
> > > Greg Linwood
> > > SQL Server MVP
> > >
> > > "Cliff" <cliff.bree@.bsl.com.au> wrote in message
> > > news:eoxLfvbVDHA.2368@.TK2MSFTNGP09.phx.gbl...
> > > > Hi Guys,
> > > >
> > > > I have tried the standard MS lists for this question and they didnt
> know
> > > > that it was possible. So this might be a DBA type question.
> > > >
> > > > I am looking to have 50 ish servers loging through perfmon to a sql
> DB.
> > > It
> > > > seams that you can do this under counter logs then logs you can
select
> a
> > > > system DSN. Seams easy enough but I cant find any info/script to
> create
> > > the
> > > > DB required for the logging to work. Thought that MS may have been
> > smart
> > > > enough if I just pointed it at a blank DB but know such luck.
> > > >
> > > > If you have any ideas that would be great.
> > > >
> > > > Thanks in advance
> > > >
> > > > Cliff
> > > >
> > > >
> > >
> > >
> >
> >
>|||Yep - WMI & .Net are good general approaches. I don't want to sound like a
dinosaur, but imho when you're talking about performance monitoring,
efficiency (usually) REALLY matters. Using a .Net wrapper (or even WMI) are
just never going to perform as well as a solution written directly against
the Win32 API (or PDH lib) with an efficient language like C.
Regards,
Greg Linwood
SQL Server MVP
"Kevin" <ReplyTo@.Newsgroups.only> wrote in message
news:#kE0pAeVDHA.1832@.TK2MSFTNGP09.phx.gbl...
> oops, that's what I meant, 2003 or XP. It's also quite easy to get to the
> perfmon counters from WMI, and even easier from within .NET.
> ----
> The views expressed here are my own
> and not of my employer.
> ----
> "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
> news:OhJWx#dVDHA.3332@.tk2msftngp13.phx.gbl...
> > Win 2003 does have this feature too.
> >
> > It's actually not too hard to write a PDH library wrapper to do this on
> > Win2k or earlier if you really need to do this though.
> >
> > Regards,
> > Greg Linwood
> > SQL Server MVP
> >
> > "Kevin" <ReplyTo@.Newsgroups.only> wrote in message
> > news:ugewv6dVDHA.1832@.TK2MSFTNGP09.phx.gbl...
> > > Only in XP, right?
> > >
> > >
> > > --
> > > ----
> > > The views expressed here are my own
> > > and not of my employer.
> > > ----
> > > "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
> > > news:ONWOv8cVDHA.2268@.TK2MSFTNGP11.phx.gbl...
> > > > Hi Cliff.
> > > >
> > > > It actually is smart enough to just point at a blank db - it will
> create
> > > the
> > > > schema itself as long as you can get it to connect. The trick is to
> get
> > it
> > > > connected at all usually.
> > > >
> > > > What is actually going wrong? Have you checked the Event Log via the
> > Event
> > > > Viewer? If the problem is a login issue, you probably need to add
the
> > > > Windows account group that the "Performance Logs & Alerts" service
is
> > > > running under as a trusted login under SQL Security / Logins.
> > > >
> > > > Follow these steps:
> > > > (a) Open the Services applet, under: Start / Control Panel /
Services
> /
> > > > Administrative Tools / Services
> > > > (b) Double click on the "Performance Logs & Alerts" service
> > > > (c) Click on the "Log On" tab
> > > > (d) Identify the account / group (or LocalSystem) that the
> "Performance
> > > Logs
> > > > & Alerts" service is logging on as
> > > > (e) Open SQL Enterprise Manager
> > > > (f) Click on Security
> > > > (g) Right Click on Logins - select "Add New" Menu
> > > > (h) Use the name ([Domain]\[Account / Group name]) identified in
step
> > (d)
> > > > (i) Assign whatever permissions it needs to access the database
named
> in
> > > > your DSN.
> > > > (j) Configure the DSN as a System DSN & ensure that the DSN uses
> Windows
> > > NT
> > > > Authentication to Log On.
> > > >
> > > > Then start your trace & you should see the tables created as needed
by
> > > > PerfMon.
> > > >
> > > > HTH
> > > >
> > > > Regards,
> > > > Greg Linwood
> > > > SQL Server MVP
> > > >
> > > > "Cliff" <cliff.bree@.bsl.com.au> wrote in message
> > > > news:eoxLfvbVDHA.2368@.TK2MSFTNGP09.phx.gbl...
> > > > > Hi Guys,
> > > > >
> > > > > I have tried the standard MS lists for this question and they
didnt
> > know
> > > > > that it was possible. So this might be a DBA type question.
> > > > >
> > > > > I am looking to have 50 ish servers loging through perfmon to a
sql
> > DB.
> > > > It
> > > > > seams that you can do this under counter logs then logs you can
> select
> > a
> > > > > system DSN. Seams easy enough but I cant find any info/script to
> > create
> > > > the
> > > > > DB required for the logging to work. Thought that MS may have
been
> > > smart
> > > > > enough if I just pointed it at a blank DB but know such luck.
> > > > >
> > > > > If you have any ideas that would be great.
> > > > >
> > > > > Thanks in advance
> > > > >
> > > > > Cliff
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||You may want to read this article:
http://www.sqlservercentral.com/columnists/losmith/automatedtracefilecollect
ion.asp
"Greg Linwood" <g_linwood@.hotmail.com> wrote in message
news:OYjPCIeVDHA.2268@.TK2MSFTNGP11.phx.gbl...
> Yep - WMI & .Net are good general approaches. I don't want to sound like a
> dinosaur, but imho when you're talking about performance monitoring,
> efficiency (usually) REALLY matters. Using a .Net wrapper (or even WMI)
are
> just never going to perform as well as a solution written directly against
> the Win32 API (or PDH lib) with an efficient language like C.
> Regards,
> Greg Linwood
> SQL Server MVP
> "Kevin" <ReplyTo@.Newsgroups.only> wrote in message
> news:#kE0pAeVDHA.1832@.TK2MSFTNGP09.phx.gbl...
> > oops, that's what I meant, 2003 or XP. It's also quite easy to get to
the
> > perfmon counters from WMI, and even easier from within .NET.
> >
> > ----
> > The views expressed here are my own
> > and not of my employer.
> > ----
> > "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
> > news:OhJWx#dVDHA.3332@.tk2msftngp13.phx.gbl...
> > > Win 2003 does have this feature too.
> > >
> > > It's actually not too hard to write a PDH library wrapper to do this
on
> > > Win2k or earlier if you really need to do this though.
> > >
> > > Regards,
> > > Greg Linwood
> > > SQL Server MVP
> > >
> > > "Kevin" <ReplyTo@.Newsgroups.only> wrote in message
> > > news:ugewv6dVDHA.1832@.TK2MSFTNGP09.phx.gbl...
> > > > Only in XP, right?
> > > >
> > > >
> > > > --
> > > > ----
> > > > The views expressed here are my own
> > > > and not of my employer.
> > > > ----
> > > > "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
> > > > news:ONWOv8cVDHA.2268@.TK2MSFTNGP11.phx.gbl...
> > > > > Hi Cliff.
> > > > >
> > > > > It actually is smart enough to just point at a blank db - it will
> > create
> > > > the
> > > > > schema itself as long as you can get it to connect. The trick is
to
> > get
> > > it
> > > > > connected at all usually.
> > > > >
> > > > > What is actually going wrong? Have you checked the Event Log via
the
> > > Event
> > > > > Viewer? If the problem is a login issue, you probably need to add
> the
> > > > > Windows account group that the "Performance Logs & Alerts" service
> is
> > > > > running under as a trusted login under SQL Security / Logins.
> > > > >
> > > > > Follow these steps:
> > > > > (a) Open the Services applet, under: Start / Control Panel /
> Services
> > /
> > > > > Administrative Tools / Services
> > > > > (b) Double click on the "Performance Logs & Alerts" service
> > > > > (c) Click on the "Log On" tab
> > > > > (d) Identify the account / group (or LocalSystem) that the
> > "Performance
> > > > Logs
> > > > > & Alerts" service is logging on as
> > > > > (e) Open SQL Enterprise Manager
> > > > > (f) Click on Security
> > > > > (g) Right Click on Logins - select "Add New" Menu
> > > > > (h) Use the name ([Domain]\[Account / Group name]) identified in
> step
> > > (d)
> > > > > (i) Assign whatever permissions it needs to access the database
> named
> > in
> > > > > your DSN.
> > > > > (j) Configure the DSN as a System DSN & ensure that the DSN uses
> > Windows
> > > > NT
> > > > > Authentication to Log On.
> > > > >
> > > > > Then start your trace & you should see the tables created as
needed
> by
> > > > > PerfMon.
> > > > >
> > > > > HTH
> > > > >
> > > > > Regards,
> > > > > Greg Linwood
> > > > > SQL Server MVP
> > > > >
> > > > > "Cliff" <cliff.bree@.bsl.com.au> wrote in message
> > > > > news:eoxLfvbVDHA.2368@.TK2MSFTNGP09.phx.gbl...
> > > > > > Hi Guys,
> > > > > >
> > > > > > I have tried the standard MS lists for this question and they
> didnt
> > > know
> > > > > > that it was possible. So this might be a DBA type question.
> > > > > >
> > > > > > I am looking to have 50 ish servers loging through perfmon to a
> sql
> > > DB.
> > > > > It
> > > > > > seams that you can do this under counter logs then logs you can
> > select
> > > a
> > > > > > system DSN. Seams easy enough but I cant find any info/script
to
> > > create
> > > > > the
> > > > > > DB required for the logging to work. Thought that MS may have
> been
> > > > smart
> > > > > > enough if I just pointed it at a blank DB but know such luck.
> > > > > >
> > > > > > If you have any ideas that would be great.
> > > > > >
> > > > > > Thanks in advance
> > > > > >
> > > > > > Cliff
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||Ah, yes, but monitoring should not be a signicant part of your system
workload anyway, can be done remotely, and can scale out!
--
----
The views expressed here are my own
and not of my employer.
----
"Greg Linwood" <g_linwood@.hotmail.com> wrote in message
news:OYjPCIeVDHA.2268@.TK2MSFTNGP11.phx.gbl...
> Yep - WMI & .Net are good general approaches. I don't want to sound like a
> dinosaur, but imho when you're talking about performance monitoring,
> efficiency (usually) REALLY matters. Using a .Net wrapper (or even WMI)
are
> just never going to perform as well as a solution written directly against
> the Win32 API (or PDH lib) with an efficient language like C.
> Regards,
> Greg Linwood
> SQL Server MVP
> "Kevin" <ReplyTo@.Newsgroups.only> wrote in message
> news:#kE0pAeVDHA.1832@.TK2MSFTNGP09.phx.gbl...
> > oops, that's what I meant, 2003 or XP. It's also quite easy to get to
the
> > perfmon counters from WMI, and even easier from within .NET.
> >
> > ----
> > The views expressed here are my own
> > and not of my employer.
> > ----
> > "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
> > news:OhJWx#dVDHA.3332@.tk2msftngp13.phx.gbl...
> > > Win 2003 does have this feature too.
> > >
> > > It's actually not too hard to write a PDH library wrapper to do this
on
> > > Win2k or earlier if you really need to do this though.
> > >
> > > Regards,
> > > Greg Linwood
> > > SQL Server MVP
> > >
> > > "Kevin" <ReplyTo@.Newsgroups.only> wrote in message
> > > news:ugewv6dVDHA.1832@.TK2MSFTNGP09.phx.gbl...
> > > > Only in XP, right?
> > > >
> > > >
> > > > --
> > > > ----
> > > > The views expressed here are my own
> > > > and not of my employer.
> > > > ----
> > > > "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
> > > > news:ONWOv8cVDHA.2268@.TK2MSFTNGP11.phx.gbl...
> > > > > Hi Cliff.
> > > > >
> > > > > It actually is smart enough to just point at a blank db - it will
> > create
> > > > the
> > > > > schema itself as long as you can get it to connect. The trick is
to
> > get
> > > it
> > > > > connected at all usually.
> > > > >
> > > > > What is actually going wrong? Have you checked the Event Log via
the
> > > Event
> > > > > Viewer? If the problem is a login issue, you probably need to add
> the
> > > > > Windows account group that the "Performance Logs & Alerts" service
> is
> > > > > running under as a trusted login under SQL Security / Logins.
> > > > >
> > > > > Follow these steps:
> > > > > (a) Open the Services applet, under: Start / Control Panel /
> Services
> > /
> > > > > Administrative Tools / Services
> > > > > (b) Double click on the "Performance Logs & Alerts" service
> > > > > (c) Click on the "Log On" tab
> > > > > (d) Identify the account / group (or LocalSystem) that the
> > "Performance
> > > > Logs
> > > > > & Alerts" service is logging on as
> > > > > (e) Open SQL Enterprise Manager
> > > > > (f) Click on Security
> > > > > (g) Right Click on Logins - select "Add New" Menu
> > > > > (h) Use the name ([Domain]\[Account / Group name]) identified in
> step
> > > (d)
> > > > > (i) Assign whatever permissions it needs to access the database
> named
> > in
> > > > > your DSN.
> > > > > (j) Configure the DSN as a System DSN & ensure that the DSN uses
> > Windows
> > > > NT
> > > > > Authentication to Log On.
> > > > >
> > > > > Then start your trace & you should see the tables created as
needed
> by
> > > > > PerfMon.
> > > > >
> > > > > HTH
> > > > >
> > > > > Regards,
> > > > > Greg Linwood
> > > > > SQL Server MVP
> > > > >
> > > > > "Cliff" <cliff.bree@.bsl.com.au> wrote in message
> > > > > news:eoxLfvbVDHA.2368@.TK2MSFTNGP09.phx.gbl...
> > > > > > Hi Guys,
> > > > > >
> > > > > > I have tried the standard MS lists for this question and they
> didnt
> > > know
> > > > > > that it was possible. So this might be a DBA type question.
> > > > > >
> > > > > > I am looking to have 50 ish servers loging through perfmon to a
> sql
> > > DB.
> > > > > It
> > > > > > seams that you can do this under counter logs then logs you can
> > select
> > > a
> > > > > > system DSN. Seams easy enough but I cant find any info/script
to
> > > create
> > > > > the
> > > > > > DB required for the logging to work. Thought that MS may have
> been
> > > > smart
> > > > > > enough if I just pointed it at a blank DB but know such luck.
> > > > > >
> > > > > > If you have any ideas that would be great.
> > > > > >
> > > > > > Thanks in advance
> > > > > >
> > > > > > Cliff
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>

loging in to sql express

I am trying to login into sql express using studio express and keep getting this msg.

"Cannot connect

....... under the default setting SQL Server does not allow remote connections. (provider: Named Popes Provider, error: 40 - could not open a connection to sql server) (sql server error2)"

I did try and make a change under surface area configuration to include local and remote connections but it did not help. Any thoughts? Thanks!

David

Are you using the SQL Server Browser service ? If not it could that that you might not use the default port of 1433. If so, type in the port number after the InstanceName using the syntax Servername\InstanceName,PortNumber which should be in your case something more specific Servername\SQLExpress,PortNumber

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||Jens, thanks. You gave me the idea to start using the browser service and I logged in that way.

Login to SQL Server management studio

Hi,

I am facing a strange behavior during loging in to SQL server mangement studio.

Whenever I install SQL server express, various protocols like Named Pipe and TCP/IP and SQL browser are disabled by default. But still I am able to log in to SQL Server throguh Management studio.

But when I try to log in to SQL server throguh one of my applications, it allows me to log in to it only for one time. From next time onwards, I am not able to log in to SQL server neither through my application nor through Management studio.

I connect to SQL server using "machine name"\"Instance name". Machine name is the name of machine on my office LAN.

PS: Once this problem occurs, I can log in to SQL server if I use "localhost" instead of "machine name".

Any suggestions would be tremendously helpful.

Regards

-Orion

SQL Express supports connecting through Shared Memory, which is a local connectivity protocol that is used by the SQL Native Client. This is how you're connecting with Management Studio. I can't tell you what the problem is with your application as you have not provided an error message or error log.

Mike

|||

Hi Mike,

Thanks for the reply.

>>
I can't tell you what the problem is with your application as you have not provided an error message or error log
>>

Following are the details of the exception thrown by SQL server.

{"State:08001,Native:6,Origin:[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets] State:01000,Native:11004,Origin:[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]"}

The problem is when it allows my application to connect to SQL Server only once. After this, I need to enable TCP/IP protocol for any subsequent connection through my application or SQL Server management studio.

Regards,
Orion

|||

Hi Orion,

Could you define what you mean by "subsequent connections through my application"? Are you talking about a single application creating multiple threads connecting to SQL Server? Are these different copies of the same application being run by other users on other computers? What is the connection string you're using from your application?

I don't recognize that error, is there any more descirptive text?

Mike