SQL Application Role Question 
Author Message
 SQL Application Role Question

Dear Group,

I have created a small SQL database and application to record PC settings on
about 120 pcs. My central IT are going to run the software on the pcs all at
the same time. If I have an Application Role Security to handle the
connections with the database and set up to enter data like the code below
(which is ammended with non-calculated values) does the group think that the
server will be able to cope with connection pooling, etc? The PCs are all
different ages and speeds so it is unlikely that I will get 120pcs all
opening connections with the database at the same time. If this will not
work is there another more robust method.

Thanks again for the help of the group. This is the first time I have had to
set something up like this so I am a wee bit unsure.

Alastair MacFarlane

_______________________________________________

Option Explicit

Private WithEvents mcnn As ADODB.Connection

Private Sub Form_Load()
  Dim strConn As String

' Create the connection.
  strConn = "Provider=sqloledb;" & _
    "Data Source=CSW024579;" & _
    "Initial Catalog=PCID;" & _
    "OLE DB Services = -2;" & _
    "Integrated Security=SSPI"
  ' Open the connection, and activate the application role.
  Set mcnn = New ADODB.Connection
  mcnn.Open strConn
  mcnn.Execute "sp_setapprole 'PCID', 'PCID'"
  mcnn.Execute "usp_InsertPCID
'pc154','Glasgow28','255.255.255','TVW024579'"
  mcnn.Close
  Set mcnn = Nothing
End Sub



Sat, 02 Jul 2011 00:42:38 GMT  
 SQL Application Role Question


Quote:
> Dear Group,

> I have created a small SQL database and application to record PC settings
on
> about 120 pcs. My central IT are going to run the software on the pcs all
at
> the same time. If I have an Application Role Security to handle the
> connections with the database and set up to enter data like the code below
> (which is ammended with non-calculated values) does the group think that
the
> server will be able to cope with connection pooling, etc? The PCs are all
> different ages and speeds so it is unlikely that I will get 120pcs all
> opening connections with the database at the same time. If this will not
> work is there another more robust method.

> Thanks again for the help of the group. This is the first time I have had
to
> set something up like this so I am a wee bit unsure.

> Alastair MacFarlane

> _______________________________________________

> Option Explicit

> Private WithEvents mcnn As ADODB.Connection

> Private Sub Form_Load()
>   Dim strConn As String

> ' Create the connection.
>   strConn = "Provider=sqloledb;" & _
>     "Data Source=CSW024579;" & _
>     "Initial Catalog=PCID;" & _
>     "OLE DB Services = -2;" & _
>     "Integrated Security=SSPI"
>   ' Open the connection, and activate the application role.
>   Set mcnn = New ADODB.Connection
>   mcnn.Open strConn
>   mcnn.Execute "sp_setapprole 'PCID', 'PCID'"
>   mcnn.Execute "usp_InsertPCID
> 'pc154','Glasgow28','255.255.255','TVW024579'"
>   mcnn.Close
>   Set mcnn = Nothing
> End Sub

Perhaps I'm misunderstanding your question, but in this case "ADO Connection
Pooling" has nothing to do with the number, or management, of connections to
the server.

You are creating one ADODB.Connection object. It has one ADO Connection
Pool.  You are requesting and getting one (* caveat below) connection to the
server from the pool. You are using it, then exiting destroying any
connections and the Connection object. The end result is 120 clients
requesting 'A' connection to the server. Any 'pooling' or management to be
done will be done by the server - not the app.

* Some types of queries will cause SQL Server to open more than one
connection to manage the request. But this is essentially transparent to the
client.

-ralph



Sat, 02 Jul 2011 04:16:58 GMT  
 SQL Application Role Question
Ralph,

Sorry for the misunderstanding. You have put me right and I am now much
happier with that. Thanks again.

Alastair

Quote:



> > Dear Group,

> > I have created a small SQL database and application to record PC settings
> on
> > about 120 pcs. My central IT are going to run the software on the pcs all
> at
> > the same time. If I have an Application Role Security to handle the
> > connections with the database and set up to enter data like the code below
> > (which is ammended with non-calculated values) does the group think that
> the
> > server will be able to cope with connection pooling, etc? The PCs are all
> > different ages and speeds so it is unlikely that I will get 120pcs all
> > opening connections with the database at the same time. If this will not
> > work is there another more robust method.

> > Thanks again for the help of the group. This is the first time I have had
> to
> > set something up like this so I am a wee bit unsure.

> > Alastair MacFarlane

> > _______________________________________________

> > Option Explicit

> > Private WithEvents mcnn As ADODB.Connection

> > Private Sub Form_Load()
> >   Dim strConn As String

> > ' Create the connection.
> >   strConn = "Provider=sqloledb;" & _
> >     "Data Source=CSW024579;" & _
> >     "Initial Catalog=PCID;" & _
> >     "OLE DB Services = -2;" & _
> >     "Integrated Security=SSPI"
> >   ' Open the connection, and activate the application role.
> >   Set mcnn = New ADODB.Connection
> >   mcnn.Open strConn
> >   mcnn.Execute "sp_setapprole 'PCID', 'PCID'"
> >   mcnn.Execute "usp_InsertPCID
> > 'pc154','Glasgow28','255.255.255','TVW024579'"
> >   mcnn.Close
> >   Set mcnn = Nothing
> > End Sub

> Perhaps I'm misunderstanding your question, but in this case "ADO Connection
> Pooling" has nothing to do with the number, or management, of connections to
> the server.

> You are creating one ADODB.Connection object. It has one ADO Connection
> Pool.  You are requesting and getting one (* caveat below) connection to the
> server from the pool. You are using it, then exiting destroying any
> connections and the Connection object. The end result is 120 clients
> requesting 'A' connection to the server. Any 'pooling' or management to be
> done will be done by the server - not the app.

> * Some types of queries will cause SQL Server to open more than one
> connection to manage the request. But this is essentially transparent to the
> client.

> -ralph



Sat, 02 Jul 2011 18:25:00 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. Using SQL Server Application Roles in a VB.NET web app

2. sql application roles and crystal reports

3. SQL Server Integrated login & application Roles

4. SQL Server Application Roles

5. Problems using SQL Server application roles (long)

6. Activate SQL Application Role with VB6.0

7. SQL Server Application roles and connections

8. SQL Server Application Role

9. Using SQL Application Role in VB

10. SQL Server Application Role

11. SQL server application role and VB connection

12. SQL Application role and dlls

 

 
Powered by phpBB® Forum Software