Hi Friends,

I was spending some time with Database Snapshots today and came across a few limitations which are as follows:

1. The source database on which snapshot is base on, cannot be dropped, detached or re-created. - Backups will run fine

Source database here refers to the database for which you are creating a snapshot.

2. The file structure of the source database cannot be altered. In case, you want to do that, you have to first delete all the snapshots.

3. Database snapshots cannot be created for a portion of the source database. For example, you might want to create a snapshot only for the orders table: you can’t do that.

4. DB snapshots cannot reside on another instance – it has to be on the same instance as the source database.

5. Suppose you have created a snapshot on a separate volume. And the volume runs out of disk space – the snapshot will be marked suspect

6. Yes, snapshots are read only and cannot be created for system databases. Its only meant for user databases.

7. DB snapshots cannot be backed up, restored, attached or detached, doesn’t have a log file either.

8. Suppose you have multiple snapshots on a single source databases. And you want to revert the source db back to one of the snapshots. How do you do it? You have to first delete all the other snapshots and then revert to the one you want.

Use the following syntax to revert:

RESTORE DATABASE <database_name> FROM DATABASE_SNAPSHOT = <database_snapshot_name>

Hope this helps.

Hi Friends,

I have uploaded the pictures of SQL BI community session that I delivered in January 2010. I was delivering in Kathmandu for the first time and it is always a good experience interacting with the audience of another country. The participants engaged me well and I thoroughly enjoyed interacting with them. And yes, it was cold there

You can browse the pics at: http://www.peoplewareindia.com/images/Events/photogallary/photogallary_Kathmandu_Jan2010/photogallary_Kathmandu_Jan2010.htm

Hi Friends,

Management by exception – don’t be too sure that things are running fine. Probably, that’s the time you need to worry when everything is running smoothly, especially your Database Mirroring session

When your DB Mirroring session is established and running smoothly, you can use the Mirroring Monitor to monitor the session.

You can see many performance metrics including the sent rate, restore rate, unsent log, etc…

But more importantly, what do you when you want to be alerted when one these performance metrics cross their limits. So what do I mean?

Lets take an example: Imagine a scenario where the mirror server is down and there are continuous transactions occurring on the principal server. In such a case, there will be a back log on the principal server which is displayed as unsent log. But you certainly don’t want the unsent log to get huge and you should by notified, right so.

Therefore, DB mirroring in SQL Server offers you threshold warnings.  You can define threshold warnings in Unsent Log, Unrestored log, Oldest unsent transaction & Mirror commit overhead.

Unsent log - how many kilobytes KBs of unsent log will generate a warning?

Unrestored log – how many KBs of unrestored log will generate a warning?

Oldest unsent transaction – Do you want a warning when the oldest unsent transaction crosses 2 mins or 4 mins ??

Mirror commit overhead – This is relevant only in high-safety mode. Specifies the number of milliseconds of average delay per transaction that are tolerated before a warning is generated on the principal server.

You can choose to be alerted via mail, NET SEND msg or pager by configuring the relevant alerts for these warning thresholds.

How do you do it?

1. First specify the thresholds that are applicable to your environment.

2. You should know the error number / event ID for these warnings, which is as follows: Unsent log-32042, Unrestored log-32043, Oldest unsent transaction-32040, Mirror commit overhead-32044.

3. Next, configure Alerts under SQL Server Agent and specify the above error numbers in the configuration dialog box.

4. You need to choose, how you want to be notified. If you choose mail, make sure DB mail is configure appropriately.

That’s it. Hope this helped.

Hi Friends,

Greetings. A little disappointing for me that I am writing after a long gap of more than a month. I witnessed very hectic schedules in the month of February and first week of March. Me and my wife travelled to US in the first week of Feb and visited snow capped New York. Well, I took a whole week off from work to enjoy New York and that itself created a huge backlog. But I was quickly back to work, at least tried to respond to incoming emails :). We than travelled to Seattle to attend the Microsoft MVP summit in 2nd/3rd week of Feb. Attending sessions, product group interactions, Back to back meetings with MVPs and some personal time to tour Washington – all of this helped me in making my backlog even larger. We came back to India in last week of Feb followed by a 4 day workshop in Mumbai – yes there was no rest at all and it was indeed very hectic

But now, I feel a little relaxed as my next training program is 10 days away. I have already cleared my backlog to a good extent. And I am back to writing for you. So you will see some real good writing from me, hopefully

When I look back, the year actually started with so many events lined up and I was already geared up for a hectic schedule. My first assignment of the year was right in the 1st week of Jan at Siemens in Calcutta. Then there was India MVP summit in January 3rd week, followed by a SQL BI program for Royal Bank of Scotland in Gurgaon, followed by a SQL community event in Kathmandu for PASS, followed by a 2 day workshop at Kathmandu on SSAS, followed by another SSAS workshop at Gurgaon and then off to US. Yes, living out of the suitcase all this while. So I am deeply honored to feel relaxed now

And I have to share all the event details, pics, etc with you all – will do that shortly along with some new blog posts on SQL Server. So, stay tuned. And thanks for being patient with me

Hi Friends,

I recently delivered Advance SQL Server 2008 Analysis Services workshop across 7 cities in India. The outline/course & training delivery was so well appreciated that I almost ran full house in every city. In this process, I landed up training 100+ IT Pros/Devs in SQL Server BI. Pictures can be seen at www.peoplewareindia.com/events.htm. In India, this was first time that such a comprehensive workshop on SSAS 2008 was being delivered pan India, that too with such a fabulous feedback. You can read all the feedback/testimonials at http://www.peoplewareindia.com/feedback/viewfeedbacks.aspx.

