SQL Server 2014 RTM

Yesterday Microsoft officially released SQL Server 2014. 

Tweeted by @SQLServer

Some of features that had me excited about this release are:

In-Memory Capabilities

Microsoft released a new feature called,  In-Memory OLTP and enhanced the existing feature known as Columnstore Indexes.

In-Memory OLTP

This is a memory-optimized database engine integrated into the SQL Server engine. It allows you to create tables in memory which can be used to provide performance improvements as well as deal with possible concurrency problems. Finally something cool for the DBAs!

Columnstore Index

ColumnStore indexes uses xVelocity In-Memory (formerly known as VertiPaq) technology to provide improvements in query execution time which is beneficial for common data warehouse data sets. This was a feature that was also introduced in SQL Server 2012. However you could only create nonclustered columnstore indexes and once created the tables cannot be updated. In SQL Server 2014, you can now create updatable clustered columnstore indexes and they also provided three new system views:

• sys.column_store_segments
• sys.column_store_dictionaries
• sys.column_store_row_groups

AlwaysOn Availability Groups

This is a high availability and disaster recovery option that was introduced in SQL Server 2012 and gave the you all the benefits of Database Mirroring as well as other benefits such as Active Secondaries. SQL Server 2014 allow you to have a maximum of 8 secondary replicas (increased from 4). This is great for reporting and of course sharing the querying load. Also Readable secondary replicas stay online if  the primary isn't available. This was what was expected in 2012 but is should be available in 2014.

Hybrid Cloud Solutions

As I mentioned AlwaysOn AG I thought I should start off the cloud solutions features by highlighting the fact that you can now use Azure VMs as replicas through the Add Azure Replica wizard. Another feature is the ability to store data files as Windows Azure blobs. The last hybrid cloud feature that I wanted to highlight is SQL Server Managed Backup to Windows Azure. This allows users to manage and automate database and log backups to the Windows Azure blob storage both at the database and instance level.

Cardinality Estimation Improvements

Using knowledge of modern OLTP and data warehousing workloads, the cardinality estimator was redesigned to improve the query plans.

Other features include:

• SSD buffer pool extension
• Resource Governor for IO
• Backup To URL
• Encryption of Backups

• Hosting a SQL Server Database in a Windows Azure Virtual Machine
• Delayed Durability
• Partition Switching and Indexing
• Managing the Lock Priority of Online Operations
• Incremental Statistics

• Sysprep enhancements

SQL Server 2014 has new and improved features for everyone (DBAs, Developers and BI enthusiasts) and is definitely a product that you should be excited to get your hands on. You can download the evaluation from here or if you have an MSDN subscription then you can download it from here.

Read more »

Significance of Square Brackets [] in T-SQL Code

After one of my T-SQL presentations, I was asked about the significance of square brackets around objects in T-SQL. I had an idea of their purpose but to confirm I asked T-SQL guru, Itzik Ben-Gan.

Basically he highlighted the fact that a database object name is known as its identifier (e.g., a name of a column, table, etc.) and it can be either regular or irregular.

When the identifier is regular, delimiting it is optional but when the identifier is irregular (e.g., starts with a digit, has a space within it, is a reserved keyword, etc.), it has to be delimited.

T-SQL supports two kinds of delimiters:

1. Square brackets, e.g., [Order Details], which is proprietary.
2. Double quotes, e.g., "Order Details", which is standard.

Both delimiters for irregular identifiers results in no errors

No delimiters for irregular identifiers results in errors

If you want to know what makes an identifier regular then check here under “Rules for Regular Identifiers”.

Read more »

PASS Azure VC Meeting - Increase data safety and uptime with SQL Server 2014 and Windows Azure

Topic: Increase data safety and uptime with SQL Server 2014 and Windows Azure

Speaker:  Hans Olav Norheim - Software Developer, Windows Azure SQL Database & SQL Server

Date: Thursday March 20th 2014 12:00-13:00  Central Time

           ADD TO CALENDER

Where: LiveMeeting Link

Summary: 

Would you like to ensure availability and backup of your data, but don't want to invest in more hardware? SQL Server 2014 comes with support for backup and disaster recovery in Azure. Join and learn about how you can secure your data as well as keep applications available and reduce the risk of losing data if something should go wrong.

About Speaker:

Hans is from Norway and has worked with SQL Server since the early 2000s as a customer, a few years back before he joined the SQL Server Engine team as a developer in the engine programmability team (T-SQL/SQL). Today he focuses mostly on the distributed infrastructure running Windows Azure SQL Database.

