SQLCMD and SET QUOTED_IDENTIFIER

Posted on by

Let’s talk about something simple today. You might already be knowing this, but if you haven’t seen/noted this before then this post will definitely help you.

I was working on requirement wherein I had to simulate a workload which will do massive inserts to a table which had computed column. I tested my T-SQL script to create the workload via SSMS and it worked like a gem.

Yay, all good and that’s the end of this post!  No, not really.

Things started little interesting when I used SQLCMD as a test case to call the same T-SQL. I got an error which said:

Msg 1934, Level 16, State 1, Server <Name>, Line 4
INSERT failed because the following SET options have incorrect settings: ‘QUOTED_IDENTIFIER’. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.

sqlcmd

 

That’s interesting because my commands worked just great when I used SSMS earlier! What’s wrong with SQLCMD now?

I quickly realized that this has to something to do with query execution options, and cross checked SSMS options.

sqlcmd2

 

So, SET QUOTED_IDENTIFIER is ON for SSMS connections, cool and that’s the reason why my commands where running cool earlier.

I quickly checked books online article for sqlcmd and found this. [Well, this is a very good article which is written for sqlcmd by those technical writers at Microsoft]

sqlcmd3

So, adding -I to sqlcmd as a query execution option fixed my problem.

sqlcmd4

Conclusion: 

Little tips and tricks like these always surprises me when I’m dealing with SQLServer, and it helps me learn something or the other daily.

Thanks for reading and keep watching this space for more. Yep, lot of SQL2014 goodness coming in next few days.

Advertisement

#SQLPASS #Summit14 Day 2 Keynote Highlights !

Posted on by

This was one of those days I was waiting to happen. You might be wondering whats special? Yes, this day is very special because Rimma Nehme will be giving a keynote on  Cloud Databases 101.

Rimma Nehme is one of those super smart people who works for Microsoft Gray Systems Lab along with the very special Dr David Dewit.

Day 2 started with a talk by Adam Jorgenson, PASS Exec Vice President of Finance.
Adam started talking on how PASS operate, from where money comes and how it’s used? The key thing to note is that PASS is in good financial state with good amount of rainy day fund.
IMG_3247

Adam on stage !

Thomas LaRock was back on stage on day 2, and he started talking by thanking outgoing board member Sri Sridharan. Kudos to Sri for all his efforts. He also introduced the new board member Grant Fritchey.

Next up was Denise McInerney,VP Marketing and she talked about her experience of PASS and the career changing decisions she took. Denise mentioned how PASS changes people’s career.

She thanked the volunteers for their hard work, and the winner of PASSion award was announced and the winner was Andrey Korshikov.

Denise confirmed that Summit will be back in Seattle Oct 27-30 2015, and that’s great news !!!

Crowd was eagerly waiting for the keynote to start and Denise requested Rimma to take over the stage.
Rimma talked little about her and cracked some jokes about Dr. David Dewit. She added that she is a great fan of SQLServer/PASS community.
IMG_3249

Rimma Nehme on stage giving Day 2 Keynote on Cloud Database 101

The agenda of the talk was based on :
  1. Why Cloud? 
  2. What is a Cloud DB?
  3.  How they are built?
  4.  What is my role as a DBA?
When people like Dr. David Dewit or Rimma talk, they come with great simple slides with lot of information on them. They also tend to use real life analogy to explain things, which is really nice.
Here is an abstract of what Rimma talked:
Why cloud computing?
Why cloud? (Also known as 5th utility! )
Cloud is special because of its characteristics:
  • On demand self-servicing.
  • Location transparent resource pooling.
  • Ubiquitous network access.
  • Rapid elasticity.
  • Measured service pay per use.
History of cloud :
1960’s (Super computers/Mainframe) ,1990(Sales force) ,2002(AWS), 2006(EC2), 2008 – present (Azure, Google Apps).
Where cloud lives: Data Centers(Of course !), and the move to the Next generation Data Centers.
Data center efficiency is measured in terms with PUE (Power Usage Effectiveness) :
 IMG_3253
PUE =
Total Facility Power /  IT Equipment power.
Traditional PUE 2.0 vs Modular PUE 1.15(cooling 0%).
Microsoft Data-centers :

Over 100 DC in more than 40 countries, More than 1 Million Servers.

IMG_3254
What is a Cloud DB
 
