In development environments I prefer to use ‘detach’ database option to move around databases. It’s simple, fast and easy.

Almost all the time when I detached a database earlier the feeling was like something which I mentioned before “Simple, fast and easy”, however today it was more sort of a fun experience. Here is that tale for you all.

Today I had to ‘detach’ a database from one of my development environments and straight away got this error !

[Pic courtesy : temzlandia.blogspot.com ]

The database was setup for replication, hence it cannot be dropped.

Apparently, my next step was to remove the publication which was setup for this database and the tale begins from this point.

Note – This was a very controlled dev environment and action like detaching, removing replication was not going to harm anything. In case you are going to repro this demo, then I would recommend you to do this on your lab environment.

I selected the required publication and went ahead with delete operation. The wizard was double checking with me if I was sure. I said Yes to it !

Bang! here comes the next error

The error was pointing that there are some connection issues to the Distributor or to the Subscriber. 

The distributor and the subscriber for this publication were already retired servers and not online and removing publication requires a connection to be made to the distributor/subscriber servers too. As those servers were not reachable, the wizard couldn’t proceed.

There is no way you could delete this publication directly using SSMS and the best way to do this is via system stored procedures.

I had to use 3 system stored procedures to cleanup this publication and those are –

sp_dropsubscription

sp_droppublication

sp_replicationdboption

The scripts used for the clean up processes are mentioned below –

--Cleans up the subscriptions for a publisher

USE <Your Database>
GO
EXEC sp_dropsubscription @publication = N'Your_Publication', 
@article = N'all', @subscriber = N'all', @destination_db = N'all', @ignore_distributor = 1

--Cleans up the publication

USE <Your Database>
GO
EXEC sp_droppublication @publication = N'Your_Publication', @ignore_distributor = 1
--Set Replication database option to false

USE Master
GO
EXEC sp_replicationdboption @dbname = N'Your_Publication', @optname = N'publish', @value = N'false'
,@ignore_distributor = 1

If you note the scripts you can find that they all have an argument @ignore_distributor passed as 1.All the three scripts were failing without this as the connection to distributor was not available as it was offline.

I was curious to check what exactly @ignore_distributor does, however the below BOL entry didnt gave me any clue –

[ @ignore_distributor = ] ignore_distributor

Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.

It’s very much clear that removing a publication will check connections to the Distributor and in case it’s not available for connection, there will be connection errors.

After running the above mentioned stored procedures I could proceed and detach the database.

Thanks for reading.

One of the advanced Server level options for SQL Server is Max Text Replication Size and this is really not a common one. In most of cases the default value is not changed.

This configuration specifies the maximum size of data that can be added to a replicated column in a single INSERT, UPDATE, WRITETEXT or UPDATETEXT statement. This applies to data type text, ntext, varchar (max), nvarchar (max), varbinary (max), xml, and image.

The default value for this configuration is 65536 bytes = 0.0625 MB.

Why we care about this value? Here is a reason why we might have to carefully change this value according to our requirements.

Recently I had setup a database for Citrix XenApp 6.5 farm data store requirements and configured transactional replication as part of the DR requirement.

After configuring replication the Citrix team where able to read data out of the database, however nothing was getting published. They were consistently getting errors like “Unknown error occurred: error code 0x82060035”

Things where working great before and this error started popping up after replication was setup. The only change made was setting replication for this database.

Upon checking the database closely I found out one table called dbo.KEYTABLE which had a column called data which was of type Varbinary (max).

As this column was part of transactional replication the value of Max Text Replication Size came into picture and anything above 65536 bytes on a single Insert was not allowed and Citrix faced issues while publishing new apps.

Carefully choosing the best possible value for this configuration setting fixed the problem.

It has a max value of 2147483647 bytes which is 2 GB.

I really don’t recommend directly increasing the value to 2147483647 bytes, and I would always test a good value which works fine for the environment.

High value will allow huge Inserts,Update in a single statement and can bring in network latency while replicating.

I had a twitter discussion with SQLServer expert Robert L Davis(B/T) to double check if changing this server level value has any other direct impact, and he confirmed that there aren’t any. Many thanks to Robert!

Thanks for reading.

When your cluster install fails, then there is lot to learn!!!

Today I am writing about my very recent experience working on a clustering deployment. It was for a two node cluster with single SQL Instance.

I stopped using Active/Passive terminology long back as it is not the right usage. Clustering MVP Allen Hirt (B/T) has pointed out this fact much time via his blog posts and through SQL forums.

There were no errors returned during the initial stages (Rule checks) of SQL cluster install. The setup apparently gave the below error at one point during the final configuration process and the Database Engine Install was failed.

The cluster resource ‘SQL Server’ could not be brought online.  

Error: The resource failed to come online due to the failure of one or more provider resources.

(Exception from HRESULT: 0x80071736)

There were no specific details on the SQL error log (Available under the Setup Bootstrap folder) which I could observe which eventually will lead me to find the reason for the error.

I kept checking the Windows error logs and hit this event right away –

[Click the picture for full view]

The reason for the error is the CNO (cluster computer account) don’t have the create computer perms at OU level.

We can test this by doing a simple Client Access Point Test

We can provide a Name and an IP (which gets picked automatically).This will create a computer object just the same way SQL Server does.

In some cases the Cluster service account are blocked from creating a computer object. In that situation you will need to work with the domain administrator and they should pre-create the virtual server computer object, and then grant certain access rights to the Cluster service account on the pre-created computer object.

In my case the domain services team created the computer object manually and then granted the cluster account full permissions for the same.

Conclusion

Domain level permissions are really important during cluster deployments, hence the person responsible for setting up the SQL cluster should closely interact with both windows team and domain services team(In most of the cases, both operations are handled by one single team) to understand what level of permissions are required or closely work together to isolate and fix potential problems.