How to repair corrupted WMI Repository?

Windows Management Instrumentation (WMI) is the infrastructure for management data and operations on Windows-based operating systems. WMI is the Microsoft implementation of Web-based Enterprise Management (WBEM), which is an industry initiative to develop a standard technology for accessing management information in an enterprise environment.

The ability to obtain management data from remote computers is what makes WMI useful. This feature makes it indispensable for many software applications including SQL Server. SQL Server Configuration manager and SSRS Configuration manager are few examples which are dependent on WMI. Corruption of any type to WMI could be critical to all these applications.

In this post I’ll document a workaround to recreate WMI Repository.

The commands mentioned below are DOS commands to repair & recreate a corrupted WMI Repository. You can execute these steps manually or via Batch file whatever you wish:

net stop winmgmt

CD C:\windows\system32\wbem

rename repository repository.old

net start winmgmt

In the commands above we are:

  1. Stopping WMI Service
  2. Renaming Repository Folder
  3. Restarting WMI Service

 

To avoid such corruptions Microsoft released one Hot Fix, apply the appropriate Hot fix available for your version of Windows.

 

Hopefully this will help you.

Regards

Sarabpreet Anand

How to find the Isolation level being used by a Database?

Hi Friends,

Recently i was preparing a Dashboard sheet for one of our critical Box. The Client was having a long list of  Requirements and one odd requirement was to embbed the Isolation level being used for every Database. This was a unique requirement for me and i never knew how to get this, but within 5 minutes of searching i came to know about an interesting Command which returns this info along with other useful stuff.

The command is
DBCC UserOptions
This command also tells you about certain DateFormats and ANSI Settings.

The below pic shows an output of one of my Database where the isolation level is set to Snapshot

1_SQL_Server_How_to_find_the_Isolation_level_being_used_by_a_Database

 

Hope this will help you in some way.

 

Regards

Sarabpreet Anand

Compare Data in Two SQLServer Tables without Any 3rd-Party Tool

For comparing data in tables you don’t need any 3rd party tool, SQL Server ships with the tablediff utility which can be used to compare the data in two tables. You can use it for troubleshooting replication. This utility can be used from the command prompt or in a batch file to perform the following tasks:

  • A row by row comparison between a source table in an instance of Microsoft SQL Server acting as a replication Publisher and the destination table at one or more instances of SQL Server acting as replication Subscribers.
  • Perform a fast comparison by only comparing row counts and schema.
  • Perform column-level comparisons.
  • Generate a Transact-SQL script to fix discrepancies at the destination server to bring the source and destination tables into convergence.
  • Log results to an output file or into a table in the destination database.

Lets see how to use it…

For testing i’ve created two tables Table1 and Table2 in DatabaseA and DatabaseB respectively on my local Instance, Then inserted few records in each table.

–On ServerA

create database DatabaseA

go

use DatabaseA

go

create table Table1(

tid int primary key,

tname varchar(20))

go

insert into Table1 values(1,’Sarab’)

insert into Table1 values(13,’Amit B’)

insert into Table1 values(18,’Amit K’)

insert into Table1 values(21,’Sachin’)

–ServerB(I’ve created this on my local instance however, You can create this on another server\Instance)

create database DatabaseB

go

use DatabaseB

go

create table Table2(

tid int primary key,

tname varchar(20))

go

insert into Table2 values(1,’Sarab’)

insert into Table2 values(13,’Amit B’)

insert into Table2 values(12,’Rishu’)

insert into Table2 values(18,’MV Priyank’)

The source table in the comparison must contain at least one primary key, identity, or ROWGUID column.

Now if you query both the tables you can see that I’ve purposely inserted 2 same records in both the tables (tid 1 & tid 13 marked Blue in the screenshot) and inserted same primary key with different values for tname (tid 18 marked Red in the screenshot) to show you how TableDiff Utility consider these scenarios.

1_SQL_Server_Compare_the_Data_in_Two_Tables_without_Any_3rd_Party_Tool

 