Visit our homepage and follow us on Twitter!

Read more »

Solving the Management Studio Error: Saving Changes is not permitted

Saving a table after making modifications (e.g. changing the data type) might result in the following error:

To avoid this error follow these steps:

1. Select "Options" from the "Tools" menu.

2. Select "Designers" and uncheck the "prevent saving changes that require table re-creation" option.

3. Click "OK".

Read more »

PASS Summit 2013 Review

This is my fourth PASS Summit but my first time attending it outside of Seattle. I like Seattle but I have to admit it was nice to visit some place new and the fact that it was warmer was definitely a plus. Also since Charlotte is the hometown of SQL Sentry they gave us a treat with maps, evening shuttles and sponsorship for various events. 

Welcome Reception

This year I decided to attend more sessions because I wanted information about particular topics and I also wanted to see certain people present in-person. There is always a wide selection of interesting topics and knowledgeable and outstanding speakers at the Summit so choosing sessions is always a task. I believe that a main benefit of attending the event is networking and visiting vendors. I attended some of the evening events such as the welcome reception and exhibitor reception and met many attendees and vendors. The Community Appreciation Party which takes place on the Thursday night is also a great place to meet old friends and make new ones. This year the party took place at the NASCAR Hall of Fame which had many historical cars as well as activities such as High Octane Theatre, the Simulated Track and the Pit Challenge. 

NASCAR Hall of Fame

The venue, Charlotte Convention & Trade Center, was a suitable size for the event even though some might say the rooms were small for some sessions. The Summit had a large number of attendees that will continue to increase and because of the quality of speakers and sessions it seems inevitable that all sessions will have to be held in large rooms. I was shocked at my first planned session when I entered the room and saw people sitting on the floor and I was blown away the next day when I saw a queue to enter a session. My own planned session at that same time was also full. Just to be clear I’m not complaining but highlighting how much the Summit has grown.

Leaving PASS Summit 2013

I must admit that this year time management was a task especially since I wasn't familiar with the venue but I still learned and networked which are my main goals at this event. I had a great time seeing my old friends and making some new ones. In the last four years, I have never regretted taking the time or spending the money to attend the PASS Summit. If you work mainly with SQL Server and have never been to PASS Summit then I recommend attending Summit 2014 in Seattle, especially with the upcoming release of SQL Server 2014! 

Read more »

Red Gate's SQL in the City US Tour 2013 Review

I attended one of these events last year in Seattle and it exceeded my expectations. SQL in the City is a free event that takes place in various cities and provides great learning and networking opportunities for the SQL Server community. This year they visited three cities; Pasadena, Atlanta and Charlotte with touring speakers Steve Jones, Grant Fritchey and members of Red Gate product team. I attended two out of three events and also spoke at the Charlotte event.

Atlanta

This event had speakers Tim Radney doing a session titled 'Life as a DBA' and Stuart Ainsworth doing a session called 'Simplifying SQL Development'. I had a great time meeting Red Gate team members again as well as their new members  and also hanging out with attendees like Tim Radney's son. The sessions were very informative but I have to say that the most useful information I got was the advice that the presenters gave me for my session in Charlotte.

Red Gate team in Atlanta

Steve Jones Presenting

Charlotte

I have been looking forward to this event since August,  when I found out that I was accepted to be a speaker, because it would be my first time presenting in the US! I got to the event very early to review my slides but spent most of the morning in awe because of the venue; the Ritz-Carlton is awesome! Eventually when I settled down I saw Tony Davis, author of Red Gate's Transaction Log Management book, and asked him to review my slide deck. His feedback caused me to make some necessary last minute changes and after three cups of coffee I was ready to go.

There were 105 people at my Understanding and Controlling Transaction Logs session and I received some positive feedback from people after the session and also a good rating from Red Gate's review. To be honest presenting for the first time in the US, added some stress to me before the session but the advice from the guys in Atlanta and seeing familiar faces in the crowd during my session made it so much easier to present. It was a great experience and I want to thank Annabel Bradford, Carly Harding, Amy Roberts, Hannah Jermy and the rest of the Red Gate team for giving me this huge opportunity and also for all their assistance in preparing for it. Also want to thank people like Tony David, Steve Jones, Grant Fritchey for the advice and tips in the morning.

Presenting in Charlotte

