SQL Server 2014 released, get your evaluation copy

Starting today, April 1st (not a April fools joke), Microsoft has made available SQL Server 2014, the latest version of the database platform to the general public (aka RTM, Release to Market). You can go to this page — > Technet Evaluation Center to download your evaluation copy of SQL Server 2014. I am excited my self to see SQL Server 2014 in action personally. I finished downloading the eval copy and am looking forward to install it in a couple of days time. You can visit this page to get more information on SQL Server 2014..

Good luck !!!

— Bru Medishetty

Do you like this site? Like our FB page @ Facebook.com\LearnSQLWithBru so that, you know when there is a new blog post.

Cannot connect to a SQL Server 2008 named instance; How to resolve

Last month, I had this issue where a couple of applications had issues authenticating to a SQL Server 2008 R2 instance. So I started to troubleshoot this issue by duplicating from my workstation and finally arriving at a solution. This blog will explain what was caused this issue and the solution to that issue.

This SQL instance (that I had issues) is a 2008 R2 SQL Cluster and named instance, but it was configured to be accessible through the port 1433. So the SQL Instance was accessible both ways as (“clustername” and also as “clustername\instancename”). After a maintenance over a weekend (what is done on that cluster, what settings have been made and why is way out of topic in this blog), we started to have couple of applications not being able to authenticate to this SQL instance.  At first I could not think why this was happening, as I was able to see several users and applications connected to this instance and working fine (All SQL jobs running etc etc). I tried to login to that SQL instance (I commonly use just the clustername to connect) and was successful. Thought the individual accounts these applications are using to authenticate, have been disabled, but that was not the case, and lastly when I tried to login using the entire clustername\instancename then I found that I was having the same authentication failed error that those applications have been reporting. 

Solution

After looking at the state of SQL Services in SQL Server Configuration Manager, I found that the SQL Server Browser Service, though the Start Mode was set to Automatic, it did not start and was causing this issue. So I started the service and tried to login using the  clustername\instancename and it started to work…

SQL Server Browser

The above pic is how it looked on my server when I saw the services in configuration manager. (Note that this screen shot is from my lab not the actual server where this issue occurred).

Do you like this site? Like our FB page @ Facebook.com\LearnSQLWithBru so that, you know when there is a new blog post.

–Bru Medishetty

Product-Ads-75-Clicks_728x90._V174189179_

How to assign Server Admin role for a user in SSAS

In this blog we shall look at the steps to add a user into the Server Administrators Role in SQL Server Analysis Services (SSAS). Let’s get started..

Connect to the SQL Server Analysis Services from Management Studio (SSMS), once connected, right click on Analysis Server (as shown below) and select Properties. ssas-1Once you click Properties, the Analysis Services Properties dialog box appears, then click Security page, to show the list of users who are currently Server administrators (shown below)..ssas-2 Click on Add button and type the name of the user and click OK button.ssas-3 The user is now part of the Server Administrator group on the Analysis Server now.. To cross check click on the Security page on the Server Properties.ssas-4Do you like this site? Like our FB page @ Facebook.com\LearnSQLWithBru so that, you know when there is a new blog post.

–Bru Medishetty

How to find the Table which has Maximum Columns in a database

If you are interested to know about the table (in a particular database) that has most number of columns and how many columns.. You can run the below script..

SELECT NAME AS Table_Name, max_column_id_used AS Total_Columns
From SYS.tables
WHERE max_column_id_used =
(SELECT MAX(max_column_id_used) FROM SYS.tables)

As shown in the below screen shot, it will list the table and the number of columns in that table..

Table_with_Max_Columns

Do you like this site? Like our FB page @ Facebook.com\LearnSQLWithBru so that, you know when there is a new blog post.

–Bru Medishetty

How to refresh Test, Dev Systems with Prod Data?

When I attended PASS Summit last October, there were many people whom I met during breakfast and lunch and sometimes in the lobby and we discussed a variety of topics on  SQL Server. During one of those conversation, I was asked to give my suggestion on how to refresh their Development and Test environments with the Production data. So I thought it would be nice to write the same, so that it might help some of those who are looking for the same info. 

Backup & Restore

One of the most common mechanism of refreshing environments is using database backups on the production server and restore on the DEV, QA server to refresh the existing one. The advantage of using backup / restore is it is easy to do it. You don’t have to perform any special steps in this procedure other than restoring, coz your most recent full backup of production database should be good. However, the downside of this is that your DEV / QA database would be the same size of the production and your DEV / QA machine might not be anywhere near the size of your PROD box. The disk space too might be a factor to consider when using this procedure.

SQL Server Integration Services (SSIS)

If you are aware and comfortable using SSIS packages, you can utilize this platform to refresh and  make other customization after your DEV / QA database is refreshed. With SSIS, you can automate the database refresh and setup a SQL Agent job and when you have to refresh you can run the job manually (or on a pre-determined schedule).. Some of the benefits using SSIS is the manual tasks that might be needed after you refresh the data, such as scrubbing off critical data (such as SSNs or Credit Card data etc), adding additional Developer / Test users etc.. You might also not want all of the data from your production databases, only the most recent year’s worth of data is good to test, in such cases backup restore will not solve the purpose, the data that is not necessary has to be dropped after restoring the database backup…

Import Export Wizard

Import Export Wizard would be another handy tool that can be used for data refresh, using this tool does not need any other program (such BIDS or SSDT as in the case of SSIS). You can pick only those table(s) that needed in the DEV / QA environment.. The disadvantage of this tool is that you cannot customize beyond a certain level and scheduling is not an option unless you save the SSIS package at the end of this wizard.. Also, you cannot push data simultaneously to 2 different databases such as DEV and QA at the same time (where as in SSIS you can do so using Multi-Cast Data Transformation).. 

Other options

Finally there are other options such as Scripting the database objects and transferring the data using stored procedures at either the source or destination (which involves a linked server between the source and destination servers) and / or command line utility called Bcp. 

Overall, I would prefer SSIS as my choice to refresh QA / Dev databases..

Do you like this site? Like our FB page @ Facebook.com\LearnSQLWithBru so that, you know when there is a new blog post.

–Bru Medishetty