Database Mirroring, client connections all ODBC so what methods are recommended?

As stated in the subject I have a situation where if database mirroring is employed for either manual or automatic failover, all the client (including web connections) connections use ODBC not ADO, or OLEDB etc... so what methods are recommended? Client side redirect is not available so I could not employe the "Data Source =A; Failover Partner=B..." option.

Right now the method employed (pre database mirroring and basically employing log shipping on SQL 2000) is to have a DNS alias for the ODBC connection so that if the server were to change in a failover situation the DNS record would have to be altered, so that all the client connections would not have to be reconfigured.

Regards,

Dominic Baines

I have one customer in production that uses the DNS name change method to switch over the clients after a failover, and so that method does have uses.

Alternatively, you could have the application handle the failover by issuing the redirect to another server.

Thanks,

Mark

|||

Sorry Mark but just not an option to recode app connection strings.

Several reasons:

1. Most apps are 3rd party and insist on ODBC only connections (I know old method but that is state of affairs).

2. Recoding ODBC connections means we would have to visit physically or by logon/registry script every client to reconfigure connection when failover occurs. Not practical and bound to miss some.

3. When fail back occurs same problem exists in reverse.

We simply have too many applications and over 50 DB's on 6 pairs of servers in the failover/failback situation (canot use clustering) and 100+ app servers and many many direct connect clients involved. And the desktop clients spread over 5 countries including several timezones and as most everyone else these days also India.

It looks like DNS alias is the option unless anyone has a better one? It was originally coded for older NT4 style domain then migrated to AD, which was interesting but that is a different story.