Platform As a Service, Infrastructure As a Service ,Software As a Service.
Pizza analogy of cloud:
IMG_3256
What kind of cloud databases are out there: We have Managed RDBMS, Managed No SQL, Cloud Only DBaaS. Analytics as a Service, Object Store.
IMG_3257
How they are build
 
Why Virtualization : Put under-utilized resources in use. With a drawback that direct access to resources will be lost.
Multi Tenancy : 4 common approaches. Private OS,Private Process/DB, Private Schema,Shared Schema.
IMG_3261
Inside Azure SQL DB :
The concept is each account as 1 or more servers, each server has one or more databases, each database has one or more objects.
IMG_3264
Everything designed with HA in mind.
Layers of abstraction : Client layer, services layer,platform later, infra layer.
Network topology :
IMG_3265
MY role as a DBA
 
Last but not least Rimma talked about the role of DBA for Cloud.
IMG_3267

Rimma wrapped up Cloud 101 talk by requesting Dr.Dewitt to join her on stage, and there was a standing ovation by the crowd.

IMG_3269

 Conclusion: 

We #SQLFamily is expecting Rimma to be back next year to give a more in-depth talk on Cloud and we are looking forward to it.

Thanks for reading, and keep watching this space for more !

#SQLPASS #Summit14 Day 1 Keynote Highlights !

Posted on by

IMG_3214

PASS President Thomas LaRock on stage !

PASS President Thomas LaRock kicked off the summit keynote.

 

Tom talked about the history of PASS and its growth curve. Positive numbers all over the place !

One of the key things to note is that PASS has delivered 1.3 million hours of training which is pretty impressive.

 

 

Next up was Microsoft Corp Vice President TK Ranga Rengarajan talking about Evolving Microsoft Data Platform.

Ranga talked about the explosion of data/data sources and its positive trend.He stressed on the evolving Data culture and how data professionals can lead this change.Microsoft data platform is capable enough to manage all the data requirements.

IMG_3218

Ranga Rengarajan on stage !

Goal is do more,achieve more via :

Capture Diverse Data

AzureDocDB, AzureHD Insight, Analytics Platform System, Azure Search.

There was a demo by Pier1 Imports on Azure Search, and the demo explained the value of Azure Search capabilities
and its power.

Pier 1 gave another demo of their Promotions app/page which uses AzureDoc DB.

They also achieve scaling using SQLDatabase sharding capabilities.

Pier 1 is also leveraging Geo Replication for its premium SQLAzure Database.

Achieve Elastic Scale

SQL2014 with Win 2012 R2, SQLServer in Azure VM’s, Azure SQLDatabase.

Max Performance

Azure SQLDatabase, SQL2014.

Simplify with Cloud

Hybrid scenarios with SQL2014, Azure as a data lake.

Ranga also talked about various businesses which are leveraging SQLServer to achieve their data/scaling requirements.

Some great numbers for SQLServer this year :

1.2 Million downloads of SQL2014. 1M Azure SQL DB’s deployed.

Major Announcements :

Azure SQL Database Major update : Leap in TSQL compatibility, Larger index handling, Parallel queries, Extended events, In memory Col Store.

Technology Sneak Peak : Pier1 Imports

Pier1 Demo was focused on In memory OLTP/ Col store on Azure SQLDatabase. 13,000 transactions/second were fired up and at the same time a reporting workload was ran and performance was awesome .

They also demonstrated the ability to stretch On Premises database to the cloud.

Next up was Joseph Sirosh, Microsoft CVP Machine Learning & Information Management.

Joseph talked about Azure Machine learning and other capabilities.

IMG_3231

Joseph making sure the crowd is engaged !

 

Again the goal is Do more,achieve more, Put data to work  :

Understand the past
Azure Data Factory

Demo from Pier1 demo on trend/stream analysis using Kinect/Azure Data Factory. Data from Kinect sensors are streamed
to azure and data is analyzed real-time.

Analyze the present
Azure Stream Analytics

Predict whats next
Azure Machine Learning

Demo by Pier1 Imports on machine learning. Pier1 app allows customer to login to the app and the customers
shopping trend/taste data is checked from Azure and results are shown.

Next up was James Phillips , Microsoft GM Data Experiences

James talked about bringing insights to more people – faster.

IMG_3237

James talking about Power BI and Machine Learning !

 

Goal again is Do more ,achieve more :

