Thursday, July 16, 2015

Msg 22049-Error executing extended stored procedure: Invalid Parameter

Hi Guys,

It's time to learn from the error which I went today. If you remember I have posted here a backup script. The purpose of the script is to take Database backup along with few options. The script has undergone through few changes now.

But here I want to discuss an issue which I faced during implementing this code on one of new environment.

Scenario:

  1. We wanted to configure this Database Backup Script in a new Server. There where few Databases on the Server. What we noticed was one of the Database name in the that Server was having 60 character (It was an Share Point Application Database).
  2. And due to that we were getting this (Msg 8152, Level 16, State 2, Line 1 String or binary data would be truncated) error message.
  3. For sake, we had changed the variable to Nvarchar(Max).
  4. After changing this variable there was another error popped up.Error was (Msg 22049, Level 15, State 0, Line 0 Error executing extended stored procedure: Invalid Parameter).
  5. Please see the Figure 1 snapshot.
  6. But in between Step 3 and 4; I was executing the SP from a Job and was unable to see this error message. As some of the error message were truncated. So I ran the SP manually from SSMS and I got to know about this error.
  7. Looking into the error, I came to know there was a problem in my last portion of the code.That means First portion 'To take DB Backup' was running fine and the Second part 'To delete the old Backup' was creating issue as I was using Extended Stored Procedure (i.e dbo.xp_delete_file)
  8. Not sure what was the issue after doing little research found an partial solution and then tested the code with some changes in variable and it was successful.
  9. Please see the demo code in Figure 2.  

Msg 22049-Error executing extended stored procedure: Invalid Parameter
Figure 1
Figure 2
Can you find any difference in Figure 1 and Figure 2?? (Of course apart from the Error)

The solution for the error was @path variable in the above code has datatype Nvarchar(Max) and if that is called from the extended Stored Procedure like in our case from dbo.xp_delete_file it throws the above error.

But if we pass the datatype as Nvarchar() like in Figure 2 and passed to Extended Stored Procedure the error is solved. 

So do remember if we are passing any variable to Extended Stored Procedure do defined a fixed value to the variable.

Keep Learning and Enjoy Learning!!!

Sunday, July 12, 2015

New Features in SQL Server 2016 - Part 1

Hello Friends,

I've given a short introduction to you on SQL Server 2016 here now, it's time to learn some of the much awaited new features for DBA's. You can consider this a qualified interview question as well.
I'll try to explain these features one by one here:

1. Stretch Database:  

This is the most interesting feature introduced by Microsoft in SQL 2016. The word "Stretch" means expand, which means you can expand your local (on-premise) Database to Azure (Cloud). So here we will see the combination of on-premise + Azure in one Database.
Stretch Database
Stretch Database

By default, this feature is disabled. We need to enable through SP_Configure 'Remote Data Archive'

Once it is enabled, you can configure this option under; 
Right Click on Database; Next Tasks; Next 'Enable Database for Stretch...

You need to supply the Azure Credential and select the table which you want to move.  

The whole purpose of Stretch Database is, we can move the old historic data to Azure by remote query processing. 

No changes is required from Application side. If a query is executed from an Application it will hit the on-premise Database and this on-premise Database will fetch the data from Azure and return the result set. (Only if that particular table data is moved to Azure)

Backup and Restore procedure of these Database would be little different from regular one. If you follow the regular 'Full' backup it will only consider the on-premise Database.

This would be consider as a good option to your environment because you can save the Hardware cost, Backup time would be less, Since historic data are moved out query processing for the current data will be improved. Also most important it's not a pre-requisite to have knowledge on SQL Azure  so any DBA who din't had luck to worked on SQL Azure should not worry.

There would be little Performance impact because it will fetch the data from on-premise as well as Azure. This will be the major drawback of this feature.

This feature must be only available in Enterprise Edition (Not sure yet). 

This was just a brief note about this Database. More information about Stretch Database are available here on msdn. You can also refer this site, it provides each and every details with GUI about Stretch Database.

2. Always Encrypted

Moving ahead with this interesting feature of Stretch Database, you might be definitely having question about data security, when there is any data movement from on-premise to Azure or vice-versa.

With this let me introduce to the next exciting feature of SQL 2016 which is Always Encrypted.
You might be well aware of TDE (Transparent Data Encryption) which was introduced by SQL Server 2008 in Enterprise Edition. It's use is to encrypt the data at rest with the help of certain keys (Master Key and Certificate). But as the data in Database is encrypted at the rest, the data is very much transparent; when the data is on fly (i.e. when data is moved from Application to Database or vice-versa). So man in middle attack is easily possible.This was the main drawback for TDE.

To overcome this drawback Microsoft has introduced new or can say extended security feature of TDE i.e "Always Encrypted" feature in SQL Server 2016.

Always Encrypted
Always Encrypted
Basically there is an enhancement in ADO.NET library which will protect the data in rest as well as motion.

So basically, the keys and certificate which we have create in SQL Server has to be deployed during application creation. This deployment of the Keys and Certificated will be handled by the ADO.NET library.

The figure on right will give an fair idea about the same. The data during fly will be encrypted and decryption will take place on the client side with the help of keys present in ADO.NET library.

This was just an overview on Always Encrypted, you can find more details here in msdn blog which is explained in depth with GUI. This is a very important feature for any organisation which handles critical data in there Database w.r.t to Auditing and Compliance. 

