Home » 2014

Yearly Archives: 2014

2014 – One more Year with you

The WordPress.com stats helper monkeys prepared a 2014 annual report for this blog.

Here’s an excerpt:

The Louvre Museum has 8.5 million visitors per year. This blog was viewed about 73,000 times in 2014. If it were an exhibit at the Louvre Museum, it would take about 3 days for that many people to see it.

Click here to see the complete report.

Cumulative Update – 5 for SQL Server 2014 RTM Is Now Available !

The 5th cumulative update release for SQL Server 2014 RTM is now available for download at the Microsoft Support site. Cumulative Update 5 contains all the hotfixes released since the initial release of SQL Server 2014 RTM.

Those who are facing severe issues with their environment, they can plan to test CU5 in test environment & then move to Production after satisfactory results.

To other, I suggest to wait for SP1 final release to deploy on your production environment, to have consolidate build.

KB Article For CU5 of SQL Server 2014 RTM

Previous Cumulative Update KB Articles:

Reference : Rohit Garg (http://mssqlfun.com/)

You can find and follow MSSQLFUN :-

http://www.facebook.com/mssqlfun

http://mssqlfun.com/feed

https://twitter.com/Rgarg86

Other Linked Profiles :-

http://www.sqlservercentral.com/blogs/mssqlfun/

http://social.msdn.microsoft.com/Profile/rohitgarg

http://www.toadworld.com/members/rohit-garg/blogs/default.aspx

http://beyondrelational.com/members/RohitGarg/default.aspx

Use of RPC in Cluster

Today, My one of friend come back from SQL DBA interview & share his review. One question about which he is looking for was “What is the use of RPC in cluster” ?

The Remote Procedure Call (RPC) [http://support.microsoft.com/kb/832017] system service is an interprocess communication (IPC) mechanism that enables data exchange and invocation of functionality that is located in a different process. The different process can be on the same computer, on the LAN, or in a remote location, and it can be accessed over a WAN connection or over a VPN connection. The RPC service serves as the RPC Endpoint Mapper and Component Object Model (COM) Service Control Manager. Many services depend on the RPC service to start successfully. RPC did not use fixed ports, it worked on port ranges. RPC also works on Named pipe for “File and Printer Sharing” services.

Windows Cluster use multiple port (at least 100 ports) for different process via RPC. This process can be available over same system, depended system, over LAN or WAN, Domain Controller etc. RPC works on range of ports instead of fix port. This feature of RPC makes it very useful. Sometime critical ports can be already in user by some other process at that time RPC can use other ports from available range for connectivity.

There is long list of services that depends on RPC [http://technet.microsoft.com/en-us/library/cc787851(v=ws.10).aspx] like : Telnet, DHCP, DNS, COM+, DTC, WINS etc.

You may face multiple error when RPC is not working like

1) Cluster name mention not found

2) Node1 not able to communicate to Node 2

3) File Share not working

4) MSDTC issues

5) Etc.

Reference : Rohit Garg (http://mssqlfun.com/)

You can find and follow MSSQLFUN :-

http://www.facebook.com/mssqlfun

http://mssqlfun.com/feed

https://twitter.com/Rgarg86

Other Linked Profiles :-

http://www.sqlservercentral.com/blogs/mssqlfun/

http://social.msdn.microsoft.com/Profile/rohitgarg

http://www.toadworld.com/members/rohit-garg/blogs/default.aspx

http://beyondrelational.com/members/RohitGarg/default.aspx

SQL Server Configuration Manager giving Error : The remote procedure call failed. 0x800706be

Issue : I am facing error “The remote procedure call failed. [0x800706be]” while trying to view SQL Services from SQL Server Configuration Manager.

Steps I perform to resolve the issue, Seems to be a WMI error :-

1) If using Window Server 2008 & above, Run command from command prompt to verify WMI repository : winmgmt /verifyrepository

If you got any error, Please contact your OS team for WMI rebuild.

2) Recompiled the MOF file –> mofcomp.exe "C:Program FilesMicrosoft SQL Server90Sharedsqlmgmproviderxpsp2up.mof"

3) Re-registered the SQL Server provider DLL –> regsvr32 "C:Program FilesMicrosoft SQL Server90Sharedsqlmgmprovider.dll"

4) Your issue should be resolved now. If you still face same issue that means there are chances that you may be running with different versions of SQL Server. If yes, Please try to patch SQL Server Management Studio (SSMS) with latest available Service pack.

Reference : Rohit Garg (http://mssqlfun.com/)

You can find and follow MSSQLFUN :-

http://www.facebook.com/mssqlfun

http://mssqlfun.com/feed

https://twitter.com/Rgarg86

Other Linked Profiles :-

http://www.sqlservercentral.com/blogs/mssqlfun/

http://social.msdn.microsoft.com/Profile/rohitgarg

http://www.toadworld.com/members/rohit-garg/blogs/default.aspx

http://beyondrelational.com/members/RohitGarg/default.aspx

TempDB Configuration Best Practices

1. Do not change collation of TempDB from the SQL Server instance collation.

2. Do not change the TempDB database owner from sa.

3. Do not drop the TempDB database.

4. Do not drop or revoke the guest user from the database.

5. Do not change the recovery model from SIMPLE.

6. Allow for tempdb files to automatically grow as required.

7. Ensure the disk drives TempDB resides on have RAID protection in order to prevent a single disk failure from shutting down SQL Server. Keep in mind that if TempDB is not available then SQL Server cannot operate.

8. Move the TempDB database to seprate set of disks.

9. Size the TempDB database appropriately.

10. Configure no. of TempDB database data files as per available C.P.U. Cores

✔ If no of cores < 8 then use the same number of data files as logical processors
✔ if no. of cores between 8 to 32 inclusive then 1/2 data files as logical processors
✔ if no. of cores > 32 then 1/4 data files as logical processors

11. Make each data file the same size; this allows for optimal proportional-fill performance.

12. Put the tempdb database on a fast I/O subsystem. Use disk striping if there are many directly attached disks.

13. Set the file growth increment to a reasonable size to avoid the tempdb database files from growing by too small a value. If the file growth is too small, compared to the amount of data that is being written to tempdb, tempdb may have to constantly expand. This will affect performance.

14. General guidelines for setting the FILEGROWTH increment for tempdb files

✔ If TempDB file size > 0 and < 100 MB then the recommended filegroup increment can be 10 MB
✔ If TempDB file size > 100 and < 200 MB then the recommended filegroup increment can be 20 MB
✔ If TempDB file size >= 200 MB then the recommended filegroup increment can be 10% OR any fix value depending on requirement or on basis of I/O system capabilities

15. Avoid shrinking TempDB (or any database) files unless you are very certain you will never need the space again.

16. Keep auto create statistics & auto update statistics OFF. It will create tempDB objects faster.

17. Keep auto close OFF

Reference : Rohit Garg (http://mssqlfun.com/)

You can find and follow MSSQLFUN :-

http://www.facebook.com/mssqlfun

http://mssqlfun.com/feed

https://twitter.com/Rgarg86

Other Linked Profiles :-

http://www.sqlservercentral.com/blogs/mssqlfun/

http://social.msdn.microsoft.com/Profile/rohitgarg

http://www.toadworld.com/members/rohit-garg/blogs/default.aspx

http://beyondrelational.com/members/RohitGarg/default.aspx

%d bloggers like this: