Memory Counter Changes In SQL 2012

Microsoft has removed several performance counters significantly used prior to SQL 2012 versions for performance tuning. Alternatives have been added that suit more after the Memory Changes in the new engine.


Here are the Details.

Enterprise Policy Management Framework with SQL Server 2008

If you have dozens of SQL instances (including sql 2000, yukon, katmai) running within the enterprise; policy management wouldn't have been so easy!

http://msdn.microsoft.com/en-us/library/dd542632.aspx

http://www.codeplex.com/EPMFramework

How to login to SQL Server as SA while running Windows Authentiaction only mode

A colleague of mine ran into following situation:
My Company polocies enforce that domain-admins group be removed from sysadmin role in production SQL Server and windows authentication mode should only be used.
He removed the domain-admin group from sysadmin roles , then set windows authentication only and restarted the server at night.
When he tried to log in back with his domain user account (which was part of domain admin group too) he could only log on to master db with public role only!!!!!!!!!!!!!!!!!!!!!
He forgot to add his own account explicitly in SQL Server before removing domain-admins group.

Interesting situation:

• Domain-admins removed from SQL sysadmin role
• SQL running Windows Authentication only Mode.
• No windows user exist in SQL with sysadmin role.

Steps to log in back to SQL with sysadmin rights using SA?
Besides my colleague was planning to run rebuildm.exe utility to rebuild the master db , and then setting up logins and all user databases using scripts.
I tried something on my local test machine and found that there's a way!

I opened Enterprise Manager and tried to connect using SA with SQL authentication; Error: Login Failed for user 'sa'. Reason: Not associated with a trusted SQL Server connection.
hmmmm! OK
Solution:

• I logged into the operating system running production SQL machine using local admin account.
• Checked that NAMED-PIPES are enabled under the Server network utility.
• Start --> Program Files --> Microsoft SQL Server --> SQL Server Client Network Utility
• Goto Alias, Select Add.
• Under network libaries , selected NAMED PIPES
• Gave server alias prod1
• Under Server Name typed the actual SQL instance name (in our case PRODUK1)
• Clicked OK.
• Open the Enterprise manager again
• New SQL Server Registration.
• Under the server, typed the alias: prod1
• Selected, Use SQL Server authentication
• user name : SA
• password : typed the password
• clicked OK.

Successfully connected using sa account! :)

This isn't a security breach; since you already logged in to the machine locally, that means your are an authenticated user to the system. NamedPipes library only supports connections that are valid windows logins. You can not connect using NamedPipes over the WAN or out of a trusted domain. Your Windows login must be authenticated on the machine running SQL Server before in order to use NamedPipes!


Cheers!

RAM and procedure cache issue resolved in SQL 2008 (Thanx to Adam Mechanic) :)

lack of RAM control and inefficiencies in SQL Procedure Cache very well addressed and escallated by the SQL Guru!
Microsoft introducing Resource Governer in sql 2008 sates that you can control the amount of RAM Proc Cache can acquire by defining resource pools. Details below:

http://sqlblog.com/blogs/adam_machanic/archive/2008/04/15/sql-server-procedure-cache-more-relief-on-the-way.aspx

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=293188

Career as a DBA

Database Administration comes into play in medium large and large enpterprises and organizations running database server boxes where the business can not tolerate downtime and data availability is very critical.
Such setups involve expensive hardware and an enterprise class DBMS solution , eg., MSSQL Enteprise Edition, ORACLE Enterprise Edition etc.
Usually the server boxes include SAN(Storage Area Network) or NAS(Network Area Storage) setup as clusters and connected to multiple active/active or active/passive server boxes. The SAN usually connects to the server through a Host Bus Adapter and the medium could be Fiber Channel or some other fast communication channel.
NAS connects to the server via a Switch and has much more flexibility of placing it physically farther than the servers.
The disk drives in NAS, SAN, or the Server itself are fast SATA or SCSIs disks bundled up in different level of hardware RAIDS for faster read/writes and fault tolerance.
Many people focus more on multiple high speed processors with not quite edequate L2 Cache. As a general rule of thumb, a PIII processor running on a board with greator L2 Cache would outperform a P4 Processor running with inadequate L2 Cache.
A DBAs task also involves selecting and configuring RAIDs to serve different kind of I/O activity; example is that you require different RAID for sequential Reads/Writes and different for non sequential Reads/Writes. One also needs to accommodate the fault tolerance.
Technically speaking, DBAs are most prone to waking up nights and staying long at office for backups , performance and downtime issues. The business stucks if the db server goes down; that adds your value to your organization more than a Developer(no offence). DBA is a hot and fun job, but you also take stress along with you at home with a fear of being called late night for assistance:)

A DBA tasks include:
Disaster Recovery Plans (including backups, restore testing)
Performance Tuning (Includes hardware and Server performance monitoring, SQL optimization)
Development (TSQL or PLSQL, whatever dbms you have)
Security (Audits, user roles and permissions, patterns of transactions etc)
Reporting (Reports server, including automation of reports for management and staff)

As of my past experience Databases are most prone to DBAs than any thing; to err is human :). I don't believe if any of the db gurus never caught up in a disasterous situation of a messed up database, the quality that seperates them from ordinary db guys is, how confidently they react by keeping the nerves in control and resolving the problem in best possible way with minimum down time.
Many smart and compitent Tecchy folks turn to the DARK END; the so called MANAGEMENT. As it's said, when you get technically experienced and older in your field; your technical skills become secondary to your management skills. Your spoken and writting skills, dressing and attitude decides how far you'll go.
Or in other words; Degree of Management Skills is INVERSELY proportional to that of Technical Skills.:)


Cheers!