As this appears to be the case can the failover be used to trigger a script in SQL2005 (that will update the DNS (I can handle that script). How could you detect the failover has occured? Still a bit too new to SQL2005 to know the answer to this one.

(and...thanks for the message on the webcast, Kimberley has got in touch).

Regards,

Dom

|||

I understood that the SQL 2005 ODBC provider (SQL Native Client ODBC provider) was part of the SNAC stack and therefore would support failover. Of course the SQL 2005 SNAC bits and pieces would need to be installed on the clients/servers and the connection string(s)/DSNs modified to support a mirror, but that would be a one off operation, albeit maybe a large one.

As per BOL : "The SQL Native Client ODBC driver complies with the Microsoft Win32? ODBC 3.51 specification. The driver supports applications written with the ODBC 2.5 or earlier versions of the ODBC functions in the manner defined in the ODBC 3.51 specification."

Am I missing something?

|||

I have marked as answer as basically the solution does provide an answer that will probably assist some.

However, I will carry on using the DNS method for the moment.

There is no scope right now to upgrade the native ODBC drivers as I am 'stuck' with desktops with older drivers and reconfiguring the client driver or connections is not possible as it would also mean rolling out new desktop images.

Regards,

Dom

sql

Database Mirroring Question

I've read several instances that when implementing Database Mirroring, all
client connections are lost. A retry from client/s, applications, etc... will
reconnect to the Failover.
In order to compensate for the disconnect you should make use of TRY CATCH
logic within your code.
When going over that statement, TRY CATCH is not valid for disconnects so
how would you accomplish this feat?
What I mean is how could I allow my application to essentially reestablish
the coonection or look for the Failover from within my Connection String?
Many Thx,
Anthony E. Castro - MCDBA
Need to utilize FAILOVER PARTNER=<Mirror SQL server> in your connection string.
"acmcdba68" wrote:

> I've read several instances that when implementing Database Mirroring, all
> client connections are lost. A retry from client/s, applications, etc... will
> reconnect to the Failover.
> In order to compensate for the disconnect you should make use of TRY CATCH
> logic within your code.
> When going over that statement, TRY CATCH is not valid for disconnects so
> how would you accomplish this feat?
> What I mean is how could I allow my application to essentially reestablish
> the coonection or look for the Failover from within my Connection String?
> Many Thx,
> --
> Anthony E. Castro - MCDBA

Database Mirroring Connection Question

I've read several instances that when implementing Database Mirroring and a
fail-over
occurs, all client connections are lost. A retry from client/s,
applications, etc... will
reconnect to the Failover.
In order to compensate for the disconnect you should make use of TRY CATCH
logic within your code.
When going over that statement, TRY CATCH is not valid for disconnects so
how would you accomplish this feat?
What I mean is how could I allow my application to essentially reestablish
the coonection or look for the Failover from within my Connection String?
--
Anthony E. Castro - MCDBADoesn't the disconnect raise a SqlException that could be caught and you
resubmit the failed request? Have you tried this already?
In case you are using ADO.Net 2.0 or later, you have a 'failover
partner' parameter in the connection string that SqlClient will use to
connect to if there was loss of connectivity to the primary server.
So, all your application needs to do is resubmit the request - whichever
database - principal or the new principal - that SqlClient is able to
connect to will be used.
acmcdba68 wrote:
> I've read several instances that when implementing Database Mirroring and a
> fail-over
> occurs, all client connections are lost. A retry from client/s,
> applications, etc... will
> reconnect to the Failover.
> In order to compensate for the disconnect you should make use of TRY CATCH
> logic within your code.
> When going over that statement, TRY CATCH is not valid for disconnects so
> how would you accomplish this feat?
> What I mean is how could I allow my application to essentially reestablish
> the coonection or look for the Failover from within my Connection String?
>

Database Maintenance on a SQL 7 Database

I have a client that I just took on who is running SQL 7. Their database
isn't too big - about 400mb. They are telling me that the performance isn't
nearly as good as it once was. They have been using this database for about 6
years now. I don't believe they have been doing any kind of maintenance on
the database except for backing it up. Can anyone tell me what I should be
doing to this database on a routine basis? Also, they tell me that it is
mainly saving data that takes a very long time. Could this be due to the fact
that maintenance hasn't been performed on this database in it's entire
existence? Thank you.Hi Jeff
You could look at using a maintenance plan and go through the wizard to
specify your index maintenance, or you could write your own more targetted
process using DBCC SHOWCONTIG, DBCC INDEXDEFRAG and DBCC DBREINDEX the
action you take depending on the level of fragmentation reported (you can use
the example in SQL 2000 books online DBCC SHOWCONTIG as the basis for such a
process).
John
"Jeff" wrote:
> I have a client that I just took on who is running SQL 7. Their database
> isn't too big - about 400mb. They are telling me that the performance isn't
> nearly as good as it once was. They have been using this database for about 6
> years now. I don't believe they have been doing any kind of maintenance on
> the database except for backing it up. Can anyone tell me what I should be
> doing to this database on a routine basis? Also, they tell me that it is
> mainly saving data that takes a very long time. Could this be due to the fact
> that maintenance hasn't been performed on this database in it's entire
> existence? Thank you.

Database Maintenance on a SQL 7 Database

I have a client that I just took on who is running SQL 7. Their database
isn't too big - about 400mb. They are telling me that the performance isn't
nearly as good as it once was. They have been using this database for about 6
years now. I don't believe they have been doing any kind of maintenance on
the database except for backing it up. Can anyone tell me what I should be
doing to this database on a routine basis? Also, they tell me that it is
mainly saving data that takes a very long time. Could this be due to the fact
that maintenance hasn't been performed on this database in it's entire
existence? Thank you.
Hi Jeff
You could look at using a maintenance plan and go through the wizard to
specify your index maintenance, or you could write your own more targetted
process using DBCC SHOWCONTIG, DBCC INDEXDEFRAG and DBCC DBREINDEX the
action you take depending on the level of fragmentation reported (you can use
the example in SQL 2000 books online DBCC SHOWCONTIG as the basis for such a
process).
John
"Jeff" wrote:

> I have a client that I just took on who is running SQL 7. Their database
> isn't too big - about 400mb. They are telling me that the performance isn't
> nearly as good as it once was. They have been using this database for about 6
> years now. I don't believe they have been doing any kind of maintenance on
> the database except for backing it up. Can anyone tell me what I should be
> doing to this database on a routine basis? Also, they tell me that it is
> mainly saving data that takes a very long time. Could this be due to the fact
> that maintenance hasn't been performed on this database in it's entire
> existence? Thank you.

Database Maintenance on a SQL 7 Database

I have a client that I just took on who is running SQL 7. Their database
isn't too big - about 400mb. They are telling me that the performance isn't
nearly as good as it once was. They have been using this database for about
6
years now. I don't believe they have been doing any kind of maintenance on
the database except for backing it up. Can anyone tell me what I should be
doing to this database on a routine basis? Also, they tell me that it is
mainly saving data that takes a very long time. Could this be due to the fac
t
that maintenance hasn't been performed on this database in it's entire
existence? Thank you.Hi Jeff
You could look at using a maintenance plan and go through the wizard to
specify your index maintenance, or you could write your own more targetted
process using DBCC SHOWCONTIG, DBCC INDEXDEFRAG and DBCC DBREINDEX the
action you take depending on the level of fragmentation reported (you can us
e
the example in SQL 2000 books online DBCC SHOWCONTIG as the basis for such a
process).
John
"Jeff" wrote:

> I have a client that I just took on who is running SQL 7. Their database
> isn't too big - about 400mb. They are telling me that the performance isn'
t
> nearly as good as it once was. They have been using this database for abou
t 6
> years now. I don't believe they have been doing any kind of maintenance on
> the database except for backing it up. Can anyone tell me what I should be
> doing to this database on a routine basis? Also, they tell me that it is
> mainly saving data that takes a very long time. Could this be due to the f
act
> that maintenance hasn't been performed on this database in it's entire
> existence? Thank you.

Database Mail

I am using the sp_send_dbmail for my mailing activity for my client
applications. I have created a profile for the SP and assigned my name
and e-mail address. but when any of my users send a mail from the
client application the display name of the mail is my name and not the
person who actually sends the mail.
Is it possible to set the display name dynamically? or anyother
workaround ?
Any help would be appreciated :)
ThanxsThe only way I could think of is to setup several mail profiles and have each user use a different
mail profile. Or, use xp_smtp_sendmail. Also, consider wishing this feature at
connect.microsoft.com.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<brinda.shree@.gmail.com> wrote in message
news:1173789930.776042.169600@.c51g2000cwc.googlegroups.com...
>I am using the sp_send_dbmail for my mailing activity for my client
> applications. I have created a profile for the SP and assigned my name
> and e-mail address. but when any of my users send a mail from the
> client application the display name of the mail is my name and not the
> person who actually sends the mail.
> Is it possible to set the display name dynamically? or anyother
> workaround ?
> Any help would be appreciated :)
> Thanxs
>