Location of the Utility File:
The TableDiff.exe Utility can be found at this location: C:\Program Files\Microsoft SQL Server\100\COM

Note – In case you are using SQL Server 2005 replace 100 with 90.

To start the action launch command prompt and locate the directory having TableDiff.exe utility, then execute the command below:

TableDiff.exe -SourceServer . -SourceDatabase DatabaseA -SourceTable Table1 -DestinationServer . -DestinationDatabase DatabaseB -DestinationTable Table2 -et difft1 -f d:\my_Diff_File.sql

2_SQL_Server_Compare_the_Data_in_Two_Tables_without_Any_3rd_Party_Tool

 

Parameters used:

Most of the parameters are self explanatory, the odd ones I am explaining below:

-et table_name
Specifies the name of the result table to create. If this table already exists, -DT must be used or the operation will fail.

-f [ file_name ]
Generates a Transact-SQL script to bring the table at the destination server into convergence with the table at the source server. You can optionally specify a name and path for the generated Transact-SQL script file. If file_name is not specified, the Transact-SQL script file is generated in the directory where the utility runs.
To get the complete list of all supported parameters visit: http://msdn.microsoft.com/en-us/library/ms162843.aspx

When -et parameter is used, the result table will be created in Destination Database. This table returns all rows where the data got mismatched based on primary (or Unique) Key including the script to resolve the conflict. It also returns all those rows which are only present at source or destination.
Here’s a screenshot of the output:

3_SQL_Server_Compare_the_Data_in_Two_Tables_without_Any_3rd_Party_Tool

 

And -f parameter will generate a file with T-SQL Script named my_Diff_File.sql which will have all the commands to resolve all the conflicts. The Output looks like this:

— Host: .

— Database: [databaseB]

— Table: [dbo].[table2]

DELETE FROM [dbo].[table2] WHERE [tid] = 12

UPDATE [dbo].[table2] SET [tname]=N’Amit K’ WHERE [tid] = 18

INSERT INTO [dbo].[table2] ([tid],[tname]) VALUES (21,N’Sachin’)

Points to consider:

  • The tablediff utility cannot be used with non-SQL Server servers.
  • Tables with sql_variant data type columns are not supported.

Permissions Required:

  • To compare tables, you need SELECT ALL permissions on the table objects being compared.
  • To use the -et option, you must be a member of the db_owner fixed database role, or at least have CREATE TABLE permission in the subscription database and ALTER permission on the destination owner schema at the destination server.
  • To use the -dt option, you must be a member of the db_owner fixed database role, or at least have ALTER permission on the destination owner schema at the destination server.
  • To use the -o or -f options, you must have write permissions to the specified file directory location.

Security Note: When possible, supply security credentials at runtime. If you must store credentials in a script file, you should secure the file to prevent unauthorized access.

 

Have Fun!

Regards

Sarabpreet Anand

What all changes require a restart of SQL Service?

Question of the day: What all changes require a restart of SQL Service?

Let’s say you get a request to change a SQL Server setting and you don’t know whether it requires a restart of SQL Service to take into affect or not, now what will you do?

You’ve left with following options:

  1. Ask someone you know (You think this person must know the answer but what if he is also guessing?)
  2. Search for it, now a days you find everything on internet (but you must hit the right keywords and someone should’ve written that for you)
  3. Ask SQL Server ( Now that’s interesting)

 

Q. How to ask SQL Server if it requires a restart of Service to take a change into affect?

SQL Server 2005 and later versions are equipped with a catalog view named sys.configurations, you can query this view to get the answer. This view gives a detailed info on all server level configuration changes.

The View provides very detailed information on server level changes like:

configuration_id
Unique ID for the configuration value.

name
Name of the configuration option.

value
Configured value for this option.

minimum
Minimum value for the configuration option.

maximum
Maximum value for the configuration option.

value_in_use
Running value currently in effect for this option.

description
Description of the configuration option.

is_dynamic
1 = The variable that takes effect when the RECONFIGURE statement is executed.

is_advanced
1 = The variable is displayed only when the show advanced option is set.

