Recently I came across situation where database sharding was once again a suggested solution by architectures. Everytime I hear the word sharding I remember my earlier article about NuoDB on Shard No More – An Innovative Look at Distributed Peer-to-peer SQL Database. Sharding requires developers to think about things like rollbacks, constraints, and referential integrity across tables within their applications when these types of concerns are best handled by the database. It also makes other common operations such as joins, searches, and memory management very difficult.
Each NuoDB database consists of at least three or more processes that enable a single database to run across multiple hosts. These processes include a Broker, a Transaction Engine and a Storage Manager. Brokers are responsible for connecting client applications to Transaction Engines and maintain a global view of the network to keep track of the multiple Transaction Engines available at any time. Transaction Engines are in-memory processes that client applications connect to for processing SQL transactions. Storage Managers are responsible for persisting data to disk and serving up records to the Transaction Managers if they don’t exist in memory.
Here is my question to you all – do you ever face a situation of sharding of your database is required? When is sharding required not a good solution?
I have downloaded NuoDBtoday and going to build a small application which I can play with to build a scenario where sharding is no longer required. Would you be interesting in joining me with this exercise? I would like to work along with you to build such a scenario. Download NuoDB and let us start building something interesting.
Azure SQL database backup used to be a difficult task. Not any more. With SQLBackupAndFTP with Azure it became trivial. Here’s what you basically need to do:
Once SQLBackupAndFTP with Azure is installed, click at “Connect to SQL Server / Azure” button and specify connection properties for your Azure SQL Databases:
Then click “Run Now” to backup your Azure SQL Database(s):
Scheduling backups is also very simple – just check “Schedule this job on the main form” to run once daily or go to Advanced Settings for more options
Sounds simple? There are just a couple more things you need for this to work: you should know your Azure Server Name and Azure should allow your IP to connect. Let me explain how to do it.
How to find your Azure SQL Databases Server Name/Connection String
If you do not know a server name of Azure SQL Database, you can find this out on Windows Azure Management site. Sign in with your Microsoft account and click the “SQL DATABASES” menu item, then click your database name
Then click “Show connection strings” link in the bottom right corner:
You will see the connection strings for many platforms. Just copy to clipboard “Server” property value of “ADO.NET” connection like in the screenshot below:
And paste it into the “Server Name” field of the “Connect to SQL Server / Azure” window in SQLBackupAndFTP:
Allow your IP address to connect to your Azure SQL Databases server
For SQLBackupAndFTP to connect to Azure SQL Database, you need to configure Azure’s firewall. Otherwise you will be getting the error: Cannot open [server] requested by the login. Client with IP address [ip-address] is not allowed to access to the server…
Login to Windows Azure Management site with your Microsoft account, click “SQL DATABASES” menu item, then click your database:
Then click “Manage allowed IP addresses” link in the bottom right corner::
You will see a page where you can specify an allowed IP addresses for your databases:
On this page you can configure your firewall to allow the machine where SQLBackupAndFTP is running access to Azure. After adding the proper IP you should have no problem connecting to Azure.
Summary
With SQLBackupAndFTP with Azureit is very simple to take backups of Azure SQL databases as well as of regular SQL Server.
Sometime I feel I know a lot about SQL Server and very next moment, I realize that honestly I do not know much about this product. Earlier today, I had similar moments. I was playing with few DMVs and suddenly I ended up on the DMV sys.dm_xe_map_values. There are only four columns and one of the columns is a GUID.
The reason I ended up on this DMV was because I was asked a question what are the different reasons any statement can be recompiled. I knew few of the reasons why would any statement recompile but I was not aware of all the reasons for any statement recompilation. After doing some search on the internet and my older archived I figured out the reasons for Statement Recompilation using sails. dm_xe_map_values.
If you have collected trace using either SQL Server Profiler or an Extended Events session, you can capture SP:Recompile event in your trace. When you look at the event it displays the reason for recompiling the statement. However, when you run following statement in SQL Server Management Studio, you can find all the possible reasons for statement recompilation.
SELECT dxmv.name, dxmv.map_key, dxmv.map_value FROM sys.dm_xe_map_values AS dxmv WHERE dxmv.name = N'statement_recompile_cause' ORDER BY dxmv.map_key;
When I ran this query, I got following 14 different reasons for statement recompilation. I personally was not aware of all the 14 and it was interesting learning for me.
map_key map_value
1 Schema changed
2 Statistics changed
3 Deferred compile
4 Set option change
5 Temp table changed
6 Remote rowset changed
7 For browse permissions changed
8 Query notification environment changed
9 PartitionView changed
10 Cursor options changed
11 Option (recompile) requested
12 Parameterized plan flushed
13 Test plan linearization
14 Plan affecting database version changed
It will be interesting to build a test case for each of the compilation reason, as it will give a great learning experience.
Here is the list of selected articles of SQLAuthority.com across all these years. Instead of just listing all the articles I have selected a few of my most favorite articles and have listed them here with additional notes below it. Let me know which one of the following is your favorite article from memory lane.
2007
Good, Better and Best Programming Techniques Well, here is my note which I prepared to discuss in my earlier meeting. This is not complete and is not in very details. This note contains what I think is best programming technique in SQL. There are lots to add here and many opinion are very generic to SQL and other programming languages.
Explanation SQL SERVER Merge Join
Merge join itself is very fast, but it can be an expensive choice if sort operations are required. However, if the data volume is large and the desired data can be obtained presorted from existing B-tree indexes, merge join is often the fastest available join algorithm.
Random Number Generator Script – SQL Query
One of the most popular blog post where I demonstrate how many different ways one can generate random numbers. I have also built a SQL in Sixty Seconds Video on this subject as well.
SET ROWCOUNT – Retrieving or Limiting the First N Records from a SQL Query
SET ROWCOUNT option is ignored for INSERT, UPDATE, and DELETE statements against remote tables and local, remote partitioned views and when an explicit TOP expression is used in the same statement. This includes statements in which INSERT is followed by a SELECT clause. SET ROWCOUNT overrides the SELECT statement TOP keyword if the rowcount is the smaller value.
User Defined Functions (UDF) to Reverse String – UDF_ReverseString
UDF_ReverseString User Defined Functions returns the Reversed String starting from certain positions. First parameters take the string to be reversed. Second parameters take the position from where the string starts reversing.
Find Table in Every Database of SQL Server
This is one of the most asked T-SQL Script where user want to know where the table is used in every single database on the server. It is a two part story. Do not miss to read Part 1 and Part 2.
Introduction to SQL Server Encryption and Symmetric Key Encryption Tutorial with Script
SQL Server 2005 and later versions provide encryption as a new feature to protect data against hackers’ attacks. Hackers might be able to penetrate the database or tables, but owing to encryption they would not be able to understand the data or make use of it. Nowadays, it has become imperative to encrypt crucial security-related data while storing in the database as well as during transmission across a network between the client and the server.
Solution to Puzzle – Shortest Code to Perform SSN Validation Write the shortest T-SQL Code that removes invalid SSN values and returns a result set with only valid SSN values. This blog post was winner of the T-SQL Challenges on the same subject. If will be interesting to see if you can come up with a better optimized solution.
Example of DDL, DML, DCL and TCL Commands Data Manipulation Language, Data Definition Language, Data Control Language, Transactional Control Language explained in simple words.
Add or Remove Identity Property on Column How difficult is it to add an Identity property to an existing column in a table? Is there any T-SQL that can perform this action? For most, the answer to the above two questions is an absolute NO! There is no straightforward T-SQL like ALTER TABLE… or MODIFY COLUMN to add an Identity Property to an existing column in a table. However, there is an easy way to accomplish this action. It can be done through SSMS.
Roadmap of Microsoft Certifications – SQL Server Certifications
There are several benefits you can gain after clearing certification exams. After passing the first exam, developers earn credential of Microsoft Certified Professional commonly known as MCP. Once credentials are achieved developers get access to an official transcript, logos and certificates, and have their own landing page on Microsoft.com! According to Microsoft’s official site, real MCPs get real benefits of Reward, Respect and Recognition.
2010
GUID vs INT – Your Opinion This is an age old problem and I want to compile the list stating the advantages and disadvantages of using GUID and INT as a Primary Key or Clustered Index or Both (the usual case). The epic intense debate is happening on this particular topic till today after 3 years. You can see there is a wealth of information on this blog which one can just learn reading the original blog post as well as comments associated with the blog post.
Disable Clustered Index and Data Insert
Should we disable the clustered index while we insert the large data or we should drop the clustered index while we insert large data. Honestly, the answer is very simple, drop the clustered index. If we disable clustered index the impact of it is much different than we expect. Check out this blog post which describes the same issue with working demo.
Here is one of my old photo of TechEd 2010 – I had great time presenting on the stage in year 2010.
What is AdventureWorks?
This question is always there in my mailbox. Till today, everyday multiple times. Here is the blog post where I describe where one can get AdventureWorks as well I explain how they can install it as well.
SHRINKFILE and TRUNCATE Log File in SQL Server 2008
There are few absolute No-No’s in SQL Server environment. There should be no need to shrink and truncate log files in daily routine. Though this blog post was written keeping SQL Server 2008 in mind, this is also valid in SQL Server 2008 R2 and SQL Server 2012. If you are going to shrink your database, I strongly suggest that you read this blog post before it.
CTAS – Create Table As SELECT – What is CTAS?
CTAS stands for ‘Create Table As Select’. This method is used when table was not created earlier and needs to be created when data from one table needs to be inserted into a newly created table from another table. The new table is created with the same data types as that of the selected columns.
Identify Columnstore Index Usage from Execution Plan How do I know if columnstore index is used by query through execution plan? Here is the detail blog post about how to identify if columnstore index is used in the query or not.
Here are the steps which are to be followed:
Create a sample table
Insert some data
Create clustered index on it
Create nonclustered Columnstore Index on it
Enable execution plan in SSMS
Run two SELECT statement together with using clustered index and columnstore index (use hint if needed)
This blog post is inspired from my earlier blog post of UPDATE From SELECT Statement – Using JOIN in UPDATE Statement – Multiple Tables in Update Statement. In the blog post I discussed about how we can use JOIN and multiple tables in the UPDATE statement. There were plenty of the emails after this blog post discussing about using JOIN in the DELETE statement as well using multiple tables in the DELETE statement.
It is totally possible to use JOIN and multiple tables in the DELETE statement. Let us use the same table structure which we had used previously.
Let us see the following example. We have two tables Table 1 and Table 2.
-- Create table1 CREATE TABLE Table1 (Col1 INT, Col2 INT, Col3 VARCHAR(100)) INSERT INTO Table1 (Col1, Col2, Col3) SELECT 1, 11, 'First' UNION ALL SELECT 11, 12, 'Second' UNION ALL SELECT 21, 13, 'Third' UNION ALL SELECT 31, 14, 'Fourth' GO -- Create table2 CREATE TABLE Table2 (Col1 INT, Col2 INT, Col3 VARCHAR(100)) INSERT INTO Table2 (Col1, Col2, Col3) SELECT 1, 21, 'Two-One' UNION ALL SELECT 11, 22, 'Two-Two' UNION ALL SELECT 21, 23, 'Two-Three' UNION ALL SELECT 31, 24, 'Two-Four' GO
Now let us check the content in the table.
SELECT * FROM Table1 SELECT * FROM Table2
GO
Now pay attention to following diagram. Here we have two tables Table1 and Table 2. Our requirement is that we want to delete those two records from Table1 where Table2 Col3 values are “Two-Three” and “Two-Four” and Col1 in both the tables are the same.
I have explained the above statement very easily in following diagram.
When you look at this it looks very simple but when we try to think the solution, I have seen developers coming up with many different solutions for example sometime they write cursor, table variables, local variables etc. However, the easiest and the most clean way is to use JOIN clause in the DELETE statement and use multiple tables in the DELETE statement and do the task.
-- Delete data from Table1 DELETE Table1 FROM Table1 t1 INNER JOIN Table2 t2 ON t1.Col1 = t2.Col1 WHERE t2.Col3 IN ('Two-Three','Two-Four') GO
Now let us select the data from these tables.
-- Check the content of the table SELECT * FROM Table1 SELECT * FROM Table2
GO
As you can see that using JOIN clause in DELETE statement it makes it very easy to update data in one table from another table. You can additionally use MERGE statement to do the same as well, however I personally prefer this method. Let us clean up the clause by dropping the tables which we have created.
DROP TABLE Table1 DROP TABLE Table2
GO
Do let me know if you use any other trick in similar situations. If you do, I would like to learn more about it.
I will be presenting once again 3 Technical Sessions on SQL Server and Performance Tuning at Great Indian Developer Summit on May 7, 2013. If you are going to attend the event, you do not want to miss the technical sessions at any cost. Here is the generic theme for every session I will be presenting at Great Indian Developer Summit.
Each session will have 30% theory and 70% demonstrations
Attendees will have access to scripts presented in the session
Location to review the videos and free learning material associated with the session
Practical Performance Tuning Tips to tune your server
Attendees will be able to learn the knowledge which they will be able to apply as soon as they walk out of the session. Here is the details about the session:
Tips and Tricks for Blazing Fast SQL Server Performance
12:00 PM | May 7, 2013 | Main Hall
60 mins | GIDS.NET | Session
Slow Running Queries are the most common problem that developers face while working with SQL Server. While it is easy to blame the SQL Server for unsatisfactory performance, however the issue often persists with the way queries have been written, and how SQL Server has been set up. The session will focus on the ways of identifying problems that slow down SQL Servers, and tricks to fix them. In this session we will cover the common issues related to the Indexes, Statistics, Joins, Conditions as well cover a few of the mistakes developers do when they are rushing to meet deadlines. We will quickly cover SQL Server Worst Practices as well have a quiz during the session to keep the conversation alive. Developers will walk out with scripts and knowledge that can be applied to their servers, immediately post the session. Additionally all attendees of the session will have access to learning material presented in the session.
Performance in 60 Seconds – Database Tricks Every SharePoint Developer MUST Know
14:00 PM| May 7, 2013 | SD Hall
60 mins | GIDS.NET | Session
Data and Database is a very important aspect of SharePoint implementations. SharePoint Developers often come across situations where they face a slow server response, even though their hardware specifications are above par. This session is for all the SharePoint Developers who want their server to perform at blazing fast speed but want to invest very little time to make it happen. We will go over various database tricks which require absolutely no time to master and require practically no SQL coding at all. After attending this session, Developers will only need 60 seconds to improve performance of their database server in their SharePoint implementation. We will have a quiz during the session to keep the conversation alive. Developers will walk out with scripts and knowledge that can be applied to their servers, immediately post the session. Additionally all attendees of the session will have access to learning material presented in the session.
Secrets of SQL Server: Database Worst Practices
15:10 PM| May 7, 2013 | SD Hall
60 mins | GIDS.NET | Session
“Oh my God! What did I do?” Chances are you have heard, or even uttered, this expression. This demo-oriented session will show many examples where database professionals were dumbfounded by their own mistakes, and could even bring back memories of your own early DBA days. The goal of this session is to expose the small details that can be dangerous to the production environment and SQL Server as a whole, as well as talk about worst practices and how to avoid them. Shedding light on some of these perils and the tricks to avoid them may even save your current job. After attending this session, Developers will only need 60 seconds to improve performance of their database server in their SharePoint implementation. We will have a quiz during the session to keep the conversation alive. Developers will walk out with scripts and knowledge that can be applied to their servers, immediately post the session. Additionally all attendees of the session will have access to learning material presented in the session.
If you are going to be at the event, do not hesitate to stop by Pluralsightbooth. I will have something unique for you – do not forget to ask for it.
So how does it feel when the only thing which you know is SQL Server?
Have you worked in the past with any other programming language?
Actually, I find these questions very interesting as I do work with other technologies and I still do work with many other technologies besides SQL Server. Recently I got the opportunity to work with MySQL and have also built quite a lot of knowledge about the application as well. I found MySQL very interesting and easy to learn. It is the most popular database technology since the year 2008 and used by most numbers of database professionals in the world (including me). The latest version of MySQL is very popular and it is very easy to install on Windows Platform. One just has to download MySQL Installer and execute it. Just click next, next and you are done!
Though, MySQL is very easy I have noticed that not everyone knows it well. The pace of the software industry has changed and people change their expertise quite frequently based on the project they are assigned. There are two kinds of beginners in the industry 1) Those who have very little experience in the software industry and just beginning their career in this field. 2) Those who have plenty of the years of the experience but beginning their career in the particular technology. When you look at both the kinds of the people they both need training but they have different level of understanding of the subject matter.
Looking at this, I decided to build a beginners course on MySQL which addresses both the kinds of the people 1) Beginners with IT field and 2) Beginners with MySQL technology. I have build following beginners course on MySQL Technology. This is part 1 of the course and for anyone who wants to learn MySQL at express speed. The length of the course is 2 hours and 37 minutes, this means anyone can learn the fundaments of the MySQL in a very short period of the time.
You can watch this entire course of Fundamental of MySQL at Pluralsight. To watch the course, you will have to subscribe to Pluralsight library. If you do not have a subscription you can opt for a FREE Trial. If you go to the page and do not see any link enable, please login to Pluralsight and you should be able to watch this course.
Here is the video teaser which I have build for this course. Let me know what you think about it. Here is the content of the course.
Pinal Dave is a Pluralsight Developer Evangelist. He has authored 9 SQL Server database books and have written over 2500 articles on the database technology on his blog at a http://blog.sqlauthority.com. Along with 9+ years of hands on experience he holds a Masters of Science degree and a number of certifications, including MCTS, MCDBA and MCAD (.NET). His past work experiences include Technology Evangelist at Microsoft and Sr. Consultant at SolidQ. Prior to joining Microsoft he was awarded the Microsoft MVP award for three continuous years for his contribution in the community. Here is the list of the Pinal Dave's books.
Nupur Dave loves technology simply because it makes life more convenient. She is devoted to technology because it touches our heart makes our daily lives easier. Among the many technological programs she uses and embraces Windows Live most because she can do lots of things with ease – from photo management to movies; business emails to personal social media connections.
Here is the list of selected articles of SQLAuthority.com across all these years. Instead of just listing all the articles I have selected a few of my most favorite articles and have listed them here with additional notes below it. Let me know which one of the following is your favorite article from memory lane.
I will be presenting once again 3 Technical Sessions on SQL Server and Performance Tuning at Great Indian Developer Summit on May 7, 2013. If you are going to attend the event, you do not want to miss the technical sessions at any cost. Here is the generic theme for every session I will be presenting at Great Indian Developer Summit.
