SQL SERVER – ReadOnly Databases and Notes Using Snapshots

This seems to be the third week that I am writing about ReadOnly databases, this blog is in continuation of last weeks notes. When I wrote the notes from last week, one of my blog reader said if it is possible to get the ReadOnly error without making the database into a ReadOnly database. This got me thinking and I couldn’t sleep that day. And a quick call to my friend to get some ideas – the answer was simple – using Snapshots.

I couldn’t control my excitement and this triggered me to write this special case scenario. As we execute the script, we will learn some of the behavior.

Let us start by creating our database which will be used for testing.

CREATE DATABASE [ReadOnlyDB]
CONTAINMENT
= NONE
ON  PRIMARY
( NAME = N'ReadOnlyDB', FILENAME = N'C:\Temp\ReadOnlyDB.mdf' , SIZE = 4024KB , FILEGROWTH = 1024KB )
LOG ON
( NAME = N'ReadOnlyDB_log', FILENAME = N'C:\Temp\ReadOnlyDB_log.ldf' , SIZE = 20480KB , FILEGROWTH = 10%)
GO
USE ReadOnlyDB
GO
-- Creating our table
CREATE TABLE tbl_SQLAuth (id INT, Longname CHAR(8000))
GO

Next let us go ahead and create a snapshot database out of our ReadOnlyDB.

-- Create a snapshot on our DB
CREATE DATABASE ReadOnlyDB_SS ON
( NAME = ReadOnlyDB, FILENAME =
'C:\Temp\ReadOnlyDB_data_1800.ss' )
AS SNAPSHOT OF ReadOnlyDB;
GO

Now that our snapshot database (ReadOnlyDB_SS) is created, it is important to know that snapshot databases are created as ReadOnly databases. The behavior is similar to creating ReadOnly Databases. To test this, let us try to insert some data into our Snapshot DB.

USE ReadOnlyDB_SS
GO
INSERT INTO tbl_SQLAuth VALUES (1, 'SQLAuth');
GO

Now we will be presented with the same familiar error (3906):

Msg 3906, Level 16, State 1, Line 25

Failed to update database “ReadOnlyDB_SS” because the database is read-only.

Even though we have not explicitly marked the snapshot database as Readonly we are getting this error. I was curious to know, what will happen if we try to mark the snapshot database as ReadOnly again. The error is self-explanatory.

-- Let us set the Snapshot DB as READ_ONLY
USE MASTER
GO
ALTER DATABASE [ReadOnlyDB_SS] SET READ_ONLY
GO

Msg 5093, Level 16, State 1, Line 31

The operation cannot be performed on a database snapshot.

Msg 5069, Level 16, State 1, Line 31

ALTER DATABASE statement failed.

If that was easy, what will happen if we try to make the snapshot database to read_write? The error is self-explanatory again here:

USE [master]
GO
ALTER DATABASE [ReadOnlyDB_SS] SET  READ_WRITE WITH NO_WAIT
GO

Msg 5093, Level 16, State 1, Line 37

The operation cannot be performed on a database snapshot.

Msg 5069, Level 16, State 1, Line 37

ALTER DATABASE statement failed.

Now that gives us two learnings, we cannot mark a Snapshot database as ReadOnly and nor can we mark a snapshot database as Read_Write enabled.

With these learnings in place, let us do the cleanup. Make sure to drop the Snapshot database before deleting the main database. So our cleanup script for this blog looks:

-- Clean up time
USE MASTER
GO
DROP DATABASE ReadOnlyDB_SS
GO
DROP DATABASE ReadOnlyDB
GO

