In a previous article, I wrote about scale-up vs. scale-out architectures using SQL Server and NuoDB as examples. NuoDB recently announced the general availability of their latest product release, 1.1, and it looks like they’ve made significant progress in improving their Microsoft support. NuoDB now supports 64-bit Windows environments, natively integrates with Visual Studio, LINQ and EntityFramework to name a few.
For those of you who haven’t had a chance to read my previous article, NuoDB is a distributed cloud database that supports SQL and ACID transactions. A single logical NuoDB database can be deployed on one or many cloud machines to support the scale-out model without the need to partition, shard, or replicate.
The NuoDB distributed architecture enables you to scale computational and storage resources independently. Meaning, if your application demands higher transactional throughput, simply add more transaction engines, and if you need a highly available redundant database, add more storage managers.
A typical scale-out architecture might look like this:
Each process in the NuoDB architecture represents a virtual or physical cloud resource but the database as a whole continues to represent itself as a single logical database to any client that’s connected to it.
Many of you have been developing, deploying and maintaining .NET applications using SQL Server and might be familiar with setting up a server farm or application cluster to improve performance. As you know, this is no easy task, from both a development and deployment perspective. I’m not advocating that you eliminate SQL Server from your stack, but let’s entertain the idea of using NuoDB as the primary database for your application backend. Since it presents itself as a single logical database to your application regardless of how many machines it might be running on, you’re able to simplify your code and your scale-out strategy. The transaction engine layer of NuoDB uses a dynamic caching mechanism to eliminate the need to shard or federate your data. Transaction engines connect directly to client applications and are able to process all transaction types (CRUD) and maintain data consistency across the entire deployment environment. This opens up the opportunity to write applications specifically designed for a scale-up architecture and leverage NuoDB to provide scale-out capabilities. Below is an example of co-locating transaction engines on each node in a server farm.
The genius of this system is that co-located transaction engines should be the most responsive to the application. However, in the event that one or more transaction engines get tied up, the broker process automatically connects applications to the next most responsive. There is no prerequisite for the data to actually be present on the transaction engine. NuoDB uses a built-in asynchronous replication technique to get data directly from other transaction engines or storage managers.
I have been following NuoDB for some time now and with each release the technology becomes more exciting. Click hereif you would like to try out the latest 64-bit version of NuoDB that includes an ADO.NET driver for Visual Studio integration. Comments welcome!
This is the 51th episode of SQL in Sixty Seconds Video and we will see in this episode how to RESEED identity of the table column. Identity column is every increasing (or decreasing) value based on the interval specified in its property. In today’s SQL in Sixty Seconds video we will see that how we can reseed the identity value to any other value. In the video I demonstrate that we can set the value to any value which is greater than the current column value however, you can also set the identity value to any value lower than the current column as well.
In the real world, developers sometimes delete few rows from the table and due to any reasons they want to reseed the identity value to a lower value, this feature can be very much useful. However, there is one question which keeps on ringing in my mind that is why would anyone need to reseed identity. Anyway here is the SQL in Sixty Seconds Video which explains how we can reseed identity value.
Let us see the same concept in following SQL in Sixty Seconds Video:
Here is the script used in this episode:
USE tempdb
GO -- Create Table CREATE TABLE TestTable (ID INT IDENTITY(1,1), Col1 VARCHAR(100)); -- Insert Table INSERT INTO TestTable (Col1) SELECT 'First' UNION ALL SELECT 'Second' UNION ALL SELECT 'Third' UNION ALL SELECT 'Fourth' UNION ALL SELECT 'Fifth'; -- Select data SELECT * FROM TestTable
GO -- Returns current Identity Value DBCC CHECKIDENT ('TestTable', NORESEED); -- Resets the current Identity value to maximum value DBCC CHECKIDENT ('TestTable', RESEED, 11); -- Insert Table INSERT INTO TestTable (Col1) SELECT 'First' UNION ALL SELECT 'Second' UNION ALL SELECT 'Third' UNION ALL SELECT 'Fourth' UNION ALL SELECT 'Fifth'; -- Select Table SELECT * FROM TestTable ORDER BY ID; -- Drop Table DROP TABLE TestTable;
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.
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.
