SQL Server Database Recovery Archive

What to Do If Your SQL Server Database Misses Key

Posted September 19, 2017 By AuthorNV1

If your database is missing a master key, service broker will try to access it and will throw errors in SQL Server Error log. In this article, lets learn how to verify keys in your database and how to install keys if they are missing.

Entry in SQL Server Error log:

If your SQL Server error log has entries as mentioned below, then the target database is missing its master key.

Service Broker needs to access the master key in the database ‘%.*ls’. Error code:%d. The master key has to exist and the service master key encryption is required.

Applications that have queuing or messaging in their modules use SQL Server service broker. This helps applications to use components of SQL Server database engine and communicate across databases that are disparate.

Does your database have a key?

To check if your database has a key, you can use the sys table symmetric_keys. This table would return one row for each symmetric key.

Syntax:

Select * from [DatabaseName].sys.symmetric_keys

If there is no symmetric key in your database but still the service broker is trying to access a master key from your database, then the only solution is to create a database master key and it should be a symmetric key.

Syntax:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘<anypassword>’

Security

What To Do If Your SQL Server Database Misses KeySQL Server Service broker not just supports the communication, it also provides additional security by encrypting messages and blocking unauthorized connections from networks. It is very easy to maintain service broker. You can do it as part of your routine SQL Server database administration tasks. Conversation_endpoints, conversation_groups, conversation_priorities, message_type_xml_schema_collection_usages, remote_service_bindings, routes, service_contract_message_usages, service_contract_usages, service_contracts, service_message_types, service_queue_usages, service_queues, services, transmission_queue, dm_broker_activated_tasks, dm_broker_forwarded_messages, dm_broker_connections and dm_broker_queue_monitors are catalog view and dynamic management views with which you can get more info about SQL Server service broker.

Protecting key with a key

SQL Server Database Master KeyThe key_encryptions is a SQL server system catalog view with which we can easily identify the mechanism that is used to protect database’s symmetric keys. Symmetric keys of your database can be protected by any method listed below

  • Asymmetric keys
  • Certificates
  • Passwords
  • Other symmetric keys

Random bits of a string form a symmetric key. The number of bits in your symmetric key depends on the algorithm that is used during the creation of the key. If the algorithm is AES_256, then the bit count is 256.

Automate it

To avoid error messages related to service broker and database master keys, you might want to track symmetric keys in your databases. The catalog view has to be run from each database. If you have several hundreds of databases, then you can easily automate it using the query below.

DECLARE @keycheck varchar(1000) 
SELECT @keycheck = 'USE ? Select * from sys.symmetric_keys' 
EXEC sp_MSforeachdb @keycheck

Run this query from a new Query window and if your database has a symmetric key, the result table would have an entry in it otherwise it would be empty. Please note, though symmetric keys help you with security, they cannot help you with complete sql recovery as backups do. So never miss to have a backup plan for your SQL environment.

Author Introduction:

Neil Varley is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including repair pst and excel recovery software products. For more information visit www.datanumen.com

Be the first to comment

Replication is a widely-used SQL Server concept for maintaining copies of a database at different locations. The major advantage with replication is the Object level control. Though it is easy to set up, an unhealthy replication status might take time to fix. In this article, we will learn how to fix secondary databases that are out of sync.

Reasons

Secondary SQL Server Database In ReplicationWhen your subscriber is not receiving data from the publisher, any one from the below list could be the root cause

  • The published article has a filter on it and there were no recent transactions that passed the filter
  • Replication agents like SQL Server Agent, Snapshot Agent, Log Reader Agent, Distribution Agent, Merge Agent and Queue Reader Agent might be failing or might be in stopped state
  • Without a proper snapshot, you might have initialized a transactional subscription
  • Data might be deleted at subscriber by a user.

Step by Step

Step 1: Rows that were missing at the subscriber might not be replicated from the publisher as they did not meet the criteria for a filter. Replication types like Snapshot and Transactional support static filter whereas filter types like parametrized and join filter are supported by merge replication.

Step 2: An improper snapshot will result in the below error message. The snapshot agent must complete at the publisher before the snapshot was applied.

message_id 21075
language_id 1033
Severity 10
is_event_logged 0
Text The initial snapshot for publication ‘%s’ is not yet available.

Step 3: Key agents in a transaction replication are distribution and log reader. If either one of these agents is not running, data would not get delivered to the subscriber.