Simplify data discovery 
     
Deliver faster time to insight
PowerBI,QA

Connect to on-premises
Data Refresh, Interactive Query

Enable data culture
Live Dashboards, Drill through

James gave a cool demo of PowerBI Dashboard.

Thanks for reading and keep watching this space for more !

My 1st SQL in the City Experience !

Posted on by

I attended my 1st #SQLinthecity event hosted by RedGate yesterday. This is one of those events which I wanted to attend for a long time, and I’m glad I could make it this year.

IMG_3194

Attendees registering for the event

This whole day event was packed with some cool sessions, and I was able learn something new from each and every session I attended. Yay,  win !

High quality training delivered by RedGate and its all free ! Yes, you heard that correct.

It was a busy day for me and attending back to back sessions are always fun. Here is a list of all the sessions I attended and a short description on what I liked in each session:

 

1st session :  You did what to my transaction log?  

Speaker : Gail Shaw/Tony Davis.

This was a fun session where Gail Shaw and Tony Davis demonstrated what all possible wrong things people do to their transaction log when it’s full.

This session reminds us that Google/Bing is good, but it contains lot of bad info too!

IMG_3197

Gail and Tony talking about transaction log mistakes!

 

What I liked about this session:

It was interactive and had some demos in it. Interactive sessions are always welcome, and that’s the way to go.

Both Tony and Gail did a good job and there was never a dull moment during the whole 1 hr window.

 

 

2nd session : 101 stupid things your colleagues do when setting up SQLServer.

Speaker : Matt Slocum

I should say Matt did a great job in this talk. This was a quick session on what should be the right way of setting up SQLServer. What is recommended vs What’s not.

IMG_3199

Matt talking about SQLServer setup mistakes !

 

What I liked about this session :

Solid content, Ability to answer to the point etc.

 

 

 

 

3rd session : SQLServer 2014 new features.

Speaker : Kevin Boles

Title name is self-explanatory, and this session was all about the new features in SQL2014. Kevin Boles did a good job here and he ensured that there was never dull moment in this session.

IMG_3200

 

What I liked about this session :

Good content and Kevin talked about lot of features which are kind of hidden within SQL2014, and I like it.

 

 

 

4th session : SQLServer tips and tricks.

Speaker : Ike Ellis

The abstract for this session was interesting and it was like : ” This is a presentation for the YouTube generation. Come discover a series of 20 tips on a wide variety of topics.

What I liked about this session :

I really had a good time attending this session and learned lot of new tips and tricks. Ike did a good job and the session was very interactive. Each tip (Wide variety of topics : Includes SSMS,Profiler, SSRS etc) was explained within 3-5 minutes and demos were nice.

Conclusion :

I should really appreciate RedGate for what they are doing for the SQLServer Community/Family.Events like these are classic examples that they care for their product and us.

Thanks for reading, and keep watching this space for more.

SQLPASS Summit 2014 is here, The largest SQLServer Conference !

Posted on by

November is not the right time to visit Seattle (Blame the rain!), but I kinda like November because that’s when #SQLPASS Summit happens.

SQLPASS Summit is the largest SQLServer conference in the world, and this year the number seems pretty huge with over 5000 attendees !

#Summit14 will be my 2nd Summit, and I’m really looking forward to it. My 1st was Summit12, and I missed the one which happened in Charlotte, NC (Summit13).

This year is very special as SQLServer 2014 was released this April, and there are tons of SQL2014 related sessions for this Summit.

If you are looking for deep dive sessions on In Memory OLTP,Buffer Pool Extension, Cardinality Estimator, Deep Azure Integration, then Summit14 is what you need to attend. Register here !

I normally check-in for the event little early, and PASS does a great job by opening the registration counters by Sunday evening.

I completed my registration today and here are some pictures for you :

IMG_3176

IMG_3186

IMG_3181

I will be blogging LIVE for Day1 keynote, and will be writing about my summit experience in the next couple of days.

Sleepless in Seattle !

Thanks for reading, and keep watching this space for more !

Checking out Windows Server Technical Preview on Azure !

Posted on by

I’m a great fan of running the latest version of Windows Server whenever I deploy SQLServer. I always try to push the latest and greatest windows server builds along with latest SQLServer builds because of many reasons (That can be a whole different post altogether).

I was all excited when I heard that Microsoft released the Technical preview of Windows Server (They might eventually call it Windows Server 10,but not sure for now), and decided to set this up.