3. PloyBase: 

This one is the most important and exciting feature Microsoft has introduced ever and also the unexpected. Before this version any RDBMS product has the ability to interact with any other RDBMS (Through Linked Server).
But for the first time in SQL Server history it will interact with Non-RDBMS product as well. If you have gone through my previous post here, I already introduced to you about this Non-RDBMS product i.e. Hadoop.  

PloyBase
PloyBase
Looking into the future of Database world, Microsoft has also considered to introduce this feature in there upcoming product.
Earlier Apache Sqoop (It is a tool for data transfer between Hadoop Cluster and Relational Database) has the ability to interact with other RDBMS application but it has few limitations.
These limitations can be overcome with this new feature in SQL Server 2016.

If you have both SQL Server and Hadoop Cluster in your environment now it will not be necessary to learn Hadoop query to fetch data it can be easily just like Linked Server in SQL Server 2016.

How it will work?

  • Microsoft has introduced two new SQL Services which will be installed during installation of SQL Server. (Uncheck the box if there is no need of this Service)
  • We need to start by configuring SP_Configure parameter 'Hadoop Connectivity',0; by passing an appropriate value.
  • You can use the following configuration values:

0no Hadoop connectivity (default)
3Enable connectivity to Cloudera CDH for Linux
4Enable connectivity to Hortonworks Data Platform for Windows Server (HDP), HDInsight on Analytics Platform System, or HDInsight’s Microsoft Azure blob storage
5Enable connectivity to Hortonworks Data Platform (HDP)for Linux

Kindly refer to this link you will get a complete details about this feature.

These are just an overview of three new feature which is introduced by Microsoft in there new version of SQL Server 2016. We will see the another few features in our next post.

Till then stay tuned for more updates and feel free to share your comments here. Also do like and share if you liked the post.

Check this link for Part 2 of this post.

Keep Learning and Enjoy Learning!!!

Thursday, July 9, 2015

Introduction to SQL Server 2016

Hello Guys,

As many of you must be aware under Microsoft flagship, they have announced SQL Server 2016 in May’15 during Microsoft Ignite conference. So, the latest available version of MS SQL Server 2016 is CTP 2.1 (Community Technology Preview) from Jun’16. Soon (Somewhere in 2016; not yet confirm) the general version RTM (Release To Manufacture) would be available.  Also the code name for this version is yet to release.


SQL Server 2016
Introducing SSMS for SQL Server 2016

Following is a brief note on the how the Software is released in phases:
  • CTP > It stands for Community Technology Preview. CTP version is released before a newly developed software is roll out in the market. It is releases to improve the software or to fix bugs present.
  • RTM > It stand for Release To Manufacture. This is the first official version which released to the customers or clients for use. 
  • CU > It stands for Cumulative Updates which keep on releasing after RTM or SP is released. Its main purpose is to fix the bug found in the product.
  • SP > It stands for Service Pack. Bunch of CU forms a SP and it is cumulative. That means say there are two SP’s are released SP1 and SP2. You can directly apply SP2 no need to apply SP1 and then SP2.
Click here to find more on release date and the version number for overall Microsoft product available from SQL Server 7.0 till SQL Server 2016.

With this information now we will see what new features are available in MS SQL Server 2016. The following diagram will give you an idea about some of the new feature in SQL Server 2016.

New Features in SQL Server 2016
New Features of SQL Server 2016

We will see in detail about these new features of SQL Server 2016 on next post here. Till then........

Keep Learning and Enjoy Learning!!!

Thank you.

Wednesday, July 1, 2015

MS SQL Server Along With Hadoop

Thank you Guys!!!

Your appreciation and encouragement on my post of "BIG DATA" under the title "Apart From SQL Server...".

From now onward we will learn both "SQL Server" and "Hadoop" Administration under this same blog of mine. So definitely first I'll update my blog title to "All about MS SQL Server And Hadoop Administrator" rather than "All about MS SQL Server DBA".
Here I will share my real world experiences about MS SQL Server as well as my learning experience about Hadoop Administrator.

We saw a tremendous revolution in past decade around us with the help of IT and of course there is much more in coming years. If you look back there were hardly or you can count the e-commerce sites in your finger tips but today there are dozens of e-commence going around. These e-commerce business will generate bunch of Structure as well as unstructured data. Structure data's can be easily managed by RDBMS but to maintain and improve these unstructured  data we need a large set of Cluster structure Servers which will handle these type of data. Most of us today are not aware to this technology or just have heard the term "BIG DATA".

I've already posted here that how I came to know about this technology. Now, here is the reason why I'm keen to learn Hadoop.



As a MS SQL Server DBA I always wanted to learn some different related technology as well. Since I'm having knowledge on RDBMS and learning Oracle which is also again an RDBMS. So thought of learning Non-RDBMS product and I landed to learn Hadoop.

So those you want to learn SQL Server or Hadoop or both can bookmark my link. I'm afraid as a new beginner to Hadoop I might go wrong sometimes but trust me I'll try to avoid such mistakes by understand the topic well before writing any blog on Hadoop.

Feel free to correct me if I'm wrong on any part not just with Hadoop but also with MS SQL Server posts.

So guys, let's start learning a new upcoming technology along with our own MS SQL Server.

Keep Learning and Enjoy Learning!!!

Thank you!!!