Database login

I have a web based application that could have many clients/users.
Should I create a user account for each client browsing the web site.
They can only access certain data after they have been verified from a
user table. They use a common database login to access the data. This
is planned to be a public free site. So hopefully the client count
will be high.create login that the application uses to get to the DB... instead of
creating a login for each user.|||Ok thats they wey I'm approaching it now.|||Are you using an ODBC or DSN less, either way assuming the users should
have the same permissions then 1 user is created in the db with a certain
level of permissions. You may also have an admin section , therfore the
admin user may have a different set of permissions
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
"nathan001" <nathan@.thelosthorizon.com> wrote in message
news:1143477924.849156.7120@.z34g2000cwc.googlegroups.com...
> Ok thats they wey I'm approaching it now.
>|||From what you describe, it sounds like you need ASP.NET Forms
Authentication. At a minimum, you will need to implement a table with one
row for each subscriber.
ASP.NET Forms Authentication Overview
http://msdn2.microsoft.com/en-us/library/7t6b43z4.aspx
As for database (server) logins, you will need to implement one login for
each type of user (admin, subscriber, guest, etc.) and then use SQL Server
role based permissions to restrict each user's access to specific database
objects.
SQL Server 2000 Administrator's Pocket Consultant - Chapter 5: SQL Server
2000 Security
http://www.microsoft.com/technet/pr...s/c05ppcsq.mspx
SQL Server 2000 Operations Guide: Security Administration
http://www.microsoft.com/technet/pr...in/sqlops3.mspx
SQL Server 2000 Resource Kit - Chapter 10: Implementing Security
http://www.microsoft.com/technet/pr...fr=true

