Friday, November 29, 2013

SQL Server Management Studio - Project

One of the features I like in sql server management studio is when the Solution Explorer got added. This is very similar to the one in Visual Studio, one of the nice things about this is the ability to keep things organised. When working with sql server Management studio and one is on a large project/being dba is to use multiple query files. Even though one maintains the files in folders, using the solution explorer can keep things organised. The solution explorer allows one to maintain the important server connections and queries all in one place. One of the options I noticed recently in SSMS is what you see in the image below: When you click on the File Menu option, at the end very of the list one can see a Move Option:

 

The Purpose of the Move option is to take your current active query file and move it one of your active projects in solution explorer. Once you choose the project the query gets added to the project in solution explorer. One of the nice things about this option is that it allows to quickly place the .sql to the project instead of going to the solution explorer and adding it. I have highlighted the query file after getting placed in the project.

 

Monday, November 25, 2013

SQL Server 2014 - In Memory OLTP...

In the Pass Summit held in Charlotte last month, there was lot of focus on SQL Server 2014, especially the In Memory OLTP component. This particular topic has triggered interest in this version. In order to test this feature out of course needs to set up the environment right. Please refer to the following links for detailed information about In Memory OLTP operation. The following links answers questions like :

Is In-Memory OLTP the solution to all my performance concerns?

http://blogs.msdn.com/b/sqlcat/archive/2013/11/13/sql-server-2014-in-memory-oltp-discussions-at-pass-summit-2013.aspx

http://blogs.technet.com/b/dataplatforminsider/archive/2013/09/17/new-amr-tool-simplifying-the-migration-to-in-memory-oltp.aspx .

http://blogs.technet.com/b/dataplatforminsider/archive/2013/10/11/in-memory-oltp-how-durability-is-achieved-for-memory-optimized-tables.aspx.

Hope the readers have a good insight into In Memory OLTP.

Saturday, November 23, 2013

SQL Server 2014

As the field of databases continues to grow with lot of progress in the area of big data, the current crop of relational databases are starting to build in features which are gaining a lot of traction now. The areas are cloud computing, in memory analytics/databases, availability of BI on different platforms such as mobile. As SQL Server 2012 continues to be adopted by different customers, the time to upgrade is a lot slower now because of the complexities/cost involved, Microsoft has announced the next version of SQL Server, SQL Server 2014. I have been talking to some of my colleagues who have SQL Server 2008 right now, one of the topics we discussed was upgrades and they mentioned that they would probably go to SQL Server 2014 directly instead of going to SQL Server 2012 first, I hope that one can migrate to SQL Server 2014 directly. As per Microsoft, "SQL Server 2014 delivers new in-memory capabilities built into the core database for OLTP and data warehousing, which complement our existing in-memory data warehousing and BI capabilities for the most comprehensive in-memory database solution in the market".
http://www.microsoft.com/en-us/sqlserver/sql-server-2014.aspx. One of the features to be present in SQL Server 2014 is it introduces hash indexes for memory-optimized tables. Hash indexes are very efficient for point lookups, when you know exactly the value you are looking for. However, they do not perform well if you need a range of value, for example a date range, or if you need to retrieve the rows in a particular order. Apparently Hash Indexes offer better performance for point lookups.
For more details on this topic, please refer to the following link:
http://blogs.technet.com/b/dataplatforminsider/archive/2013/11/12/sql-server-2014-in-memory-oltp-nonclustered-indexes-for-memory-optimized-tables.aspx

Wednesday, November 6, 2013

CDC Update & TFS 2013

In my previous blog post I had mentioned about the CDC Session I had attended. One of the important points which was pointed out during the session was the realtion between the database recovery mode and CDC in sql server. When a Database is in Bulk logged or simple recovery mode and CDC need to be performed on the tables in the database, the recovery model changes to fully logged. The reason being the CDC has to read the log before the log backup otherwise the whole purpose of CDC would be lost. One more key point need that need to be kept in mind is that there is no overhead for CDC in sql server.

The other topic which I would like to discuss in this post is that of Team foundation Server 2013. There has been quite a bit of changes to TFS 2013. One of the main aspect of TFS 2013 is that there is now a web based interface for working with TFS. When you log into TFS 2013 on the web there is a dashboard which presents menu options such as Home, Code,Work,Build, Test. There are online team rooms which people working on a project can chat/discuss about the different changes that happen to the code base. It provides a collaborative way of working by using the web interface and provides a list of activities that need to be performed along with the members who belong to the project. Please see the couple of images below to get an idea of the web based interface for TFS 2013.