I just went to Azure and see if there is any template which is made available, and it was indeed available. Way to go Microsoft (Yep, I do praise good things !)

Win Server Tech

 

Gave it a name, sized it as I need and added few more details

Win Server Tech1

 

Win Server Tech2

Got little surprised to see all these new additions

Win Server Tech3

 

And there you go….Started provisioning my VM

Win Server Tech4

Once it was created. I downloaded the RDP file and got connected to it. When connecting to the server you need to make sure to use the ID and Password which was used while creating the VM.

As soon as I logged in I realized that there are no major UI changes, but what Microsoft did here is re-touch everything and made sure that a proper Start Menu is back.

Win Server tech1

 

You have an option to choose Start Menu or Start Screen(I’m not a fan of Start screen ! ) and that flexibility is nice.

Win Server tech4

Windows Server Failover Clustering is one of the most important feature which I deal with Windows Server as its one of the pre-requisites for AlwaysOn Availability groups, so I decided to just add that feature(Not going to configure clustering now).

Nothing new here. Its all old story with same UI.

Win Server tech12PNG

Win Server tech3PNG

Once it was installed, I went to Start menu to launch it. This is kind of neat and I like this way rather than using charm bar and searching for it.

Win Server tech5

Win Server tech6

You can also search failover cluster manager using the search option available in the start menu. There are the small things which I care about and like. Nice !

Win Server tech7

And well if you like Tiles, then you definitively have an option to pin it. I like to pin things like Cluster Manager etc.

So here you go

Win Server tech8

You also have an option to add multiple desktops. That’s kind of neat as I tend to open multiple windows and I can switch between desktop.

Win Server tech9

Conclusion :

That’s it for the UI changes for now. Nothing dramatically new and fancy,but I liked the way things look now. In the next few days I will configure clustering and will come with more updates.

Thanks for reading, and keep watching this space for more !

SQLServer 2008 R2 SP3 released, Now its testing and deployment time!

Posted on by

As promised Microsoft released SQL Server 2008 R2 SP3 last Friday, and now this is your opportunity to test and patch all the existing SQLServer 2008 R2 to this latest build.

SP3 will be the last and final service pack for SQL Server 2008 R2 ,and it will be a good idea to patch your servers just because the mainstream support for 2008 R2 is already finished. Yes, you heard that right mainstream support for SQLServer 2008 R2 ended on 7/8/2014.

You might want to get onto extended support if you don’t have solid plans to upgrade to SQLServer 2012/SQLServer 2014 (Or probably just skip SQL Server 2012 and go to 2014).

So what’s mainstream support and extended support?

Mainstream Support –

Mainstream support means Microsoft supports a product with its full offerings including paid incident support, hot-fix support, security updates, etc.

Extended Support –

Microsoft still provides security updates at no charge to all customers.

You can still call CSS or create a case online per the normal support offerings (Pay per incident, Premier, etc.)

You cannot obtain a non-security hot-fix from Microsoft free of charge. In order to obtain a non-security hot-fix, we must purchase an Extended Hot fix Support Agreement.

Extended Hot fix Support Agreements are available for Premier customers.

What’s covered in SP3 – Contains fixes from all Cumulative Updates that were released since SP2 for SQL Server 2008 R2, i.e. till SP2 CU13.

It also contains 2 additional fixes and you can read them here.

If you are on an extended support agreement and you open a case with Microsoft, then probably the first thing they are going to ask is if you are running SP3 or not?  So test this build real hard and plan to deploy it across the enterprise

OR

Upgrade to SQLServer 2014 for lot of goodness!

Thanks for reading and keep watching this space for more!

Building a Virtual Machine within Azure (IaSS) – Improvements !

Posted on by

Those Dev’s/UX designers out there at Microsoft seems to be pretty busy as they releasing back to back updates to windows azure portal over the last couple of months.

I had to create a VM in Azure to do some testing, and this time I decided to try out the new portal. Apparently, I’m not a great fan of Windows 8 styled tiles, but on Azure portal these tile like options are not that bad.

I selected virtual machine from the add button(+) and the portal gave a nice view of the available options  :

Create VM

I’m pretty impressed with the amount of flavors you have now for the VMs. Oracle Linux, Open SUSE ! Cool !

