Archive for the ‘SQL’ Category

SCOM Datawarehouse-Event31551 Failed to store data in the Data Warehouse

March 15, 2017

After I updated the SQL management pack to latest release we did saw below error related to SCOM DW system

Failed to store data in the Data Warehouse. The operation will be retried. Exception ‘SqlException’: Login failed for user ‘SCOMAccount’

image

since the latest SQL management pack update there are now new SQL RunAs profiles and one which is called “Data Warehouse SQL Server Authentification Account”. In my case the SCOM Action account was entered here and this is no permission on SQL to login so above error is correct. adding the right account fixed the issue and DW was back healthy state and data now gets pushed out of the OperationalDB to DW

image

image

again and again but reading the SQL management pack is a must for having a healthy SQL monitoring which is usually business critical databases

Microsoft System Center Management Pack for SQL Server
https://www.microsoft.com/en-US/download/details.aspx?id=10631

…Stay tuned and happy SCOM’ing!

best practices for running AV on SQL servers

September 25, 2014

Running Antivirus software is critical part of server security but it is also important to understand the impact and effect it has on SQL Server. here is some really good guidance on how to configure AV and exclusions.

1. SQL Server Data Files (files with extensions like .MDF, .NDF)
2. SQL Server Log Files (files with extensions like .LDF)
3. SQL Server Backup files (files with extensions like .BAK and .TRN)
4. Full-Text Catalog Files
5. SQL Server Audit Files
6. SQL Server Query Files (Extensions like .SQL)
7. Trace/Profiler Files (Extensions like .TRC)
8. Entire Directory that is holding Analysis Services files that is used for processing Cubes and used for queries that are reading files in to the data folder and the temp folder.
9. Folder where Analysis Services Backups Files Reside
10. Exclude SQLServer.Exe file (This is the main database engine file)
11. ReportingServicesService.Exe
12. MSMDSrv.exe
13. Cluster Directory (Usually it is C:\Windows\Cluster) *If your servers are clustered
14. Cluster Quorum Drive
15. FILETABLE and Filestream folders

Reference full article here

YOU HAVE LOST ACCESS TO SQL SERVER, HOW TO FIX-IT?

June 14, 2011

You are working as a trusted DBA responsible for some extremely important SQL Servers for your company. For the sake of security, you have performed the following steps to secure SQL Servers:

  • You have removed any and all built-in administrators account from SQL Server logins
  • You have removed all the users (except SA) that were part of SYSADMIN server role (Including any Windows Accounts and/or SQL Server logins)
  • You have set the password of SA to something extremely complex which is hard to remember
  • For day-to-day operations on SQL Server, you use your domain user account which has DBO permissions on couple of databases but doesn’t have SYSADMIN privileges.

Since you set the SA password to be complex and you have not been using it, you forgot the SA password. You are the only person in the company who would know the SA password and now you have lost the SA password.

What would you do now?

Some quick options I can think of are listed below:

1. You will try to look for the SA password on your computer hard-drive or in your emails (If you stored it in some file which is a bad practice)

2. You will rebuild Master database or reinstall SQL Server and attach all the user databases. However, this could take some time and also doesn’t guarantee that all your logins, users, permissions and server configurations will be recovered unless you plan to restore the Master database from an old backup. However, as you don’t remember the SA password, restoring the Master database will not help you and you are back to square one.

3. You will call up Microsoft PSS

You are now running out of options. What would you do?

There’s a way with which you can gain SYSADMIN access to your SQL Server. However, that would mean your Windows account will need to be a member of the local administrators group.

SQL Server allows any member of Local Administrators group to connect to SQL Server with SYSADMIN privileges.

Here are the steps you will need to perform:

1. Start the SQL Server instance using single user mode (or minimal configuration which will also put SQL Server in single user mode)

From the command prompt type: SQLServr.Exe –m (or SQLServr.exe –f)

Note: If the Binn folder is not in your environmental path, you’ll need to navigate to the Binn folder.

(Usually the Binn folder is located at: C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn)

2. Once SQL Server service has been started in single user mode or with minimal configuration, you can now use the SQLCMD command from command prompt to connect to SQL Server and perform the following operations to add yourself back as an Admin on SQL Server instance.

SQLCMD –S <Server_Name\Instance_Name>

You will now be logged in to SQL Server as an Admin.

3. Once you are logged into the SQL Server using SQLCMD, issue the following commands to create a new account or add an existing login to SYSADMIN server role.

To create a new login and add that login to SYSADMIN server role:

1> CREATE LOGIN ‘<Login_Name>’ with PASSWORD=’<Password>’

2> go

1> SP_ADDSRVROLEMEMBER ‘<Login_Name>’,’SYSADMIN’

2> go

To add an existing login to SYSADMIN server role, execute the following:

1> SP_ADDSRVROLEMEMBER ‘<LOGIN_NAME>’,’SYSADMIN’

The above operation will take care of granting SYSADMIN privileges to an existing login or to a new login.

4. Once the above steps are successfully performed, the next step is to stop and start SQL Server services using regular startup options. (This time you will not need –f or –m)

Kudos to Saleem H. from SQL CAT Team Winking smile

Regards

Ramazan

SQL IO Performance

February 19, 2009

SQL is one of the most I/O intensive business critical applications and especially here it is really important that you have a look at the best practices.

More Details about Performance, Disk Alignment, Storage Recommendations, I/O improvements ….. can be found at at :

Jimmy May his Blog (SQL CAT Member)

http://blogs.msdn.com/jimmymay/default.aspx

Troubleshooting SQL 2005 Performance

http://technet.microsoft.com/de-de/library/cc966540(en-us).aspx

SQL CAT (Customer Advisory Team)

http://sqlcat.com/

Database Mirroring Best Practices in regards to Performance

http://technet.microsoft.com/de-de/library/cc917681(en-us).aspx

Storage Top 10 – Best Practices

http://sqlcat.com/top10lists/archive/2007/11/21/storage-top-10-best-practices.aspx