Tuesday, August 26, 2014

Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.

Today one of our development team member reported following error on newly restored database in staging environment

Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.

Error was generated in a stored procedure. First of all i found that collation of subject database is different than SQL_Latin1_General_CP1_CI_AS. Using this method i changed the collation of subject database to required SQL_Latin1_General_CP1_CI_AS.
I was hopeful that changing collation of database will solve the issue but it was not the case. I opened the stored procedure code and got the tables being used in it. 
Suppose we have table1, table2 and table3 in USP. Now we have to get all those columns where collation is different than our required collation. Following script did task and returned the columns with different collation.

SELECT object_name(object_id) as TableName, 
name as columnName, collation_name
FROM sys.columns
FROM sys.objects where name in ('table1','table2','table3'))
and collation_name <> 'SQL_Latin1_General_CP1_CI_AS'

Once we have the list of columns then we can change their collation to match with required collation. This can be done with following script.

ALTER TABLE table1 ALTER COLUMN ColumnNameHere nvarchar(512) COLLATE SQL_Latin1_General_CP1_CI_AS  NULL
ALTER TABLE table2 ALTER COLUMN ColumnNameHere varchar(64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
ALTER TABLE table3 ALTER COLUMN ColumnNameHere varchar(512) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL

This time i found that issue was resolved and there was no error.

Tuesday, May 6, 2014

Using 'odsole70.dll' version '2009.100.1600' to execute extended stored procedure 'sp_OACreate'

Recently we came across unexpected cluster fail over of one of our servers. It was required to get the exact reason for it. I analyzed the sql server and windows logs. There was no traces of failure neither i found any major issue in the logs that may lead to fail over. However an entry in the logs caught my attention and it was following message

Using 'odsole70.dll' version '2009.100.1600' to execute extended stored procedure 'sp_OACreate'. This is an informational message only; no user action is required.

I googled it and came to know that it is culprit of event. According to its BOL page

You call some Automation procedures from a SQL Server common language runtime (CLR) object, such as sp_OACreate. In this situation, SQL Server may unexpectedly crash.
Note This issue also occurs when a CLR object calls a Transact-SQL procedure that calls Automation procedures.
 It applies to SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, and SQL Server 2012. More detail can be found here.
Now we have to trace the call and modify it to avoid the accidental fail over.

Monday, March 24, 2014

The ALTER TABLE statement conflicted with the FOREIGN KEY constraint (Replication error)

We had configured transactional replication on a QA server. Publisher and subscriber were on same server. While implementing snapshot we were facing following error message as in replication monitor.

Command attempted:
if object_id(N'sys.sp_MSrestoresavedforeignkeys', 'P') < 0 exec sys.sp_MSrestoresavedforeignkeys
(Transaction sequence number: 0x000B0F2B0002BB5101C300000000, Command ID: 1)

Error messages:
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_ContractID2". The conflict occurred in database "%", table "%", column '%'. (Source: MSSQLServer, Error number: 547)
Get help: http://help/547
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_ContractID2". The conflict occurred in database "%", table "%", column '%'. (Source: MSSQLServer, Error number: 547)
Get help: http://help/547
Subscriber database had no constraint that was mentioned in the error message. Even publisher did not have this constraint. It was a mess that we were not able to find the constraint but it was causing failure to snapshot implementation. 

Towards Solution
While looking into details i came across following there tables that were being created by replication processes and contain the foreign key data of replication.
  • dbo.MSsavedforeignkeys
  • dbo.MSsavedforeignkeycolumns
  • dbo.MSsavedforeignkeyextendedproperties
I noticed that ghost enteries for our culprit foreign key were there in these tables. I deleted those entries manually and regenerated the snapshot. This time it was successfully generated and implemented.

Friday, January 31, 2014

dbmail error File attachment or query results size exceeds allowable value of 1000000 bytes

Last day i was working on a task which was meant to generate some data and send it as an email attachment to a specific email address. This process was going to be configured as a scheduled job in SQL Server agent. I completed the first part of task and data was ready as a CSV file to be send through email.

Problem Phase
In the second phase while trying to send the file as an attachment i got following error message.

dbmail error File attachment or query results size exceeds allowable value of 1000000 bytes

Amount of bytes shows that about 1 MB file size cap was there on attachment file that should be send through DBMail. In my case we were expecting the attachment file of size up to 25 MB.
So the questions are
  • Can we change the default limit on attachment size in database mail .i.e. 1000000 bytes?
  • If yes then where can we change this parameter?

Yes we can change the default limit on attachment size in database mail. And related to the procedure of changing this parameter following are the steps
  • Open SSMS and go to Management folder 
  • Right click on Database Mail and select Configure Database Mail

  • Click Next and select view or change system parameters radio button
  • Click next and you will be presented the parameters screen where you can view and modify the parameters as well
  • Here you can change the value against the Maximum File Size (Bytes) parameter
  • Specify the required size in bytes and click Next to save the configurations
Now you can send attachment up to mentioned max size here without any error.

What is the limit of Max value?
It is a valid question here at this point that what is the limit of value that we can enter as maximum file size in bytes? The answer is 2147483646.
Yes this parameter is of type int and can accept value under 2^31-1 (2,147,483,647) bytes. This mean that roughly you can get about 2GB size against this parameter. If you enter exceeded value for size then following error message will be generated

Account Retry Attempts, Account Retry Delay, Maximum File Size, Database Mail Executable Minimum Lifetime, and Maximum Number of Mails must have valid values to continue.

Value was either too large or too small for an Int32. (mscorlib)

Wednesday, January 29, 2014

Turn your self from DBA to a community DBA

Often i notice that many skilled and seasoned DBA lack a guide line to share their knowledge with the online DBA community. By using the several free services, tools and plugins it is now very easy to access the knowledge base of your interest and also to share your knowledge with the whole community.
I am utilizing the community aspects of DBA since 2008 and found it a very valuable feature for every IT professional. So i arranged webinar in my organization (Systems Ltd) to motivate and guide my fellow DBAs and Developers towards a path where they start utilizing the community content as well as actively add their own efforts towards the community through blogs, articles, webcasts, presentations and webinars etc.
Due to my own background my presentation was slightly inclined to SQL Server DBAs but i made sure that it should be general enough to be adoptable by all DBAs and Devs. Today the webinar went quite successful. DBAs and Devs across various Systems Ltd offices participated and showed their eagerness towards all the points mentioned in the webinar.
Following were the items discussed and click here to download the presentation.

Wednesday, December 25, 2013

Optimal run of DBCC CHECKDB on production servers

DBCC CHECKDB is SQL Server built-in utility command to analyze and report the database integrity in various aspects. It checks the logical and physical integrity of all the objects in the specified database by performing the following operations:
  • Runs DBCC CHECKALLOC on the database
  • Runs DBCC CHECKTABLE on every table and view in the database
  • Runs DBCC CHECKCATALOG on the database
  • Validates the contents of every indexed view in the database
It is a useful command and should not be abandoned due to its resource hungry execution. However under default deployment it may take significant time on production databases. With increasing time window, risk of performance degradation increases. You may have faced these issues several times on your production servers. Following short comings may be noticed in default implementation.
  • Error or information messages are not stored any where when scheduled job is run
  • Check is required to exclude any databases other than ONLINE from the target databases list
  • A strategy is required to minimize the activity on server
We may take few steps to make the process optimized and log the output. Following are points that are recommended for it.
  • Save log file of DBCC CHECKDB scheduled job output to disk. It may have just error messages if any or also the informational messages.
  • Make sure to exclude the databases whose CHECKDB is not required. CHECKDB is performed for ONLINE databases, so make sure to include the check in the script that will filter out all the databases that are not ONLINE.
  • Use parameter PHYSICAL_ONLY. It will greatly reduce the process time and will only consider the data as it is stored on physical layer.
  • We can check the logical structure on any fresh restored copy of same databases on other server. Logical checks are not dependent on machine or disk. It will totally eliminate the load of logical checks from production server.  This process will also make sure the validity check of backups.
Page Verification Settings and DBCC CheckDB
It seems here relevant to discuss the relationship between the Page verification check sum and DBCC CheckDB. PAGE VERIFICATION is a database level setting related to data recovery and integrity. Its value may be NULL, TORN_PAGE_DETECTION or CHECKSUM. For SQL Server 2005 and onwards CHECKSUM is the default option for new databases. CHECKSUM is more detailed and granular algorithm than TORN_PAGE_DETECTION. CHECKSUM covers all aspects of TORN_PAGE_DETECTION. However it may require more CPU resources as compared to TORN_PAGE_DETECTION. CHECKSUM Calculates a checksum over the contents of the whole page and stores the value in the page header when a page is written to disk.
A common misconception is that if we enable CHECKSUM for Page verification feature then we may skip the DBCC CheckDB command as CHECKSUM will be evaluating the data integrity.
It is not true at all. Page verification CHECKSUM is not alternate of DBCC CheckDB however it may effectively enhance the scope of DBCC CheckDB. Page verification CHECKSUM is a limited scope page level verification whereas DBCC CheckDB covers far more areas than that.
Page verification CHECKSUM is calculated/updated only when a data page is saved back to disk. If a data page gets corrupt after it has been saved to disk with CHECKSUM calculated on it then we will not get its report until it will be retrieved again. To verify through the calculated CHECKSUM we have to run DBCC CheckDB necessarily.
Technical detail of differences is not under scope of this statement. For better verification framework we should use both features.

Thursday, December 19, 2013

Different HA/DR technologies available in SQL Server

In a previous post i have discussed the scope and differences of High Availability (HA) and Disaster Recovery (DR). Recalling that concept now we are going to cover the features provided in SQL Server.
SQL Server provides following levels of HA/DR
  • Object level (Tables, views, USPs)
  • Single or Multiple Database Level
  • Server Level (multiple databases with server objects and services)
Before going for a plan build by combination of different technologies we will have a brief description of each of technology.

Used to mark and distribute the data and database objects specified. Replication articles may be distributed to multiple destinations. It is considered as object level recovery solution.

Replication Pros
  • Only specified data changes are  transmitted to target servers thus lowering the data travel volume
  • Replicated data provides redundancy that may be used for reporting or stand by purpose
  • Can also replicate the schema changes
  • Database can be in Simple recovery model
  • Database on secondary is both readable and writeable
  • Can choose to replicate only a subset of tables within the database
  • Since all databases are write able, you can create different indexes optimized for reporting environment
Replication Cons
  • Not suitable when whole database recovery is required rather than selective objects
  • Replication administration grows complicated when volume of data and number of DML transactions increases
  • A separate server role is required to make available and configure as distributor
  • Requires extra disk space for saving snapshots and replication transactions
  • Have to change application connection string to an available node if the node it is connected to becomes unavailable
  • Replication architecture builds an interim queue in the distribution database, causing higher latencies

Log Shipping
Log shipping remained a popular DR solution in previous versions of SQL Server. It is the process of automating transaction log backup at defined interval and automatically restores them on the standby server.

Log Shipping Pros
  • Can use compressed backups
  • Very good way to validate the transaction log backups you are already taking
  • Can run in a delayed restore mode to protect secondary from human error on primary site
  • Provides DB Level protection
  • Can have multiple secondaries
  • Very good option when other DR options are not possible or supported by a vendor since it is essentially a constant transaction log backup and restore sequence
  • Stand by server is not strictly required to be same as primary server
Log Shipping Cons
  • Data loss may be experienced 
  •  Downtime is expected 
  •  Database must be in Full or Bulk Logged recovery model 
  • No automatic failover
  • No automatic redirection 
  • Can only failover at database granularity - cannot group a set of databases to failover together 
  • Database on secondary not readable/writable during restore 
  • Cross database dependencies not supported

Introduced in SQL Server 2005 and is a good alternate for log shipping as it covers some of limitation that we faced in log shipping. Mirroring also provides us the option to choose among two modes.
High-safety mode supports synchronous operation. Under high-safety mode, when a session begins, the mirror server synchronizes the mirror database with the principal database as quickly as possible. Once the databases are synchronized a committed transaction is committed on both partners, at the cost of increased transaction latency.
High-performance mode, as soon as the principal server sends a log record to the mirror server, the principal server sends a confirmation to the client, without waiting for an acknowledgement from the mirror server. This means that transactions commit without waiting for the mirror server to write the log to disk. Such asynchronous operation permits the principal server to run with minimum transaction latency, at the risk of some potential data loss.
Mirroring Pros
  • Primarily a DR solution. Can be used as HA when used with synchronous/automatic failover options.
  • Protects against I/O subsystem failure on the primary server or datacenter
  • Log stream compression available for increased performance
  • Automatic failover is possible, requires a 3rd witness server
  • Automatic redirection to secondary is possible
  • Can choose between several mirroring and failover options: synchronous or asynchronous streaming, automatic or manual failover
  • DB Level protection
  • Does not require special hardware (SAN) and cluster ware
  • Automatic repair on primary server by referencing the mirror
Mirroring Cons
  • Database must be using the FULL recovery model
  • Database on secondary is not readable/writeable
  • Additional management overhead of third witness server
  • Deprecated in SQL 2012
  • Only 1 secondary allowed
  • Can only failover at database granularity - cannot group a set of databases to failover together
  • Application connection may be modified one time to include mirror redirection
  • System databases master, tempdb, and model cannot be mirrored
  • Only one secondary database is supported with database mirroring in SQL Server 2008
  • data lost is possible in asynchronous operation mode
  • Does not support FILESTREAM
  • Cross database dependencies not supported

Automatically allows one physical server to take over the tasks and responsibilities of another physical server that has failed. It is considered as pure HA solution and does not provide data recovery or redundancy features.

Clustering Pros
  • Uses time tested Windows Clustering technology. Advanced heartbeat, failover, quorum model, and quorum voting options available in the latest versions of Windows
  • Protects against Machine failures. ex. CPU/motherboard issues
  • Protects against OS failures. ex. blue screens
  • Protects against SQL Instance failures. ex. SQL hangs
  • Application connection strings are required no change
  • Works well with most other features  such as log shipping, replication,  and asynchronous availability groups
  • Manages external dependencies well upon failover
  • Supports rolling upgrades scenarios
  • Instance-level protection without data redundancy
  • No special requirements with respect to database recovery models
Clustering Cons
  • Does not maintain a redundant copy of the data and so does not protect against  an I/O subsystem failure
  • Not a DR solution
  • Must ensure nodes are maintained properly (patching levels should match)
  • A major outage like a datacenter power failure or failure of the network link to the primary data center is not addressed if all nodes are within the same datacenter.

SAN based Replication
SAN Replication resembles the database mirroring technology as both do data replication from one location to another. However while using SAN Replication we cannot automatically fail over.  SAN Replication can fail over automatically when used with a Windows Cluster and when you have software which is installed on the Windows Cluster and tells the storage array to fail over. However without a Windows Cluster in place, you will just have a server sitting waiting for the storage array.

SAN Based Replication Pros
  • Makes sure the disaster recovery through redundancy
  • Works effectively with clustering
SAN Based Replication Cons
  • Additional cost for SAN based storage
  • No automatic failover when used without clustering

SQL Server Always On Availability Groups
It’s like mirroring, but we get multiple mirrors for many more databases that we can fail over in groups, and we can query the mirror. An availability group supports a failover environment for a discrete set of user databases, known as availability databases that fail over together. An availability group supports a set of read-write primary databases and one to four sets of corresponding secondary databases. Optionally, secondary databases can be made available for read-only access and/or some backup operations. It is based on Windows Server Failover Clustering (WSFC) cluster that should not be confused with the SQL Server clustering

Always-On Solution Layers
A successful SQL Server Always-On solution requires understanding and collaboration across these layers:
Infrastructure level: Server-level fault-tolerance and intra-node network communication.
SQL Server instance level. A SQL Server Always-On Failover Cluster Instance (FCI) is a SQL Server instance that is installed across and can fail over to server nodes in a WSFC cluster. The nodes that host the FCI are attached to robust symmetric shared storage (SAN or SMB).

Database level: An availability group is a set of user databases that fail over together. An availability group consists of a primary replica and one to four secondary replicas. Each replica is hosted by an instance of SQL Server (FCI or non-FCI) on a different node of the WSFC cluster.

Client connectivity: Database client applications can connect directly to a SQL Server instance network name, or they may connect to a virtual network name (VNN) that is bound to an availability group listener. The VNN abstracts the WSFC cluster and availability group topology, logically redirecting connection requests to the appropriate SQL Server instance and database replica.

 Availability Groups Pros
  • HA (synchronous, local datacenter) and DR(asynchronous, remote datacenter) solution
  • Protects against I/O subsystem failure on the primary server or datacenter
  • Log Stream compression available for increased performance
  • Automatic Page Repair
  • Automatic redirection to secondary is possible via an AG listener
  • Automatic failover is possible, does not require a 3rd witness server, windows cluster is used for quorum Up to 4 secondaries
  • Can failover a set of databases as a group
  • Can choose between several mirroring and failover options: synchronous or asynchronous streaming, automatic or manual failover
  • Can offload T-Log backups  on secondary
  • Database on secondary can be set as readable (Readable or READ-INTENT secondary options)
 Availability Groups Cons
  • Database must be using the FULL recovery model
  • Database on secondary is not writeable
  • System databases master, tempdb, and model cannot be part of an AG
  • Cross database dependencies unsupported
  • Special configuration considerations when using Replication
  • Quorum model and node vote options should be configured to ensure an outage at the DR site will not cause the cluster to lose quorum
  • One DB fails then it failed over as a group
  • We can't choose between Synchronous and Asynchronous modes, it been used internally based on HA or DR scenarios
 If we are using SQL Server 2012 then it is the best choice for HA and DR provided the budget and infrastructure limits are OK. 

Combining different features
Now we may look at few combinations derived from above mentioned technologies. One or more of these may suit our requirements and budget.

Clustering with Mirroring
Keeping in mind the PROs and CONs of both these options we know that clustering lacks the DR features while Mirroring is all about data redundancy and recovery. So combining these may lead to a very good solution in terms of both HA and DR. The only problem with this solution is that if multiple databases are involved in failover, they might not be in same consistent state at a given point of time.

Clustering with SAN Replication
Keeping in mind the pros and cons of both these options we know that clustering lacks the DR features while SAN replication is all about data redundancy and recovery. So combining these may lead to a very good solution in terms of both HA and DR

Pros of Clustering with SAN Replication
  • Protects against I/O subsystem failure on the primary datacenter
  • Data disks can be synchronously or asynchronously mirrored
  • External dependencies handled well as in typical single site failover clusters
  • Instance-level protection WITH  data redundancy
  • SQL 2012 supports multi-subnet environments, eliminating the need for a VLAN
Cons of Clustering with SAN Replication
  • Additional expense of SAN replication technology
  • Additional complexity of managing and configuring SAN  replication technology
  • Quorum model and node vote options should be configured to ensure an outage at the DR site will not cause the cluster to lose quorum
Availability Groups with SQL Server Clustering
Availability groups emerged as our best choice while reviewing different options in previous sections. It is based on Windows Server Failover Clustering (WSFC) cluster and we may add SQL Server clusters to make sure the SQL Services availability in case of any outage or disaster. It would be a second layer of failover. AlwaysOn Availability Groups does not depend on any form of shared storage. However, if you use a SQL Server failover cluster instance (FCI) to host one or more availability replicas, each of those FCIs will require shared storage as per standard SQL Server failover cluster instance installation.

Always-On Availability Groups
It helps ensure availability of application databases, and they enable zero data loss through log-based data movement for data protection without shared disks.
Availability groups provide an integrated set of options including automatic and manual failover of a logical group of databases, support for up to four secondary replicas, fast application failover, and automatic page repair.

Always-On Failover Cluster Instances (FCIs)
Enhance the SQL Server failover clustering feature and support multisite clustering across subnets, which enables cross-data-center failover of SQL Server instances. Faster and more predictable instance failover is another key benefit that enables faster application recovery.