If this database is hosted by a 3rd party internet service provider, then
you will need to coordinate with the server administrator to setup the
logins and permissions, becuase these configurations require server admin
rights. If your ISP provides this functionality through their website
management tool, then that's great, but that would be exceptional.
"nathan001" <nathan@.thelosthorizon.com> wrote in message
news:1143473709.083112.37370@.i40g2000cwc.googlegroups.com...
>I have a web based application that could have many clients/users.
> Should I create a user account for each client browsing the web site.
> They can only access certain data after they have been verified from a
> user table. They use a common database login to access the data. This
> is planned to be a public free site. So hopefully the client count
> will be high.
>

Database location in crystal report

Hello
i am totally new to crystal reports
Do we need to change database location of crystal report after installation at client side
If this is case then if there are 1000 reports, database location has to be changed for each report?
any there some shortcut to do this programmatically or any other wayHi,

use a DataSethttp://support.businessobjects.com/communityCS/TechnicalPapers/rtm_reportingoffadonetdatasets.pdf

or a ReportIni like this:
Private Sub ReportInit(ByVal blRefreshLocation As Boolean)

Dim crSections As Sections
Dim crSection As Section
Dim crReportObjects As ReportObjects
Dim crReportObject As ReportObject
Dim crSubreportObject As SubreportObject

Dim crReportDocument As ReportDocument
Dim crSubreportDocument As ReportDocument

Dim crDatabase As Database
Dim crTables As Tables
Dim crTable As Table
Dim crTableLogOnInfo As TableLogOnInfo
Dim crConnectioninfo As ConnectionInfo

Dim myArrayList As ArrayList = New ArrayList

'declare an instance of the report and the connectionInfo object

crReportDocument = New ReportDocument
crConnectioninfo = New ConnectionInfo

crReportDocument.Load(mstrReportName)

'pass the necessary parameters to the connectionInfo object
With crConnectioninfo
.ServerName = mstrDBServerName
.DatabaseName = mstrDataBaseName
End With

'set up the database and tables objects to refer to the current report
crDatabase = crReportDocument.Database
crTables = crDatabase.Tables

'loop through all the tables and pass in the connection info
For Each crTable In crTables
crTableLogOnInfo = crTable.LogOnInfo
crTableLogOnInfo.ConnectionInfo = crConnectioninfo
If blRefreshLocation Then
crTable.Location = mstrDataBaseName & ".dbo." &
crTable.Location.Substring(crTable.Location.LastIndexOf(".") + 1)
End If
crTable.ApplyLogOnInfo(crTableLogOnInfo)
Next

'set the crSections object to the current report's sections
crSections = crReportDocument.ReportDefinition.Sections

'loop through all the sections to find all the report objects
For Each crSection In crSections
crReportObjects = crSection.ReportObjects
'loop through all the report objects to find all the subreports
For Each crReportObject In crReportObjects
If crReportObject.Kind = ReportObjectKind.SubreportObject Then
'you will need to typecast the reportobject to a
subreport
'object once you find it
crSubreportObject = CType(crReportObject, SubreportObject)

'open the subreport object
crSubreportDocument =
crSubreportObject.OpenSubreport(crSubreportObject.SubreportName)

'set the database and tables objects to work with the
subreport
crDatabase = crSubreportDocument.Database
crTables = crDatabase.Tables

'loop through all the tables in the subreport and
'set up the connection info and apply it to the tables
For Each crTable In crTables
With crConnectioninfo
.ServerName = mstrDBServerName
End With
crTableLogOnInfo = crTable.LogOnInfo
crTableLogOnInfo.ConnectionInfo = crConnectioninfo
If blRefreshLocation Then
crTable.Location = mstrDataBaseName & ".dbo." &
crTable.Location.Substring(crTable.Location.LastIndexOf(".") + 1)
End If
crTable.ApplyLogOnInfo(crTableLogOnInfo)
Next
End If
Next
Next

crystalReportViewer.ReportSource = crReportDocument

End Sub|||You need to make database connection suggested above or Try to use DSN name