Showing posts with label SQL Azure. Show all posts
Showing posts with label SQL Azure. Show all posts

Monday, August 29, 2016

SQL Azure, Hekaton, and Bob Dorr is Awesome

Hello Dear Reader!  I had recently written a blog for the PFE blog, "How Many Tables Can I Have In SQL Azure DB & SQL Server".   It was a fun blog that I had written during a very long layover when traveling home.  It covers the maximum number of objects that you can create in SQL Server.


In this blog I had written up a demo to create the maximum number of tables you can in a database.  I also explained that based on table creation rates it would take around 64 days for me to reach that limit.  As a point of interest I tried creating In-Memory OLTP/Hekaton tables vs. native T-SQL tables.  There I noticed something very interesting.  Creating In-Memory OLTP tables was slower than creating T-SQL tables.  A lot slower.

To be crystal clear this is specifically on the creation of tables nothing more.  This has nothing to do with the insert, deletion, or reading of data.  My blog was about how many OBJECTS could a database hold, and I had a tight loop creating tables over and over again.  I explored different options to get as many tables created as quickly as possible.

"So Balls", you say, "You said this was slow.  How slow?"

Great point Dear Reader, back to the question!

I was able to create 388 native T-SQL tables per second and only 3 In-Memory OLTP tables.  On the book of faces someone asked about the results very quickly.



I reached out to my colleague Robert Dorr (Blog) from the SQL Team.   He and Bob Ward, from the SQL Tiger Team, (@BobWardMS | Blog) are running a great blog called Bob SQL, https://blogs.msdn.microsoft.com/bobsql/ , you should check this out.

He was kind enough to write up a reply that covered how things work under the covers.  To read his blog click here.


QUICK SUMMARY
Each In-Memory table is managed compiled code.  By making In-Memory objects compiled this allows us to save CPU cycles and allows us to skip some of the traditional over head of native T-SQL objects.

When we issue the DDL to create an In-Memory table we follow the same processes as a native T-SQL table. We also do two additional steps we create the Access DDL code and we compile the Access DDL code.

These steps give us a one time hit on creation of an object that give us long term benefits as we execute against our In-Memory OLTP/Hekaton tables.

WRAP UP



There are nothing but great questions out there Dear Reader.  The worst kind of question is the one that is not asked.  In this case the question pushed me to look for an answer that I already wanted to know.


In this case a demo that I almost didn't write, a question from a place that I normally do not look for questions, are all that we needed to get an answer.  My thanks again to Robert Dorr for taking the time to give us the answer.



I love it when a plan comes together.  Until next time Thanks for stopping by.

Thanks,

Bradley Ball






Monday, April 25, 2011

Cloudy With A Chance of SQL

We’ve all heard the phrases “The Cloud” and that Microsoft is “All in on the Cloud”. We manage SQL Server, we Develop on SQL Server, and we support SL Servers.  We make our living supporting a product that our companies use that was made by Microsoft.  Some are predicting that the Cloud will change the world, and plenty more just don’t buy it.

“So Balls,” you say,”As DBA’s what does this Cloud thing mean for us?” 

Well Dear Reader it means learning something new, which is always a good thing.   The Cloud offers us SQL Azure.

WHO IS THIS SQL AZURE, AND WHO SAID HE COULD TAKE MY BIRD SEED DATABASES


 SQL Azure is a totally different version and build of SQL, depending on the release you could get a different version number but executing SELECT @@VERSION should return 10.25.****.

SQL Azure by default disables DMO’s that work below an instance level.   You set up a SQL Azure Server, which is a logical container similar to a SQL Instance.  However, this is not the type of server that you would create Multiple instances on.  Your database will be automatically replicated to three separate locations, load balanced, and supported by High Availability. 


While those features are all sound pretty sweet, there are quite a few things that you cannot do, to see the list click here.

One thing that won’t be going out of style with anyone that uses Azure is from the Database on up.  By that I mean Indexing, Performance Tuning, and general Database Design.

NOTHING TO SEE HERE

I think one of the biggest drawbacks to the cloud is that you cannot look at the DMO commands or Perfmon for anything at a server level.  If an application starts to have a hiccup, the database and its performance normally come in to question at some point.  Having access to Server level objects helps us to know things are doing good, or not so good.

Then again, Azure is something new.  First we don’t always virtualize it.  Second if we virtualize it, we don’t cluster and replicate it to three different locations.  Third we don’t LOAD BALANCE it.  

So if we did have access to the server, there may be a lot of new things we would have to teach ourselves.   Personally I’d like that opportunity, but that could just be me.  

So we’ve got to Trust Dear Reader that everything is going just fine with the servers.  I’m sure there is a support staff we could contact if need be, but not having done that before I could not tell you the cost nor how to do it.

SQL VS SQL

So let’s take a look at some of the Pro’s and Con’s to this

Pro’s

Time Savings – You won’t be setting up database jobs, no SQL Agent, no Maintenance Tasks

HA/DR- Your High Availability is built in, replicated 3 times, Clustered, Load Balanced, and if one of your Virtual Machines Fails, another will be spun right up.

Hardware-You can stand up Hardware much faster, you don’t have to cost out the depreciation of hardware, you are charged by the usage.  Grant Fritchey (Blog|Twitter) wrote about the Cost of SQL Azure, go read that for more great info!

SLA’s-Microsoft has been working with Internet Facing Applications for quite some time, cough cough MSN, Hotmail, Microsoft.com, Technet, I hope you get the point.  They have published SLA’s that guarantee 99.95% uptime.

Security-When you go to MVA to learn about Azure there is a Security Model, that show’s how Microsoft is going above and beyond to provide a secure data center experience.

Con’s
Server Access- As we discussed before, your trouble shooting will only go so deep, you won’t be able to look at his for yourself.

No SQL Agent- Okay I get you want to take server management because you’re doing that, but how do I defragment my Indexes, what if I want to run a batch script, or have a job to update statistics.

Trust- When my boss says take a look at that SQL Server and see if anything is wrong, I’m limited, unless I involve boots on the ground that I don’t have a current working relationship with.  This will probably get tackled over time, but it does take time.

Lack of features- I posted that earlier, but there are things like XML, Filestream, and Encryption that some applications will need to have.

“So Balls”, you say “Do we use this or what?”

The places I see this being of most benefit, if you have a business group that needs to move fast, you can have a SQL Server, or app servers for that matter, set up in the blink of an eye and the swipe of a credit card.   I can see some places this will work great.  And I can see some places this will not.  I’m going to Total Recall it here Dear Reader, you’ve got to decide on your own, and like just about everything else SQL “It Depends”.

GO LEARN SOMETHING ALREADY!

Right now the Microsoft Virtual Academy is offering free training courses in Cloud technologies.  I would recommend you sign up and take the courses.  There are video’s, PDF’s and quizzes on Introduction To SQL Azure, Introduction to Windows Azure, Planning, Building and Managing a Private Cloud, and Windows Azure Security Overview.

 (By the way everyone end's up being #1 when you take All the tests, while I'd love to be super special you'll get to be #1 as well)

I’ve already taken my training and it was well spent time!  Go and learn something!

Thanks,

Brad