Revamping SUG 2nd Meeting

Tweet

Friends,

Last Saturday, 21st we have 2nd meeting of SUG members and this time we made progress – I, Vinay and Matang was present in meeting .We have discussed following :

1. We have decided that portal management will be done by I, Vinay and Matang initially, here are the module that we’ll be adding content on:
1. Forums and Team section –> Hemantgiri S Goswami
2. Article Section –> Matang Panchal
3. Blog Section –> Vinay Pugalia
2. We’ll design and use common PPT theme during all our presentations
3. We’ll organize quize every quarter
4. From next meet, 1 member will make presentation of 15 minutes; starting with myself
5. We will try to accommodate as many as practical / self demos in initial (and, so on) events
6. We would also create PPT / Demo repository that we have used during event
7. Also, there will be a download section where we’ll share scripts/codes to download
8. We will open a bank account to manage the fund we’ll receive from Sponsors

See you in next meeting will be on 11th February 2012
Venue : 406, Empire State Building, Nr. Udhana Darwaja
Time : 7 PM


Image source: http://pmtips.net/wp-content/uploads/2010/03/meaningful-meetings.jpg

-- Hemantgiri S. Goswami (http://www.sql-server-citation.com )

1/23/2012 by Hemantgiri S. Goswami · 0

Roles and Responsibilty of SQL Server DBA

Tweet

• What is a role of a DBA in an organization?
• What are the daily activities of a DBA?
• What shall I check on regular basis?
• What all processes shall I automate ?
• What are the precautions, I have to take additionally?

These are the common question being asked in-common on may forums, here is what my listing is

• Design and implement Disaster Recovery

• Design, development, upgrade and Migration of Database Server.

• Providing support and handling most critical situations in vast variety of database systems.

• Database Optimization / Query Performance Tuning.

• Design and implementation Automated Database & Application fail over server setup.

• Capacity Planning, Change Management and Admin. Documentation.

• Review of existing design and specifications of the system.

• Design and documentation of operational specifications of the system.

• Monitoring of the development process, in order to confirm optimum performance of the system.

• Preparation of standards and follow up rules for the back – end system, to ensure a fully secured and a robust system.

• Design and implementation of the logical & physical structure of the database.

• Monitoring of performance of database servers and providing tuning measures.

• Performing database / application wide query tuning operations.

Try automate every process that needs to be performed on daily basis and/or requires manual intervention.

--  Hemantgiri S. Goswami (http://www.sql-server-citation.com)

1/02/2012 by Hemantgiri S. Goswami · 0

Tweet

Wishing you all a very Happy and Prosperous new year, may success comes to your doorstep

Image source : Top CNN News

1/01/2012 by Hemantgiri S. Goswami · 0

What’s new in SQL Server RC0 setup

Tweet

1. Datacentre Edition is no longer available as a MS SQL Server 2012 RC0 candidate, Read More
2. MS SQL Server 2012 RC0 includes new edition – SQL Server Business Intelligence,  Read More
3. Service Pack 1 is the minimum requirement for Windows 7 and Windows Server 2008 R2 operating systems, Read more
4. Data Quality Services can be installed using SQL Server 2012 RC0 Setup, Read more
5. Product update is the new feature available with MS SQL Server 2012 RC0, this will integrate latest update with main product including MS Update, WSUS, local folder or UNC. This feature is an extension to Slipstream Functionality which was available in SQL Server 2008 PCU1. We can use command prompt or configuration file to override default values to find updates by specifying values for UpdateSource parameter, Read more
6. We can now install MS SQL Server on Windows Server 2008 R2 Core SP1, not all features are supported though, Read more
7. SQL Server Data Tools (formerly, BIDS) is now part of setup, with this we can carry out all database design work, and can build solutions for SSAS, SSRS and SSIS. Read more
8. Now, Support for SQL Server multi-subnet clustering included, Read more 
9. Databases (systems and users) can now be on UNC path, we may need to make sure that proper NTFS and File Share permissions are assigned, Read more
10. Local disk can be used for Tempdb in cluster, Read more
11. Built-in Administrator and Local system account will not be part of sysadmin role
12. Itanium editions are no longer supported

12/29/2011 by Hemantgiri S. Goswami · 0

Revamping Surat User Group

Tweet

Dear Friends, we had a user group meeting last week – I and Vinay Pugalia was  here. We have discussed many things to revamp and re-launch Surat User Group. Here are the minutes of meeting:

1) Regular Meetings -> Monthly, Proposed schedule :Saturday evening, 1900 to 2000

2) Regular Events -> 1 every 2 months, 1 workshop every quarter