Besides me and the rest of the touring team, this event had speakers like Louis Davidson, Kevin Boles, Kevin Hazzard and Mickey Stuewe. The sessions that I attended were fun and informative. The extra fun stuff started around 4 pm when the Charlotte BodyWorks masseurs came to relieve some people tension, followed by the networking event at 5 pm that included food, drinks and of course free swag. I want to congratulation the Red Gate team on another successful SQL in the City Tour. How do I know it was a success? Well besides me enjoying it and learning things I met someone later in the week who had attended  and to paraphrase her "For a free event I was impressed at the level of sessions, all of them were very useful and they were done by good presenters. Some paid events don't even give you that."

Performing a demo

You can download my slide deck as well as any of the other presentations from Red Gate SQL in the City site. I want to thank Paul Randal for permission to use some of his demos during my presentation.

UPDATE: While writing this post I received a Christmas card from the "Friends at Red Gate" team so I wanted to public acknowledge that and wish everyone at Red Gate a very happy holiday and a Happy New Year as well.

NEXT REVIEW: PASS Summit 2013 Review!

Read more »

TechEd North America 2013 Review

I decided to use the last few days of 2013 to write posts to review conferences and events that I attended outside Trinidad this year. Why? Because I find people's views on events to be helpful when deciding what to attend and hopefully my feedback will be useful to someone when they are choosing events in 2014! I will start off with TechEd North America 2013 which was held at Ernest N. Morial Convention Center in New Orleans, Louisiana. TechEd is an annual technical conference which is held by Microsoft for IT professionals and developers.  The conference has various sessions that allow you to learn about the future of Microsoft’s products, solutions and services. Please note that this was my first time attending a TechEd event so I can’t compare it to previous instalments.

Front view of the Convention Center

Content

There were many sessions to choose from but not many if your focus is just one product/technology like SQL Server. To me this wasn’t an issue because if there were no SQL Server sessions that I wanted to attend then going to an Infrastructure or Developer session was still very useful to me. Even if you couldn’t find a track session that you considered interesting, there were certification sessions that were valuable even if you already passed the exams. Let's be real, passing an exam doesn’t mean you know absolutely everything in the course and who knows this may come in handy if you start offering training in the future (hint: opportunity). As for the speakers, the list is made up of individuals who are well-established and very knowledgeable in their field. 

Transaction Log session

Certification and Labs

If you went to sessions and saw something that interested you in a session then you could head down to the hands-on lab area and actually try out what you saw or play around with the product/feature. Not sure what you're doing or how to get started? No problem because there were a number of MCTs available to assist at the labs area. Also, you could have done certification exams on the spot for 50% of the cost. There was even the option to take practice tests

Expo Area 

Me at MSE Booth

Expo areas are my favourite at conferences because it is the best place to meet new people and build your network. The interesting thing about this expo was the fact that there were vendors with products  that are considered competition to Microsoft products. They were there because they were a sponsor and added some value to another product (Yes Oracle had a booth!).  This expo was also different because I was working at Microsoft Solutions Experience (MSE) Data Insights area. In most MSE areas there were a number of product experts made up of MVPs and Microsoft Product Group members who answered questions about various products and features. One cool thing about the MSE was the fact that the experts included speakers so if you needed clarity on anything from their session then you could have asked them at the booth.

Other Cool Stuff

There were many book signings which meant free autographed books. There were lots of swag from various vendors and you also had the option to buy at a conference store at the venue. An abundant supply of caffeine was available but I guess that is expected at Microsoft events. The event had random events at various MSE booths and various “After Hours” events during the week which included vendor parties and the closing attendee party at the Mercedes-Benz Superdome with a Tina Turner performance. Last thing that I must mention is the super Surface sale that allowed me to get my Surface Pro for $399.

Closing Attendee Party with Tina Tuner on stage

Expo Hall

Con

The venue was too big which made it very difficult to get to some sessions on time and in some cases I didn’t even bother to attend one or two because of the hassle involved in reaching back to the booth on time.

As I mentioned this was my first time at TechEd and also my first time in New Orleans so everything was fantastic for me (hence why only one con). I know people say TechEd has become more marketing oriented but the sessions that I went to had lots of demos and the MSE areas is a great platform for technical discussions. I understand why you might not want to attend if your focus is one product but I recommend it if you want to widen your scope or if your focus is more than one Microsoft product. New Orleans was also a factor for the event because I really enjoyed the food and the weather. I also had a great tour guide named Adam Machanic so finding the right places to eat and hangout wasn’t an issue.

Registration is open for TechEd North America 2014, which is being held from May 12-15 in Houston, Texas.

NEXT REVIEW: Red Gate's SQL inthe City US Tour 2013!

Read more »