If the value in is_dynamic column for any configuration is 1, that means the change is dynamic in nature and it doesn’t require SQL Server Service restart to take that change into effect, otherwise if the value is 0 that means restart is required.

You can query this view just like any other normal table\view.

Select * from sys.configurations

1_SQL_Server_What_all_changes_require_a_restart_of_SQL_Service

Hope you learned something new, do leave us a comment.

 

Regards

Sarabpreet Anand

Introduction of SQL Server on Azure VM (IaaS)

SQL Server on top of a VM running in Azure is yet another example of Infrastructure-as-a-Service (IaaS). This is just like running SQL Server on a VM in your own data center, the only difference here is you don’t have to own all that hardware and maintain it, all the hardware is owned, hosted & maintained by Microsoft only. The best part is you still get an option to bring your own license of SQL Server if you have one – means if you already have a SQL Server license you can (migrate your license to Azure) use the same license on cloud. If you use this model you’ll be only charged for (compute + storage cost)

Even if you don’t have a SQL License don’t you worry, you will get an option to select a pre-installed SQL VM image from gallery while creating your VM and you’ll be charged for your (compute + storage cost + SQL License cost) means you pay only what you use – As per your usage J; the billing is per minute basis.

Just like any other service here also you’ll get multiple service tiers, each with different capabilities and SLAs. I’ll discuss more on this in my later blogs.

While you will start using SQL Server on Azure VM, but it will be same On-Premise SQL Server product (Edition & Version of your own choice) with the same features and capabilities. You don’t have to compromise on any feature.

You have multiple ways of migrating data from on-premise to SQL Server running on Azure VM.

The icing of the cake is getting an option to configure AlwaysOn Availability group by using multiple VMs in Virtual Network.

Hope you enjoyed the post, feel free to leave a comment. 🙂

Thanks,
Sarabpreet Singh Anand
Subscribe now to get latest Tips\blog posts in your Inbox

Auto-Truncate Log in Full Recovery Model

FULL Recovery model: This means that all database changes are fully logged and ideally the log records should stay in the log file until the log records are safely stored away\backed up in a Transaction Log backup. As per MSDN:  If a DB is in Full Recovery Model, then No work is lost due to a lost or damaged data file & we Can recover to an arbitrary point in time (for example, prior to application or user error) provided we have all backups in place.

But unfortunately this is not completely correct. Your database can be in Auto-Truncate Mode while the recovery model is set to FULL.

Now what is Auto-Truncate Mode: This means your DB is still working as if it is in SIMPLE Recovery Model. or in other words, the log file will be truncated every time a CHECKPOINT is run against the database. CHECKPOINT happens at regular intervals. Log Truncation means that inactive parts of the log file will be overwritten if the log space is needed.  Log Truncation does not physically shrink the log file, but it can keep it from physically growing.

In this article i will explain you the different conditions which changes this behaviour & let you know how to avoid such pitfalls and make your database fully recoverable. This all is internally related to LSN.

 

Now What is LSN?

Every record in the SQL Server transaction log is uniquely identified by a log sequence number (LSN). LSNs are used internally during a RESTORE sequence to track the point in time to which data has been restored. When a backup is restored, the data is restored to the LSN corresponding to the point in time at which the backup was taken. Once the LSN Chain (Log Chain) is broken, you can never do point in time recovery. (there are few exceptions to this, i’ll explain this later)

There are four conditions in which Auto-Truncate Mode gets enabled:

  1. By switching the recovery model of your database to simple.
  2. Using BACKUP LOG command with NO_LOG | TRUNCATE_ONLY option. (Note:  The BACKUP LOG … WITH NO_LOG | TRUNCATE_ONLY option is no longer available in SQL Server 2008)
  3. If you have never taken a FULL backup of your database, log backup of that database will also be not available. Your database is in   Auto-Truncate Mode until the first FULL backup of the database is taken.
  4. If you have not taken a FULL\Differential backup after the last time you switched from SIMPLE to either FULL or BULK_LOGGED recovery model.