3) Decide and form SUG Website, with Blog,Forums section, Facebook page and twitter handle

4) Press Notes after every event

5) Help from CSI Surat -> Have to meet, discuss and encourage Local IT professional to join and PARTICIPATE

6) Seminars/ Workshop :-free, TBS on weekends (Sat-Sun) to have discussion with Professional / Students

7) Different topics and technology, every time has 1 continue and 1 different topic

8) Initially we'll take session and then, we'll encourage others to take sessions and like that

9) Form Core Team & Backup Team

10) Role definition and Role assignment

11) Encourage new speaker to start with very basic topics to build their confidence

12) More Online activities etc.

13) Ask speakers to provide technology they can confidently speak

-- Hemantgiri S. Goswami (http://www.sql-server-citation.com )

12/27/2011 by Hemantgiri S. Goswami · 1

Maintenance Plan mystery

Tweet

Last week I had interesting (or I can say weird ? ) case of Maintenance Plan. One of the server that we monitor has reported job failure – this was a part of maintenance plan. Looking at the job history and log file I came to know that this job was failing because of some database which wasn’t available – this database was dropped few days back, and, the maintenance plan has it’s reference stored somewhere. I have search around system tables etc. but nothing unusual or nothing helpful was available that indicates the source of this database.
Finally, I have edited the maintenance plan which resolved this case, here is what I did:

1. Alter the maintenance plan "some maintenance plan"
2. note down the databases name
3. altered it and selected all the system databases
4. saved the maintenance plan changes and exit
5. re-open the maintenance plan
6. re-selected those databases (from step 2)
7. saved the maintenance plan changes and exit
8. re-invoked the job
9. and, the job completed successfully

And, the mystery persist, can you guys suggest me if I miss something to refer/research ?

-- Hemantgiri S. Goswami (http://www.sql-server-citation.com )

12/14/2011 by Hemantgiri S. Goswami · 0

Using NOLOCK hint

Tweet

Use NOLOCK hint to avoid block - this is what I have often heard/see in many forums I participate, during local user group events and meeting. I have always advised that its not that good idea to use hints, as it may cause data corruption and blocking. And, anyways, there are many things that you can do to avoid blocking, like:

1. use sp for everything (almost)  
2. try to avoid using cursor
3. transaction shouldn't be too big etc
4. and, use READPAST hint , I will still say, use this only when you don't have choice

But these all comes from the experience from the field and haven't anything concrete to quote as reference point until last night, I was googling something and this blog article from  Dave, on MSDN Blog showed up.  Now, I can quote SQL Server NOLOCK Hint and Other poor ideas as reference to my peers,and friends at local user group, and I am referencing it here for you to read and make a note.  


I hope this helps.


-- Hemantgiri S. Goswami (http://www.sql-server-citation.com/) 

12/02/2011 by Hemantgiri S. Goswami · 0

Download SQL Server 2008 SP3 CU2

Tweet

MS has recently released SQL Server 2008 Service Pack 3 Cumulative Update 2 Released‏ which has fixes reported after SQL Server 2008 SP 3, this build version is  10.00.5768.00.
Below are the bugs that have been fixed in this CU:

1. FIX: A backup operation on a SQL Server 2008 or SQL Server 2008 R2 database fails if you enable change tracking on this database
2. FIX: CLR stored procedure returns NULL after it is recompiled in SQL Server 2008 R2
3. FIX: Access violation when you insert a record into a new empty partition in SQL Server 2008

There are few more things we have to keep in consideration while applying this CU:
Pre-install:
1) You should be running on SQL Server SP3, if you haven't installed SP3 for SQL Server here is the link from where you can obtain SP3, Link => http://support.microsoft.com/kb/968382
Post-install:
* It is advisable to reboot the system once CU is applied
Please make sure you test the CU in Dev/QA/UAT environment before you apply it in the PROD environment.
Here is the link from where you can download the SQL Server 2008 SP3 CU2, link => http://support.microsoft.com/kb/2633143/en-us

-- Hemantgiri S. Goswami (http://www.sql-server-citation.com )

11/23/2011 by Hemantgiri S. Goswami · 0

Download SQL Server 2012 Developer Training Kit

Tweet

Microsoft has released SQL Server 2010 Developer Training Kit which includes :

• Labs
• Demos
• and, Presentations

This kit will greatly help you learning Developer and BI Solutions.


One will require to have Windows 7 or Windows Server 2008 R2 to install this kit. Once you have download the kit, launch the installer and it will ask you which lab, demos and presentations you want to include and install them based on your choice.


Sounds interesting? Grab it from the link


-- Hemantgiri S. Goswami (http://www.sql-server-citation.com )

11/08/2011 by Hemantgiri S. Goswami · 0

Resolving 701 There is insufficient system memory to run this query

Tweet

In recent past while working on an assignment I have encounter an error 701 There is insufficient system memory to run this query . I had a quick look at the server and noticed that server is not configured proper for max memory, and have suggested client to make changes to the max memory settings for the server. While I've suggested changes I have quote two articles, thought they would be a help to you as well to understand better on how SQL Server Memory managed. An article by SQL Server MVP Jonathan Kehayias will help you understand the memory management very well. 




1) http://sqlblog.com/blogs/jonathan_kehayias/archive/2009/08/24/troubleshooting-the-sql-server-memory-leak-or-understanding-sql-server-memory-usage.aspx


2) http://support.microsoft.com/kb/912439

-- Hemantgiri S. Goswami (http://www.sql-server-citation.com)

11/07/2011 by Hemantgiri S. Goswami · 0

DATABASE_OBJECT_CHANGE_GROUP do not audit SP or other object

Tweet

Deepak Kumar (friend of mine and founder of http://www.sqlknowledge.com )  were chatting yesterday. We were discussing about audit feature in SQL 2008, Deepak has enabled this feature for one of his client since month. And when he was looking at the log he found that there were entries but they are related to Tables only, and not other objects like SP(s). 

We were discussing and google about the same and found an entry in connect where in it was answered.  

If we need the other objects to be audited we have to add SCHEMA_OBJECT_CHANGE_GROUP to the audit specification. Here is an excerpt from the connect:

Thanks for your feedback. The behaviour you're seeing is by-design. In order to audit CREATE/DROP of an SP, you need to add the SCHEMA_OBJECT_CHANGE_GROUP to your audit specification. The DATABASE_OBJECT_CHANGE_GROUP is actually auditing the ALTER permission check on the SCHEMA as part of the CREATE statement. 

You can find the complete entry and more information at connect article  


-- Hemantgiri S. Goswami (http://www.sql-server-citation.com )

10/29/2011 by Hemantgiri S. Goswami · 0

Refresh QA Database with Manual Scripts

Tweet

Couple of week back Megha Sharma send me an email with a document attached, this document is all about how to refresh QA database with manual script. Here is the preview of the document, and the reason why we should follow the method in her own words:

QA environment frequently needs Database refresh and hence space on its disks. QA Database is refreshed from Production followed by a data purge, which leaves a lot of free space in the database. In order to release the free space, we follow database shrink command, a lengthy & single thread I / O operation, also cause high data fragmentation. To avoid this resource & time consuming activity, we do a Refresh Database with manual scripts, in which, we create a new database, transfer tables, data & other objects via scripts. The Generate Scripts option of a database generates script for the complete database and transfers data (using Insert into command, highly logged), to avoid this we do a Select * into, to transfer table definition & data (minimal logged & fast, being a bulk operation) & then generate Table Objects (Keys, Constraints, Triggers, Indexes) via manual scripts & other database objects (Views, Stored Procedures, Functions, Users, Roles, Schemas) via generate scripts.

Sounds interesting ? Want to download the complete document ? Download Link

Please post back your review in the comment section here.


-- Hemantgiri S. Goswami (http://www.sql-server-citation.com )

10/28/2011 by Hemantgiri S. Goswami · 0

workaround for orphaned users

Tweet

The server principal  is not able to access the database  under the current security context, is the error message appears for those users who was able to access the database / application earlier especially after refreshing database from production to dev, test or uat enviornment when the correspondent login is dropped.
The reason is that, when we restore the database the the SID for the user mismatches, and it became orphaned. The workaround for this issue is very simple, you will have execute the below code

 -- This part will report if there are orphaned users    
USE 'yourdbname'    
GO    
sp_change_users_login     
    @Action='Report'    
GO    
-- This part will fix orphaned users    
USE 'yourdbname'    
GO    
sp_change_users_login     
    @Action='update_one'    
    ,@UserNamePattern='youruser'    
    ,@LoginName='yourlogin'    
GO

-- Hemantgiri S. Goswami (http://www.sql-server-citation.com )

9/27/2011 by Hemantgiri S. Goswami · 0

SQL 2008 R2 Administration Cook Book

Tweet

Couple of week back I have received a special gift, from the person whom I admire as a elder bro and mentor – Satya SkJ. Recently he has written a cook book on SQL Server 2008 R2 Administration, forward written by MVP Brad McGehee, and reviewed by Vinod Kumar M – the most admirable person in SQL Server Community in INDIA. I am very excited to read this book thoroughly, the thing I like most in every chapter is “How it works”  section. Very soon I will post detailed review for this book.

You can find the detailed information about each chapter at the below location :
                                                  Chapter 1: Getting Started with SQL Server 2008 R2
Chapter 2: Administrating the Core Database Engine
Chapter 3: Managing the Core Database Engine
Chapter 4: Administering Core Business Intelligence Services
Chapter 5: Managing Core SQL Server 2008 R2 Technologies
Chapter 6: Improving Availability and enhancing Programmability
Chapter 7: Implementing New Manageability Features and Practices
Chapter 8: Maintenance and Monitoring
Chapter 9: Troubleshooting
Chapter 10: Learning the Tricks of the Trade
Appendix: More DBA Manageability Best Practices



Sounds interesting ? You can purchase the one for you at Link => https://www.packtpub.com/microsoft-sql-server-2008-r2-administration-cookbook/book#in_detail



-- Hemantgiri S. Goswami (http://www.sql-server-citation.com)

9/26/2011 by Hemantgiri S. Goswami · 0

CU 16 for SQL Server 2008 SP1

Tweet

 

Microsoft has recently released a Cumulative Update (CU) # 16 for SQL Server 2008 SP1 which contains hotfixes which were resolved after SQL Server 2008 SP1; once you will install / apply this CU the server version would be 10.00.2850.00

As always it is recommended that you test this CU in the development/test/QA environment first before you roll it out in production environment. Once you apply this CU, you might have to restart you server.

Download => Cumulative Update # 16

Find KB article which explains what is a different model for Incremental Servicing Model (ISM) like Critical On-Demand known as COD, On Demand known as OD, Cumulative Update known as CU and General Distribution Release known as GDR. Here is the reference link => Incremental Servicing Model

-- Hemantgiri S. Goswami (http://www.sql-server-citation.com )

9/23/2011 by Hemantgiri S. Goswami · 0