I picked SQL2014 standard, and started the create process. When you start entering the details like host name, user name etc. everything looks same as before but as you click the pricing tier, there comes some improvements.

You now have an option to check all the pricing tiers and can get an idea on the approx. monthly costs pretty easily.

Click the image for better resolution.

Create VM2
Icing on the cake is the Max IOPS numbers. This is way cool and handy to know and have.

Create VM3

I chose something small and started exploring more. I gave the hostname and all other required things and chose check box, “Add to Startboard”. This will just add the new VM to azure portal home page.

Portal started giving me cool visualization when the VM was getting created, and the current actions were getting displayed in the notifications area.

Create VM4

As soon as the VM was created its details were made available in the startboard. There are some cool data like Disk, CPU, Network etc. which definitely will come handy for everyone.

Create VM5

The process to add an alert for a specific matric looks pretty straight forward within the portal. I tried to set an alert for disk, and here is what the portal has to offer.

Create VM6

Overall, I’m really glad the way Microsoft Azure has improved over the last couple of years and now this is shaping up to be one of the trusted platforms to host critical workloads with great management capabilities.

Thanks for reading and keep watching this space for more! (Hint: Some cool SQL2014 related posts are on the way !)

Microsoft Azure Portal Preview looks promising !

Posted on by

This is one of those posts which is pending for a very long time. I was pretty excited when Azure Portal Preview was announced during BUILD2014. This portal will deeply enhance developer experience.

As a SQLServer person I was interested to explore more within the portal to see what in store for me, and here are some of the cool things which the portal had to offer me.

As soon as you login, the page welcomes you with some great information such as Service Status, Billing info etc.

Portal1

If you interested to know about a service, there is great level of information which is made available in the portal.

Portal2

If you drill down to the billing details, then you are provided with some nice stats.

Portal3

You have the option to right click a particular view and you can pin it to the startboard( Yes, Windows8 theme is everywhere !)

Portal4

Portal5

You can even do customizations for the view which was added to the startboard.

I tried to create a SQLDatabase using the portal, and it allowed me to create a web edition one with 1GB size. (Remember this is still preview). Here are the steps

Portal6

Portal7

There are lot of things like creating VMs is not available in preview for now, but the layout looks neat.(Again, if you are a windows8 user, then you will love it !)

Portal8

 

One thing really excited me was the view for storage. Everything was laid out nicely. Quite impressive !

Portal9

Conclusion  :

I’m looking forward to this portal, and I’m pretty sure that it will add more value to everyone.

Keep watching this space for more.

Journey of an On-Premises DB to Azure VM – SQL Server 2014 makes it easier!

Posted on by

SQL Server 2014 is deeply integrated with Windows Microsoft Azure and moving an On-prem database to a Microsoft Azure VM is much easier and seamless now.

This post talks about the journey of a database named “MoveMeToCloud” which is hosted on SQLServer 2014 RTM on-prem instance to a SQL Instance which is hosted on an Azure VM.

Here are the steps (Can’t get easier than this)  –

1. Right click database “MoveMeToCloud” and choose tasks and then Deploy Database to Windows Azure VM (Oops, that named needs to be changed too!)

Move1

2.  As soon as we complete the first step, we will get the intro screen. Pretty straight forward information made available here.

Move2

Once we go pass the intro screen we will get the option to connect to the local SQL Instance.  We can then choose the db which we want to move, and also a location for the backup files.

Move3

3. Next up will be security related settings. We will need a management certificate to proceed here.

Move4

By using the Sign in option we can get the required certificate. This is a smart wizard and it will pull the certificate details for you.

Move5

We will proceed by clicking next.

4. Next up is the deployment settings part. This part is little tricky as we will have to do some authentication.

Move6

As you can see in the above screen the Cloud service name, Virtual Machine name and the Storage acct is already available as we have done a sign-in in the earlier step.

We will need to access the settings button to do an authentication again. There should be a VM end point for the specific port for this to work.

Move7

Once the authentication is done, we will get the target database details which will be automatically populated.

Move8

5. Final step is to check the summary and initiate this by saying finish. The wizard will do rest of the job for you.

Move9

Move10

 

If we check the SQL Instance on Azure we can see the database is available there, Viola !

Move11

Conclusion

This is a very smart wizard and I’m really happy that Microsoft have made great efforts to make this simple to ensure that cloud movements are seamless.

Thanks for reading and keep watching this space for more !