Now, I have another group of IT PROs requesting the same course again in Delhi NCR, and I am running another batch from 4-7 Feb in Gurgaon.

Please visit http://www.peoplewareindia.com/emailers/SSAScourse_onceagain.htm to get complete details on schedule, course outline & the venue.

I will deliver the training myself and you can know more about me by visiting www.peoplewareindia.com & www.amitbansal.net. You can also download my profile from these links.

Get yourself nominated from your company (or) attend this in your capacity. This is the most comprehensive course on SQL Server 2008 Analysis Services and the sessions are truly ‘deep-dive’. If you want me to send the details to your Training manager/Project Manager/Training & Learning Department or any other manager, connect me with that person over email at amit.bansal@peoplewareindia.com

Please also forward this email to your friends & colleagues to make them aware of the event.

You can reach me at +91 9007009017 to know more. (PS: I will be out of India from 29 Jan to 1^st Feb and this number will not be reachable)

Apologies, if this message has disturbed you, I just wanted to make you aware of the event.

Hi Friends,

We all know how to access a report using the Report Manager (the web application which gets installed with Reporting Services). You can also access your reports using the web service which is also installed during installation of Reporting Services. The URL to access the web service is:

/ReportServer">http://<ServerName>/ReportServer

This means, suppose you have a report by the name of Employee Sales Report, you can access/run the report using the web service URL:

http://server/reportserver?/Sales/Northwest/Employee Sales Report&rs:Command=Render

And most interestingly, you can also pass parameters using the URL. Lets take an example: suppose your report has 6 parameters. A business user spends 15 minutes in supplying the right parameter values to get a desired output. He wants his colleagues to use the same values so that all of them get the same output. Sending an email to all the users with the parameter values they should put will be time-consuming. Every user has to manually put all the parameter values again to see the output.

Instead, you can just send the complete URL with parameter values concatenated in the URL itself. Suppose the above report takes EmployeeID as a parameter; you can run the report in the following way:

http://server/reportserver?/Sales/Northwest/Employee Sales Report&rs:Command=Render&EmployeeID=1234

This is good. URL access functionality in SSRS is very powerful and there are many tweaks that can be implemented. See books online for more details.

Hi Friends,

Many websites have something called as the ‘SiteMap’ which allows the user to see a navigational/hierarchical structure of the website making it easy for the user to locate the content he/she wants to see (like a typical map, for guidance purposes)

Similarly, if you create a report with too many items on it: a table, a chart, with totals, with groups, etc – you may want to give a site map to the user to that he can quickly jump to a particular item in the report. This is called as Document Map. It appears in a separate pane on the left side of the report viewer, and can be toggled (ON/OFF) from the report toolbar.

See the following section in books online: How to implement a Document Map.

ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10rs_1devconc/html/d0b0b1f1-c0d5-4f4e-ac9b-b44c1795a5b3.htm

Hope it helps.

Hi Friends,

We all cherish the ‘holiday’ on 26th Jan every year, but many of us are not aware of the history behind the republic day.

We all know that India obtained its independence on 15th August 1947, but it did not have a permanent constitution at that time. The laws were still being carried on from Government of India Act of 1935. On August 29, 1947, a draft committee was appointed to draft a permanent constitution. BhimRao Ramji Ambedkar headed as chairman.

It took more than 2 years and the assembly met again and again, in public, before the draft was accepted with numerous modifications and iterations. On January 24, 1950, 308 members of the assembly signed the draft (both in English & Hindi). 2 days later, on 26th Jan, 1950, the constitution of India became of the law of the Indian lands. Dr. Rajendra Prasad became the first President of Independent India.

Since then 26th Jan is celebrated as Republic Day – its the President’s day !!!

Read more at http://en.wikipedia.org/wiki/Republic_Day_(India)

Hi Friends,

Community TechDays is being organized across India. I could not post the emailer in my blog due to formatting issues. the emailer has been posted in Peopleware India forum www.WeTogether.in. Log in to the forum and register yourself for the event. Its a professionally managed community event. Great sessions & great speakers. And its all free. I might be speaking in Hyderabad or Delhi. Hope to meet some of you.

Hi Friends,

I am planning to deliver a few webcasts on SQL Server. I know many of you are looking forward to webcasts on SQL Server 2008 R2. But apart from standard stuff like "What’s New" or "Feature-based" sessions, I want to focus on real-life issues that you all face on a day to day basis. Thus I need suggestions from all of you. Think about what type of sessions could help you in improving your productivity, or solve a real problem (performance or otherwise) or help implement a solution. It could be administration, development or BI or DW. Your suggestion could be across SQL Server 2005, 2008 or R2, or it may not be version specific at all. So, I am really looking forward to your valuable inputs. Send me your suggestions at amit.bansal@peoplewareindia.com. Please put the subject as ‘SQL Server session ideas’. Do not forget to put your name, company name & contact details in your signature. Please **do not** post your suggestions in the forum here because I may not come and check this thread as I have posted the same message in many UGs across India. I am really looking forward to something innovative, something different from the standard league. If your session is selected, you shall get the credit when I deliver the session.

Looking forward to hear from you. Send you suggestions at amit.bansal@peoplewareindia.com