Reference: Pinal Dave (http://blog.sqlauthority.com)

Hey DBA – Protecting a Proactive Attitude – Notes from the Field #069

[Note from Pinal]: This is a 69th episode of Notes from the Field series. Mike Walsh is an amazing person to meet and know. I am fortunate to be his friend. Every single time I meet him, I learn something as a human. He is always looking for an opportunity to improve the life of DBAs and Developers.

Being proactive is a good thing. There are few things which can not go wrong and being proactive is one it. Being proactive is not all about SQL scripts and deployments. It is also about attitude and mindset. If you know Mike, you must be aware of that he is the person who is well aware of human psychology. He knows what DBA and Developer things and how their attitude towards technology. I asked him one question – “How to protect a proactive attitude?” and database expert Mike Walsh decided to guide me with the answer of this question.

Read the entire story in his own words.


My posts lately here have been talking about being proactive. I am going to continue on that theme today, because it is that important. We perform a lot of WellDBA Exams™ – where we go into a client shop, review their existing setup, look at performance and write up our findings.

Each time we do one of these, we find the same types of findings almost everywhere. In fact we have a free resource available where we guide you through a checklist where you can evaluate your own environment against the 7 most common findings we encounter and see detailed information on what to do about those findings.

You can grab that free resource here.

In this post I want to zoom in on a proactive attitude again, and some ways to protect your proactive time. Then I will suggest a plan using that checklist to go through your environment and ensure the big things are taken care of.

Protecting a Proactive Attitude

I know I talk about this in just about every post I share here, but I am trying to cement this thought – a good DBA is a proactive DBA. You cannot just wait for a problem to arise, fight the problem, and then sit back and wait for the next problem.

I know you don’t do that. I know most DBAs don’t do that, at least not on purpose. But in this interrupt driven world, users and other IT teams are constantly clawing at their DBA for requests. These requests have to be met, because customers are expecting them to be done. But I challenge you that many requests don’t need to be done as fast as most DBAs do them.

You should develop a plan to protect your proactive time.

A few tips on how to preserve that time:

  • Schedule it! Make a block of time on your calendar each day, each week, whatever you can do. Guard that time and shut down your e-mail and IM clients (if you are allowed) and focus on doing proactive tasks to make your life easier.
  • Talk to management. When you are reactive you are spending more time, you are less effective and mistakes happen. If you get management to guard your time and people’s access to you for a bit for some proactive time, it will make you more efficient.
  • Work from home if you can. A lot of times when I was a full-time DBA – I could be more proactive when working from home, as the “drop by” visits can’t happen as easily that way.
  • Consider a WellDBA exam from Linchpin People, or a health check from another firm. Sometimes having an outside firm come in, audit your environment and tell you about your misses is the best way to get proactive time prioritized. Plus that approach helps you know where to focus your proactive time.
  • Just do it – Sounds harsh – but sometimes you just have to do it. If you don’t start trying to be more proactive, you’ll always be reactive and that is when mistakes happen. This is when we can come in and do an Exam and uncover the findings that the DBAs will say “yeah I know.. But I didn’t have time”

A Proactive Plan

Download our checklist, or find other SQL Server diagnostic scripts and approaches out there. There are several. Our checklist is less focused on scripts, and more focused on how, what and why you should care about these 7 areas, then suggests an approach to resolve the findings.

Here is the link again, it really is free and not some gimmick to get you on a “to be harassed” list.

Then start going through the checklist. We’ve ordered it in terms of our typical priorities.  For example – if you aren’t able to recover your environment, the other 6 points don’t matter as much.. Make sure you are really secure in each area, make sure you really understand the how and why in each area.

Move on to the next question and repeat the process. It is that simple. Some of the tasks will take more time than others to investigate. Some will take more time than others to fix. But this is a place to get started. And sometimes a place to start is all that is needed.

So, get some time set aside for being proactive, review your environment and get to the point where at least those 7 critical items are being taken care of! All the best on your journey!

If you want to get started with performance analytics and triage of virtualized SQL Servers with the help of experts, read more over at Fix Your SQL Server.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Storing Data in Hindi, Chinese, Gujarati and Tamil (or any other) Language in the Same Table

Long time ago, I have written a blog which talks about multi language support available with SQL Server.

SQL SERVER – 2005 – Multiple Language Support

I have been getting below questions on regular basics and it still baffles me:

  1. How do I pass Hindi parameters in SQL Server procedure as I want to insert a row with Hindi text?
  2. What is the collation I need to use if I have to store Tamil in SQL Server tables?
  3. I am inserting the data in Gujarati but while selecting I am getting “???”

Here are some basic rules which you may want to keep in mind. While storing Unicode data, the column must be of Unicode data type (nchar, nvarchar, ntext). Another rule is that the value must be prefixed with N while insertion.

If you are using a stored procedure then while passing the parameter to stored procedure make sure that N is used before the string. Also make sure the parameter declared within the stored procedure which are used for carrying those variable are also defined as Unicode.

Here is the sample script and the output which can make sure understand the things. I have used Google translate to do translation and I am not sure how accurate it is in Kannada, Tamil and Telugu.

use master
go
If db_id('SQLAuthority') is not null
drop database SQLAuthority
go
set nocount on
go
Create database SQLAuthority
go
use SQLAuthority
go
Create Table MultiLanguage_Will_Not_Work (i int, j varchar(10), k varchar(100))
go
-- Hindi
Insert into MultiLanguage_Will_Not_Work values (1, 'with N', N'मेरा नाम Pinal है')
go
-- Tamil
Insert into MultiLanguage_Will_Not_Work values (2, 'without N', 'என் பெயர் PINAL ஆகிறது')
go
Select * from MultiLanguage_Will_Not_Work
go
Create Table MultiLanguage (i int,j varchar(10), k nvarchar(100))
go
-- Gujarati
Insert into MultiLanguage values (1, 'with N', N'મારું નામ પિનલ છે')
go
-- Telugu
Insert into MultiLanguage values (2, 'without N', 'నా పేరు PINAL ఉంది')
go
-- Kannada
Insert into MultiLanguage values (3, 'with N', N'ನನ್ನ ಹೆಸರು ಪಿನಾಲ್ ಆಗಿದೆ')
go
Select * from MultiLanguage
go
use master
go
drop database SQLAuthority
go

Here is the output

Same would work for any language as well. It is important for us to know the basics and this blog post is one of many basics blogs that I plan to write. Do let me know which topic you would like me to revisit as part of this – back to basics series? More ideas you give me, more interesting the series can be.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Configure, Monitor and Restore Backups from Mobile & Web browser

So tell me if this is a fantasy or a reality: You are on a beach, sipping on your cocktail and looking at the sea when you get a frantic call from your boss, “One of the junior DBAs has just deleted all of the data from a critical database”. You say “Hold on”, switch to a browser on your smartphone, go to your dashboard on SqlBak.com and click a Restore button on your latest transaction log backup. In a few seconds you get back to your boss and say: “I have fixed it”. Your boss is stunned thinking you are a magician and makes a note to give you a raise and a bonus while you continue enjoying the beach…

Well, the beach and the pay raise are a fantasy, but restoring your database in a couple of clicks through just a browser on your smartphone is a service you can use today – it is called SqlBak.

This is how it looks like – you select a backup job, click Restore and the latest backup will be restored to your SQL Server:

Let’s see how to make it works

First – boring stuff: go through 10 seconds signup with your Facebook, Twitter or Google account. Then (and here’s the secret to a magic of SqlBak working through a browser) – download and install a small service program to run on your SQL Server computer – SqlBak Client.

To connect this program to your SqlBak account, enter your “Secret Key” from SqlBak Dashboard

into the SqlBak Client (it will prompt you for it):

 

The service program (SqlBak Client) has practically no interface and after connecting it to your SqlBak account you can forget about it (it will also auto-update). All of the configuration, monitoring and restore interface will be online through SqlBak.com, not through the program directly.

Now the fun part begins.

Go to the SqlBak Dashboard and click “Add New Job” button.  Select the computer where you have previously installed and connected the SqlBak Client and authenticate with the SQL Server – exactly like you’d do in SSMS.

Press Continue and you get to the Job Settings page. Select the databases you want to backup:

Then Press “Add Backup Destinations” to select where you want to store the backups – you have an option of sending it to you Local/Network folder, FTP, Amazon S3, Dropbox, Google Drive, MS OneDrive or Azure Storage:

I’ve selected to send backups to Amazon S3 and a Network Folder:

Lets schedule the full backup to run every 6h and differential backup every 1 hours:

By default it takes standard backup files and compresses them to zip – lets keep it like that:

You would probably want to receive a confirmation email at least on any failure – so fill out that field:

Now save the job and press Run Now to see if everything runs correctly:

You would see a log like this:

Now we can forget about it – it will email us if anything is wrong. If we get back to it in a few days – heres what we would see a backup history like this:

For each successful backup you have the Download and Restore buttons. Let’s click on the Restore buttons to restore one of the Differential backup. Select the destination to restore from (Amazon S3 or a network folder in our case):

You will see a restore progress bar:

In a few minutes your database is restored.

Absolutely brilliant! I know of no other product that would allow me to do the same from just a browser. It took quite a few pictures to describe the process, but in reality it takes just a few minutes and pretty self-explanatory.

In addition to backups this service would monitor that your SQL Server is alive and would email you if it goes offline – very useful.

Companies that want to provide Sql Server private label backups themselves can configure the service so it is branded to their company.

Some companies with strict security requirements may be hesitant of storing SQL Server login credentials with SqlBak. I’ve contacted the developers and they have told me that they are working on a version that would give user a choice of entering login credentials on the client – this way they would never leave the SQL Server.

Conclusion: I have seen many SQL backup programs before, but I am seriously impressed with the ease of using SqlBak – the only SQL backup software where you can configure, monitor and restore backups right from the browser.

If you enter the promo code Pinal2015 on the Sign Up page and you will receive $20 credit to your account.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – COLUMNSTORE and COLUMNSTORE_ARCHIVE data compression

In earlier blogs on “Fundamentals of Columnstore Index”, I took a simple approach on taking a tour of how to create a simple Columnstore index. Having said that, SQL Server 2014 supports columnstore and columnstore archival compression for columnstore tables and indexes. Columnstore tables and indexes are always stored with columnstore compression. Most data warehouses have some data that is frequently accessed and some that is accessed more infrequently.

You might be wondering the use case scenario for these commands. For example, in reality the data may be partitioned by date and the most recent data is accessed much more frequently than older data. In such cases the older data can benefit from additional compression at the cost of slower query performance. I talk to a lot of customers about this special feature:

  • To enable this scenario SQL Server added support for archival compression of SQL Server column stores from SQL Server 2014.
  • The archival compression option is enabled on a per-table or partition (object) basis; a column store can contain objects with archival compression applied and objects without. To allow for easy extensibility of existing on-disk structures, archival compression is implemented as an extra stream compression layer on top of Columnstore index implementation.
  • The Columnstore compression implementation transparently compresses the bytes being written to disk during the column store serialization process and transparently decompresses them during the deserialization process. Stream decompression is always applied when data is read from disk. Data is not cached in memory with stream compression.

The further reduction obtained by archival compression is substantial, ranging anywhere from 25% to 60%+ depending on the data. These are rough numbers based on what I have seen at customer interactions in the past.

  • Use COLUMNSTORE_ARCHIVE data compression to compress columnstore data with archival compression.

Let us look at how this can be achieved using TSQL commands.

To Apply Compression:

The simple TSQL commands to play with ColumnStore and columnStore Archive bits in single ALTER script:

-- Adding Archive bit for single Partition
ALTER TABLE tbl_myColumnStore
REBUILD PARTITION
= 1 WITH (DATA_COMPRESSION =  COLUMNSTORE_ARCHIVE) ;
-- Adding Archive bit for all Partition
ALTER TABLE tbl_myColumnStore
REBUILD PARTITION
= ALL WITH (DATA_COMPRESSION =  COLUMNSTORE_ARCHIVE) ;
-- Adding Columnstore for all parition and Archive for few Partition
ALTER TABLE tbl_myColumnStore
REBUILD PARTITION
= ALL WITH (DATA_COMPRESSION =  COLUMNSTORE_ARCHIVE ON PARTITIONS (1,3)) ;

Use COLUMNSTORE data compression to decompress archival compression. This resulting data will continue to be compressed with columnstore compression.

To Remove the Archive Compression

Similar to above commands, we can also use to remove our archival compression on specific partitions or on objects. A representative command looks like:

-- Turning Archive bit off to Columnstore for all Partitions
ALTER TABLE tbl_myColumnStore
REBUILD PARTITION
= ALL WITH (DATA_COMPRESSION =  COLUMNSTORE) ;

As I wrap up this blog, I would like to know how many of you out there are using the archival columnstore in your environments. How much compression are you getting and what is the space savings because of this? I would love to hear from your experience as we learn together.

Reference: Pinal Dave (http://blog.sqlauthority.com)

Interview Question of the Week #008 – Write Scripts to Convert String to Title Case or Proper Case

Just received a question in email:

“I just got out of the interview and I was asked to write scripts to convert a string to Title Case. I was also asked to refer to the website over here to validate my answer after I complete writing script.

Do you think it is not fair to ask such questions in an interview?”

Well, this time the question is not about writing scripts, but more about if this kind of questions is a good question for interview or not. The question is a bit open ended and my answer can be vague or full of philosophy of it depends. However, I will try to answer it bit more honest and clear.

I believe all questions are good question in the interview. It is never about the question’s validity, but more about quality of answer and attitude decides the capability of the user. If I ever face a situation where I am asked questions to write script to convert a string to title case, I would happily sit down and attempt to write it if I want the job and I am up for the challenge. If I do not know how to write it, I will honestly say I do not know and let the interviewer decide the next step. If you believe this kind of question is inappropriate for the job which you have applied, well, you can always mention that after you solve it.

Well – in my early career, I have written similar script and I have posted that on this blog post well. You can refer that in this blog post – SQL SERVER – UDF – Function to Convert Text String to Title Case – Proper Case. I strongly suggest that you read the comments as those are packed with the wealth of the information. Here is the script from the blog post.

CREATE FUNCTION udf_TitleCase (@InputString VARCHAR(4000) )
RETURNS VARCHAR(4000)
AS
BEGIN
DECLARE
@Index INT
DECLARE
@Char CHAR(1)
DECLARE @OutputString VARCHAR(255)
SET @OutputString = LOWER(@InputString)
SET @Index = 2
SET @OutputString =
STUFF(@OutputString, 1, 1,UPPER(SUBSTRING(@InputString,1,1)))
WHILE @Index <= LEN(@InputString)
BEGIN
SET
@Char = SUBSTRING(@InputString, @Index, 1)
IF @Char IN (' ', ';', ':', '!', '?', ',', '.', '_', '-', '/', '&','''','(')
IF @Index + 1 <= LEN(@InputString)
BEGIN
IF
@Char != ''''
OR
UPPER(SUBSTRING(@InputString, @Index + 1, 1)) != 'S'
SET @OutputString =
STUFF(@OutputString, @Index + 1, 1,UPPER(SUBSTRING(@InputString, @Index + 1, 1)))
END
SET
@Index = @Index + 1
END
RETURN
ISNULL(@OutputString,'')
END

You can use this script as follows:

SELECT dbo.udf_TitleCase('This function will convert this string to title case!')

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Script to Find Strings with Same Characters

One of my blog followers asked me if it is possible to check whether all the characters of a column value is same. For example if there are values like KKK and KKT, the value KKK should be the output as all characters are same.

There is a method without using WHILE Loop

Create the following data set

CREATE TABLE #TEMP(COL VARCHAR(20))
INSERT INTO #TEMP
SELECT 'AAAAA' AS DATA UNION ALL
SELECT 'NKKKKKKKKKK' UNION ALL
SELECT '32000000' UNION ALL
SELECT '11111111' UNION ALL
SELECT '1002' UNION ALL
SELECT 'OOOOO'UNION ALL
SELECT 'M'

Run the following code

SELECT
COL FROM #TEMP
WHERE
REPLACE(COL,LEFT(COL,1),'')=''

The result is

COL
--------------------
AAAAA
11111111
OOOOO
M

The logic is to get first character and replace that character by empty string in the column. If the final result is empty string, it means that all characters are same. Otherwise all characters are not same.

Reference: Pinal Dave (http://blog.sqlauthority.com)