Categories
Reporting SSRS Visual Studio

Developing reports in BIDS using Report Wizard

In my last two posts about reports, I explained how can we develop & deploy reports using Business Intelligence Development Studio (BIDS). But I think that even though I titled it as 101, it was not as easy as it sounds. Because there is even easier way exists to create a report that is by using Report Wizard. This wizard guides user at every step and they can create simple report with few clicks and even without being much “expert” at it. In today’s post, I will explain how can we use Report Wizard to create report.

Before doing anything, we need to create new reporting project from BIDS section of VS 2008. And select Report Server Project Wizard. Once we enter name and location of project and clicks OK, a wizard appear and thus our journey begins.

NewProject ReportWizard_1

First step is to create datasource for our report, which is basically connection to database.

Categories
How To Me Performance Tuning SQL Server

SQL Server with in-memory TempDB

Today, I did some experiment with SQL Server. I have been using an awesome tool called RamDisk now and then for experiments. It allows to use RAM as a HDD !! So if you have enough RAM you can throw some RAM to this tool (free version has 4Gb max limit) and you will get nice 4Gb chunk of storage. And then you can use this chunk just as any regular drive but downside is, it is volatile since though it looks like normal HDD, it is in fact still RAM and thus it posses all good and bad properties of RAM. Bad properties like highly volatile and unreliable storage but Good property like super fast read and write capability and near instantaneous response.

As we already know that most common bottleneck for SQL Server (or generally any application) is HDD. Since they are the slowest moving part of whole architecture. Yes it is true that with introduction to newer breed of storage devices,called SSDs don’t have any moving part and so they provide super fast read and write capability. But SSDs are still relatively new technologies, so we actually don’t know how they will perform at long run and they are still expensive as hell.

And RamDisk on the other hand, is cheaper solution because you actually don’t buy new storage but you add more memory and use it to store data temporarily. It could be very well possible solution to explore for TempDB, since TempDB doesn’t require to store any data and it is mostly used as a scratch pad for SQL Server.

Before proceeding further, I would like to mention that TempDB for SQL Server was using same physical drive as of Windows and I used SQL Server 2008r2 Dev on Windows 7.

The experiment,

Categories
DOS How To

DOS: Execute and Kill Applications from Command prompt

Yes, I just refuse to use PowerShell (just yet). I know that powershell can do this and that and god MSFT knows what not but with all these bells and whistles doesn’t come at cheap price. I mean if something works just fine and smooth then why you actually need to use something new.

It is true for good ‘ol DOS (with BATCH file). I still use it for most of my automation tasks. The most good thing about DOS is it doesn’t require me to Google much because I know what I need to do if I am required to do something. But if I am asked to do same thing using Powershell then it is very likely that I will spend more time browsing through forums to find solution. I am not saying that PS is the worst shell ever invented Open-mouthed smile … but I think it is not justified to spend time on it unless it can do something that I can’t do using DOS(read it as WITH BATCH file).

Couple of days ago I was tasked to create some sort of script to automate updating of one of our demo server. A colleague of mine suggested me to use PS to streamline the process but I found that I can do it easily with using DOS commands in batch file. Two of the command that I used were START and TASKKILL,

Categories
Babbling Me

Over Clocking my AMD Processor

If you don’t know what overclocking is then there is from Wikipedia,

Overclocking is the process of running a computer component at a higher clock rate (more clock cycles per second) than it was designed for or was specified by the manufacturer. This is more usually practiced by enthusiasts seeking an increase in the performance of their computers than professional users, as overclocking carries risks of less reliable functioning or even damage. Some purchase low-end computer components which they then overclock to higher clock rates, or overclock high-end components to attain levels of performance beyond the specified values. Others overclock outdated components to keep pace with new system requirements, rather than purchasing new hardware.

As this definition says, there is a risk involved with this process as you are making your processors to work beyond their “recommended” capacity. And it worst case scenario there is a risk of physical processor damage is involved.

But despite of all these risks, I tried (succeed ) to overclock my processor. The main reason of this is I have AMD Thuban X6 (2.8GHz, hexacore processor). Good thing about this series is they can be easily overclocked upto 3.2 GHz. After some digging I actually found that AMD has a utility called AI Suite, and with it’s help you can overclock these processors with just a single click !!

Categories
Babbling Me SQL Server Virtualization Windows

Side effects of User Account Control (UAC) of Windows 7

When I used it for first time I thought that UAC was really annoying feature of Windows Vista. I mean for each and every app it was required me to confirm, if I wanted to perform some task which required access to core system. It was more like low level mimic of Linux. But after using Vista for about 2 years (yesssssss … I did used Vista that long, poor me Don't tell anyone smile) I was sort of used to of this feature. So when I upgraded to Windows 7 (tell me who didn’t upgraded from Vista to 7, except old XP users ??) I enabled UAC in it as well.

But I think I am going to fed up of this really dumb feature. Well, the feature itself is not bad its just it is not just at par with its counter part of Linux. And biggest annoyance is if you run some application and it failed to run due to UAC, Windows just don’t say it. Instead of this you will get all sort weird errors.

Categories
How To SQL Server Tips

Managing Orphaned database users in SQL Server

It one of the most common issue you will face when you will move/copy your database from one server to another server. You will have all finely setup SQL Server Logins and their related database users but when you copy or move your database those Server Logins will not get transferred automatically. Result is what we call Orphan Users, these database users are there in database but there is no login associated with it. In other words they are just useless and will create various issues related to authorization if an application or users are using it.

There are many cases when you may need to manually map SQL Server Logins with database users. For example it is possible that you have different logins created in SQL Server and you wanted to use these new logins with existing users Or you might even have same logins but since they are on different machines it is very likely that they will have different SIDs (Security Identifiers) Or simply some users has left an organization and you want to assign new user existing login (which I think is more easy by use of ROLES rather than directly assigning a user) Or wanted to have different user name then server login.

For all of these condition there is a system stored proc called “sp_change_users_login“ which can be used to manually assign logins to database users. I have created a simple demo to display the concept and process of mapping existing users to new logins.

Categories
SQL Server Tips

Truncate Vs Delete in SQL Server

If you have been looking for job in Database related field then you might be already familiar with this age old question (which I was asked in almost all interviews) is “What is difference between DELETE and TRUNCATE ?” And usually if you read on internet you will see that people answer it with just one line that,

When you use DELETE to delete records, information is stored in transaction log and hence it can be rolled back. And when you use TRUNCATE, no information is stored in log and so you can’t it rollback.

But technically it is wrong. I mean it is true that when you use TRUNCATE it doesn’t gets logged into transaction log, but it simply wrong to say that truncate will not let you rollback your operation. Code below can explain this operation,