Step 4: To identify if records at subscriber got deleted by a user, you can run validation using the binary checksum. The other options it to run a validation on row count. `

From the replication monitor

What To Do If Secondary SQL Server Database In Your Replication Miss DataUsing the replication monitor, you can easily identify the sequence number of the transaction and ID of the command. You can also fetch those records from the replication table msrepl_errors. From these identified values, you must manually copy missing records from the publisher and insert those into the subscriber. Soon as you finish that, the distribution agent will continue to run and apply failed commands. Subscribers will get synchronized with the Publisher. During this task, you might end in turning on and turning off identity inserts into your tables.

Automate the monitoring

Automating the replication monitoring will help you to reduce the amount of time spent on troubleshooting. You can use MSreplication_monitordata to get the replication status. From this table, you can easily get Publishing server name, the name of publishing database and the agent name.

Select * FROM distribution.dbo.MSreplication_monitordata

You can create a SQL agent job to track replication errors. The table MSrepl_errors hold all replication related error text and the time during which those errors occurred.

Select * FROM distribution.dbo.MSrepl_errors

With snapshot replication, you always have a copy of your database with which you can quickly recover from SQL Server corruption

When Author Introduction:

Neil Varley is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including pst repair and excel recovery software products. For more information visit www.datanumen.com

Be the first to comment

Why SQL Server Returns NULL Info About Drives?

Posted September 13, 2017 By AuthorNV1

When you are trying to get information about drives on your server you might see that SQL is returning NULL values instead of actual values. In this article, we will understand the reason for such NULL values and how to prevent those.

Dynamic management function

Dynamic Management Functionsys.dm_os_volume_stats is a SQL Server dynamic management function with which you can

  • get your server’s volume information
  • attributes of server’s drives
  • status of free space

This dynamic management function requires two parameters and they are

  • Database id
  • File id

If you run the code below from a new query window, you can see file info of master database.

select * from sys.dm_os_volume_stats(1, 1);

The result set would look like this. The only drawback is that the space info is returned in bytes. You have to convert that into MB or GB.

database_id 1
file_id 1
volume_mount_point C:\
volume_id \\?\Volume{41b774f3-3cd9-11e5-8c1d-806e6f6e6963}\
logical_volume_name
file_system_type NTFS
total_bytes 157181538304
available_bytes 6606110720
supports_compression 1
supports_alternate_streams 1
supports_sparse_files 1
is_read_only 0
is_compressed 0

Combine it

 

This dynamic management function can be combined with the system table sys.master_files to retrieve file size of each database file, the total space and the free space of the drive hosting database files.

 

SELECT
   systable1.name, 
   systable1.physical_name,
   dynamicfunction1.volume_mount_point AS drive,
   dynamicfunction1.total_bytes AS total_disk_in_bytes,
   dynamicfunction1.available_bytes AS free_disk_in_bytes
FROM sys.master_files systable1
OUTER APPLY sys.dm_os_volume_stats (systable1.[database_id], systable1.[file_id]) dynamicfunction1

Why SQL Server Returns NULL Info About DrivesThis is a good method to monitor disk space on your SQL Server. At times, in the result set, you might see NULL values for disk info. In such cases, windows error log is the first place that you must check to fix this issue. If you search the windows application error log for the string “Access is denied”, you can see entries when the script was run. This indicates that the account with which the script was run does not have the necessary permission to read system information.

If the SQL Server service account is added to the Windows Administrator group, then the query above will start displaying values instead of NULL. The SQL Server service account should have at least read permission on the drive that is hosting the database files. Please also ensure that your SQL Server service account has VIEW SERVER STATE permission.

Automate it

You can create a log table to capture the result set from the query above. Add this query inside a SQL Server agent job and schedule it as per your requirement to collect and track disk space on your SQL Servers. You can then script out the SQL Server agent job, create Registered Servers using your SQL Server management studio and then run the job script from a new query window. This would deploy the job on all your target servers. You can then create linked servers and read information from each of these SQL Servers. HTML alert emails can be configured using TSQL to alert database administrators about drives that run on very low free space.

It is always a good practice to check your disk health. Because, an unhealthy disk can easily result in sql corruption

Author Introduction:

Neil Varley is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including damaged Outlook and excel recovery software products. For more information visit www.datanumen.com

Be the first to comment