Using ADO with SQL Server Native Client

To enable the usage of SQL Server Native Client, ADO applications will need to implement the following keywords in their connection strings:

Provider=SQLNCLI10
DataTypeCompatibility=80

The following is an example of establishing an ADO connection string that is fully enabled to work with SQL Server Native Client (SQL Server 2008 R2):

Dim connection As New ADODB.Connection
connection.ConnectionString = “Provider=SQLNCLI10;” _
& “SERVER=(local);” _
& “Database=Northwind;” _
& “DataTypeCompatibility=80;” _
& “User Id=sa;” _
& “Password=Pa$$w0rd;”
connection.Open

The following is an example of establishing an ADO connection string that is fully enabled to work with SQL Server Native Client (SQL Server 2012 and SQL Server 2014):

Dim connection As New ADODB.Connection
connection.ConnectionString = “Provider=SQLNCLI11;” _
& “SERVER=(local);” _
& “Database=Northwind;” _
& “DataTypeCompatibility=80;” _
& “User Id=sa;” _
& “Password=Pa$$w0rd;”
connection.Open

SharePoint 2010 with SQL Server 2012

To install SharePoint 2010 on SQL server 2012, you will need to ensure that you have at least the SharePoint 2010 with Service Pack 1 installer. SQL Server 2012 removed the system stored procedure sp_dboptions. This is a required stored procedure for installing SharePoint 2010. Service pack 1 for SharePoint 2010 does eliminate the dependency for the sp_dboptions stored procedure.

General access denied error in Hyper-V

This issue occurs if the permissions on the virtual hard disk (.vhd/.vhdx) file or the snapshot file (.avhd/.avhdx) are incorrect. To resolve this issue, perform the following steps:

1. Right click the virtual machine folder (the folder above the “Snapshots”, “Virtual Hard Disks”, and “Virtual Machines” folder), and
go to the “Properties” -> “Security”, select “Virtual Machines” user group, then click “Edit”” button, then select “Virtual Machines” user group.

Note: You see that this user group doesn’t have any rights over the virtual machine folder.

2. Grant all rights to “Virtual Machines”, then click “OK” button.

3. Done!

What is REDO queue in database mirroring?

The REDO queue is the set of log records that have been received on the mirror from the principal, but have not yet been replayed in the mirror database. Once a failure is initiated, the mirror database will not come online as the principal until the REDO queue has been processed. It is very important to monitor the REDO queue on the mirror. The amount of REDO queue correlates to the amount of downtime you’ll experience during a mirroring failover. This is due to the fact that the amount of unrestored log waiting in the redo queue is an indicator of the time required to fail over to the mirror database.

Operating modes in database mirroring

Operating modes in database mirroring is one of the common questions usually posted to me during the DBA trainings that I have delivered over the years.

In summary, there are two operating modes in database mirroring:

  • High-performance mode. The database mirroring session operates asynchronously and uses only the principal server and mirror server. The only form of role switching is forced service (with possible data loss). In high-performance mode, as soon as the principal server sends the log for a transaction to the mirror server, the principal server sends a confirmation to the client, without waiting for an acknowledgement from the mirror server. Transactions commit without waiting for the mirror server to write the log to disk. Asynchronous operation permits the principal server to run with minimum transaction latency.
  • High-safety mode. The database mirroring session operates synchronously and, optionally, uses a witness, as well as the principal server and mirror server. In high-safety mode,  the mirror server must synchronize the mirror database with the principal database. When the session begins, the principal server begins sending its active log to the mirror server. The mirror server writes all of the incoming log records to disk as quickly as possible. As soon as all of the received log records have been written to disk, the databases are synchronized. As long as the partners remain in communication, the databases remain synchronized.

Cannot drop server ‘repl_distributor’ because it is used as a Distributor in replication

Recently I encountered the following error:

Cannot drop server ‘repl_distributor’ because it is used as a Distributor in replication

during an attempt to remove the linked server that is created as part of a replication. The following is the fix to this error:

EXEC master.dbo.sp_serveroption @server=N'<SERVER_NAME>’, @optname=N’dist’, @optvalue=N’true’
GO

EXEC sp_dropdistributor @no_checks = 1, @ignore_distributor = 1
GO

You need to replace <SERVER_NAME> with the computer name of the distributor.

Server ‘<SERVERNAME' is not configured for RPC error

Recently, I was doing a proof of concept on database mirroring and needed to execute a stored procedure on the principal server through linked server. I ran into the following error when I attempt to execute the stored procedure from another server:

Server ‘<SERVERNAME’ is not configured for RPC

Upon further troubleshooting, I discovered that there is an option for ‘RPC’ and ‘RPC Out’. To resolve the issue, ‘RPC Out’ needs to be set to ‘True’ in order to successfully execute the stored procedure. ‘RPC’ allows stored procedure call from the linked server whereas ‘RPC Out’ allows stored procedure calls to go out to the linked server.

Follow

Get every new post delivered to your Inbox.