What’s new in SQL Server RC0 setup

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

Read More

Revamping Surat User Group

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 )

Read More

Maintenance Plan mystery

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 )

Read More

Using NOLOCK hint

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/) 

Read More

Download SQL Server 2008 SP3 CU2

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 )

Read More

Download SQL Server 2012 Developer Training Kit

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 )

Read More

Resolving 701 There is insufficient system memory to run this query

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)

Read More

DATABASE_OBJECT_CHANGE_GROUP do not audit SP or other object

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 )

Read More

Refresh QA Database with Manual Scripts

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 )

Read More

workaround for orphaned users

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 )

Read More

SQL 2008 R2 Administration Cook Book

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)

Read More

CU 16 for SQL Server 2008 SP1

 

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 )

Read More

SQL Server DBA Quiz on BeyondRelational

Friend of my mine and fellow MVP Jecob Sebastian is running SQL Server DBA Quiz 2011 on his community web site – BeyondRelational.  He asked me to submit question as a Quiz Master, so, here is my question for you all :

You have an SQL Server Integration Service (SSIS)  installed on some machine and you have client tool installed on your local desktop, when you tried to connect to the machine you have SSIS service running on from your local desktop you will get an error “RPC server is unavailable”

You will have to let us know what the root cause of this issue and how to resolved it.

Know the answer already?

Click Here to Submit an Answer

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

Read More

SQL Server Wait Types in SQL 2008

While working on an assignment of troubleshooting performance issue few month back I have come across a situation where I have to provide good information about the different waittypes and what they do, while researching a good documentation on wait types I have found the most authentic information on PSS Engineer’s blog where Bob Ward has put them nicely. This blog was published in 2009 but its good to keep handy as reference.

Most of the time wait type says us where the bottleneck are, if you wait types like IO_Completion, PAGE IO LATCH, this is indication for disk IO subsystem has some bottleneck. We have to use perfmon counter to verify this and then work around on resolving this issues.

Let me quote some links for the further reading:

http://blogs.msdn.com/b/psssql/archive/2009/11/03/the-sql-server-wait-type-repository.aspx

http://blogs.msdn.com/b/psssql/archive/2009/11/24/doctor-this-sql-server-appears-to-be-sick.aspx

http://support.microsoft.com/kb/822101

http://blogs.msdn.com/b/psssql/archive/2009/07/08/q-a-on-latches-in-the-sql-server-engine.aspx

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

Read More

Overcome update conflict in P2P replication

When we have Peer to Peer (P2P) replication in place on SQL Server 2008, probably we would see a conflict

1) conflict of type 'Update-Update' was detected at peer 2 between peer 1 (incoming)

2) conflict of type 'Update-Delete’ was detected at peer 2 between peer 1 (incoming)

Microsoft has confirmed that this is known issue with P2P replication, and Microsoft has released a fix for this with Cumulative hot fix for SQL Server 2008 SP1. I am penning down some links for you as a point of reference for troubleshooting P2P conflict.

Paul Libson has wrote an excellent article on how to handle P2P replication

http://support.microsoft.com/kb/973223

http://technet.microsoft.com/en-us/library/bb934199.aspx

http://technet.microsoft.com/en-us/library/ms147366.aspx

http://technet.microsoft.com/en-us/library/ms151865.aspx

 

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

Read More