AS DBAs we often need to develop DML triggers to capture multiple entries which are either INSERTED, UPDATED or DELETED from a table. A few months back, I came across a situation where the Application Development Team asked me to write a logic to notify the DBA if any records were either INSERTED,UPDATED or DELETED from a particular table. The requirement was that the trigger should capture multiple entries instead of just one single entry. Based on the requirements from the team, I finally deci...
Read More
This is another good feature of DTA. There are lot of different scripts available over internet to find unused indexes, however why not use the SQL Servers very own DTA to figure this out. It’s similar to tuning up any other workload only the tuning option to be selected is “Evaluate Utilization of Existing PDS only”. It doesn’t works along with Tuning option “Keep all existing PDS” and it should not be selected. Let’s see how it’s done. I will use...
Hi SQLGeeks, Welcome to SQLServerGeeks.com PodCasts ! We bring you interviews and tech talks between SQL Server personalities. Once a while, one of our board member schedules a SQL Server interview and technical discussion with a leading SQL Server expert from the community. During the tech talk, the expert shares his technical knowledge and expertise with the listeners based on the subject of discussion. We are grateful to our guests for their valuable time. This is yet another way of learning ...
Hi Friends, Using SQL Server “Copy Only Backup” option for ad hoc backups. Problem: We always setup a bulletproof backup strategy for our production server using Full, Differential and transaction Log backups. Which always Suppose on our production server the backup strategy is as follows: Full Backup: 1 AM (Sunday 1 AM) TLog Backup: after every one Hour 2 AM, 3 AM, 4 AM………….. Differential Backup: 1 AM (Monday 1 AM) TLog Backup: after every one Hour 2 AM, ...
Hi Friends, What triggers me to write this blog today is that in one of my recent consulting engagements, my audience had blurred understanding of this server-level property and the concept of parallelism overall. Here is a simple explanation: Parallelism, in simple words, means that SQL Server can use 2 or more processors to execute your query. Which means, the optimizer produces a parallel plan and you can observe the parallel physical operator (yellow circle with black arrows) Processors here...
SQL Server allows a log backup to be created even when all data files are not available for the database. This is very useful in restoring the transactions that occurred right before disaster occurred. The BACKUP LOG command has an option NO_TRUNCATE which allows us to create a log backup when database is inaccessible. To quote BOL, Using NO_TRUNCATE specifies that the log not to be truncated and causes the Database Engine to attempt the backup regardless of the state of the database. To demonst...
Dear SQL Geek, Hope you are doing well. Continuing the success, here is the next bi - weekly webcast, absolutely free for the SQL Server community. It is your opportunity to hear a specialist and this time it is Abhay Chaudhary presenting on “Transactional Replication”. On Thursday, 31 May 2012 at 3:00 pm IST, Abhay Chaudhary will present an intriguing webcast on - Transactional Replication. Understanding Transactional Replication: we are beginning with a level 200 Webcast on Transac...
While replying on the MSDN Forums Most of the time people ask on how to write SQL+MDX both in single place means. 1- Certain data like aggregated data from MDX Query. 2- Select that data from SQL Query and get these data in table to show on reports. I already answer couple of the question on forums but then think it’s better to write blog on above topics. Some for the forum post I Included here for reference. 1- SSRS combining MDX and T-SQL . 2- Need help on MDX query to retrieve data from...
Hi Friends, Here I am going to explain the SQL Server Batch Separator. Batch: Batch is a group of one or more TSQL statements submitted to SQL server for the execution. 1- To separate batches we use batch separator "GO" for example : use sales go select * from xtsales2012 2- you can also use GO to define how much times you want to execute your batch. Syntax: GO [count] where count is a positive integer. for example : print 'sqlservergeeks' GO 5 3- GO is a utility command. So utilities never send...
Once you will installed SQL Server 2012 you could not find the BIDS to develop SSAS/SSIS/SSRS application. BIDS will replace with SSDT i.e. SQL Server Data Tools. Comparison of SQL Server 2012 Vs SQL Server 2008 R2. You can get more idea on below Screen shot how SQL Server 2012 looks compare with SQL Server 2008 R2 components. SQL Server 2012 Components SQL Server Data Tools SQL Server Management Studio Analysis SErvices Configuration Tools Data Quality Services Documentation & Community Int...
Hi Friends, As you might aware of (with regards to my last blog) that I was setting a Windows Server 2003 cluster on Hyper V, I want to pen down some more notes here. My last blog was about sysprep-ing a Windows Server 2003 image which can be found here http://www.sqlservergeeks.com/blogs/AmitBansal/sql-server-bi/538/sql-server-sysprep-windows-server-2003-r2-sp2-hyper-v-image When I started the Windows Server 2003 Hyper V image for the first time, I found issues with the resolution, mouse captur...
VertiPaq engine is currently only available to PowerPivot for Excel in SQL Server 2008 R2. After the amazing success of PowerPivot for Excel in the previous release of SQL Server 2008 R2 this engine moved to the server side with the same InMemory BI and compression and it’s called the VertiPaq Engine or the Analysis Services Tabular Mode. Data retrieval and calculations happen at a much faster rate as its entire database is in-memory.Vertipaq enables Excel to process hundreds of millions o...
This feature creates a copy of a database to be tuned on a specified test server and runs the tuning process on that test server. The recommendations suggested can then be applied to production server as appropriate. Only database and object structure is copied including statistics. It doesn’t copies data. Thus, it saves performance issues on production server because of tuning process and it can also be used for copying complete database structure on to test servers if need be. This featu...
Hi Friends, Sometime back, I had blogged about sysprep-ing a Windows Server 2008 R2 image. I was setting up a Windows Server 2008 cluster between multiple Hyper-V images. You can find that blog here: http://www.sqlservergeeks.com/blogs/AmitBansal/sql-server-bi/425/sql-server-failover-cluster-sysprep-your-cloned-images This time I am setting up a Windows Server 2003 cluster between multiple Hyper V images and had to sysprep a Windows Server 2003 image. So here are some tips about that: By default...
Hi friends, Here i just want to inform you the power of Dedicated Administrator Connection (DAC) in SQL Server: SQL Server provides a special type of connection only for administrators when standard connections to your sql server instance are not possible. This DAC connection can be made through sqlcmd utility as well as SQL Server management studio. There are many restrictions also with DAC. Some of them are listed below: 1-The connection is only allowed from a client running on the server. 2-O...
Hi Friends, This is indeed so basic and simple and I got surprised when I observed during one of my assignments that the customer has not set a default measure for the cube; they just did not know about this option. Since when you browse the cube, a default measure is picked up automatically and that suits the requirement of the client :) But what if you want a different measure to pop up by default? This is where you set the default measure for the cube: As per the documentation the Analysis Se...
Dear SQL Geek, Hope you are doing well. Continuing the success, here is the next bi - weekly webcast, absolutely free for the SQL Server community. It is your opportunity to hear a specialist and this time it is Sarabpreet Singh presenting on “Log Shipping”. On Wednesday, 16 May 2012 at 11:30 am IST, Sarabpreet Singh will present an intriguing webcast on - Log Shipping. You think Log Shipping is an old and not-so-worth solution to implement? Join this session and you'll change your o...
Hi Friends, By this time, many of you already know about columnstore indexes, a column store architecture that groups and stores data for each column on separate pages and then joins all the columns to return data, as required. Note that you can have only one columnstore index per table and as per Microsoft guidelines, you may want to put many columns in that one index. I am a little scared of putting ‘many’ columns in that one index :) – thus keeping an eye on the size of your...
Hi Friends, Speaking at SQL Server conferences is something that I cherish a lot, even more when it is outside my home country, India. It is indeed a privilege to interact with global audience and share knowledge. It is an unmatched and unforgettable experience. And this time, it was a pleasure speaking at SQLBits in London in March 2012. I delivered a session on Indexes which was well appreciated by the audience. Even more contenting is when you receive follow up emails after your session, cong...
Hi Friends, Continuing on from my last blog post “SQL Server: Clustered Index Physically Orders a table”, this post demystifies the myth “Clustered index physically a table”. The order of the rows in a page is managed by Row offset table and not by the clustered index. Row offset table defines the scope of a row in page i.e. it tells the start of a particular row in a page. Here is an abstract about row offset table from books online. ( http://msdn.microsoft.com/en-us/lib...
Hi Friends, Today I got a question on “can you install SQL Server 32 bit on 64 bit OS?” Answer: Yes, you can ! During setup, click on the Options link and select the architecture (64 bit or 31 bit). By default on a 64 bit OS, x64 bit will be selected. You need to change it to x86. If you liked this post, do like us on FaceBook at http://www.FaceBook.com/SQLServerGeeks Have a SQL Server question? Join the fastest growing SQL Server facebook group at: http://www.facebook.com/groups/458...
Hi Friends, Look at this scenario: When I try to slice Reseller Sales measure group by Geography dimension, I get the following output: Rightly so, because the Geography dimension is not related to the Reseller Measure group, it just shows the grand total for all the members. This can be confirmed from the Dimensions Usage tab. But don’t you think that the output could show null or blank instead of showing incorrect or inaccurate results? No worries; there is a property called IgnoreUrelat...