You should also be aware that taking a log backup will truncate the log, but taking a FULL database backup will not truncate the log. (I received many queries from DBA’s asking this specific question)

 

How to check whether your database is in Auto-Truncate Mode or not?

You can query a system view called sys.database_recovery_status. If the value of last_log_backup_lsn column of this view is NULL it means the database is not maintaining a sequence of log backups and it is in Auto-Truncate Mode:

Note: To see the row for a database other than master or tempdb, you should have one of the following permission:

  • You should be owner of the database.
  • You should have ALTER ANY DATABASE or VIEW ANY DATABASE server-level permissions.
  • You should have CREATE DATABASE permission in the master database.

SELECT @@servername,db_name(database_id) as ‘database’, last_log_backup_lsn FROM sys.database_recovery_status(nolock)

 

How to resolve this?

To resolve this you need to either take a FUll or Differential backups to bridge a gap & recreate a new LSN Chain.

 

To replicate the same and test following is the step by step script.

CREATE DATABASE sarab_12

GO

— Check the status of auto-truncate option after first creating Database

SELECT db_name(database_id) as ‘database’, last_log_backup_lsn FROM sys.database_recovery_status WHERE database_id = db_id(‘sarab_12’)

GO

–Now take a full backup of sarab_12 Database

BACKUP DATABASE sarab_12 TO disk = ‘C:\sarab_12.bak’

GO

— Check status of auto-truncate option after taking full backing up of sarab_12 Database

SELECT db_name(database_id) AS ‘database’, last_log_backup_lsn FROM sys.database_recovery_status WHERE database_id = db_id(‘sarab_12’)

GO

–Try to truncate the inactive portion of sarab_12 database without taking backup of the same

BACKUP LOG sarab_12 WITH TRUNCATE_ONLY

GO

— Check status of auto-truncate option after truncating log

SELECT db_name(database_id) AS ‘database’, last_log_backup_lsn FROM sys.database_recovery_status WHERE database_id = db_id(‘sarab_12’)

GO

–Clean your instance

DROP DATABASE sarab_12

GO

 

APPLIES TO
MS SQL Server 2000 till 2014

 

Regards

Sarabpreet Anand

What is SQLAzure (Azure SQL Database)

SQLAzure or Azure SQL Database is a database-as-a-service cloud based offering (Microsoft Azure); this offers a fully managed relational Database solution which is highly compatible with current tools to manage SQL Server. This also offers built-in high availability & scalability based on requirements. You don’t have to worry anymore about the infrastructure or the management of the same, just create a Database and start using its features – that means rapid development and at the same time it accelerates your time to market.

Let me walk you through couple of features\offerings which will help you understand it better:

SQLAzure DB is available in Basic, Standard and Premium tiers. Each tier offers its own level of performance and capabilities to support tiniest to highly transactional database workload. The amazing part is you can change service tiers as your database requirements grow that too without any downtime; isn’t it amazing! This makes more sense to those scenarios where you can predict the peak usage and off-peak usage and based on the demand you can turn the knobs and manage cost.

Let’s say you can’t predict the actual usage or the usage pattern is not defined & you don’t want to waste resources\increase cost by over-provisioning then don’t you worry Elastic database pools are there to help you out.

When it comes to performance measure you can very well do that in SQLAzure and the unit of measure is a Database Transaction Unit (DTU) which represents the relative power of database to complete a database transaction.

You get all of these things without compromising anything over security; yes like On-premise SQL Server SQLAzure provides solid database security from limiting access to protecting data at a granular level. This gives you peace of mind & you can put your most confidential data over SQLAzure without any doubt.

How can we forget to mention the SLA (Service level agreement) and uptime; like any other service vendor Microsoft provides SLA which describes its commitments for uptime and connectivity; Azure provides 99.9% availability for its Web/Business tiers and 99.99% availability SLA for Basic, standard and premium tiers; each service tier has its own set of Business continuity features to choose.

To know more about SQLAzure stay tuned!

Feel free to leave a comment. 🙂

Thanks,
Sarabpreet Singh Anand
Subscribe now to get latest Tips\blog posts in your Inbox