MS SQL – EugeneChiang.com

SQL Server Agent Fixed Database Roles

https://learn.microsoft.com/en-us/sql/ssms/agent/sql-server-agent-fixed-database-roles?view=sql-server-ver16 SQL Server has the following msdb database fixed database roles, which give administrators finer control over access to SQL Server Agent. The roles listed from least to most privileged access are: SQLAgentUserRole SQLAgentReaderRole SQLAgentOperatorRole SQLAgentUserRole Permissions SQLAgentUserRole is the least privileged of the SQL Server Agent fixed database roles. It has permissions on only operators, local jobs,... » read more

Read

echiang written 3 days ago

Deploy SSIS Package to SQL Server from .dtsx package file

Open up SSMS -> Connect Integration Services… (Administration mode) Expand the “Stored Packages” folder -> MSDB folder -> right click and select “Import Package” Package location: File System Package path: C:\Deployment\MyPackage.dtsx Package name: MyPackage Protection level: Keep protection level of the original package Note: If package already exist in the MSDB, will get a prompt... » read more

Read

echiang written 2 weeks ago

Custom Database Maintenance Scripts for Backup, Integrity Check, and Index

https://ola.hallengren.com/

Read

echiang written 3 weeks ago

An installation package for the product Microsoft OLE DB Driver for SQL server cannot be found: msoledbsql.msi

Error: Error Description: An installation package for the product Microsoft OLE DB Driver for SQL server cannot be found, Try installing again using a valid copy of the installation package ‘msoledbsql.msi’ Fix: Uninstall all OLE and ODBC drivers and re run the setup. References: https://stackoverflow.com/questions/71857345/error-when-installing-microsoft-sql-server-2019-cannot-find-the-microsoft-ole-d

Read

echiang written 3 weeks ago

How to Think Like the SQL Server Engine – Brent Ozar Unlimited®

How to Think Like the SQL Server Engine – Brent Ozar Unlimited® You’re a developer or a DBA, and you’re comfortable writing queries to get the data you need. You’re much less comfortable trying to design the right indexes for your database server. In my totally free How to Think Like the Engine class, you’ll... » read more

Read

echiang written 4 weeks ago

Brent Ozar Training

Homepage | Brent Ozar Unlimited Brent Ozar Unlimited Level 2 Bundle | Brent Ozar Unlimited The BrentOzar.com Annual Black Friday Sale – Brent Ozar Unlimited® You’ve used sp_Blitz and the First Responder Kit to learn a lot about your databases. You read the blog regularly, subscribed to the free newsletter, watched some of my YouTube videos, maybe... » read more

Read

echiang written 1 month ago

Changing Database Column Order

Issue: Changing the order of database table columns. Resolution: Have to re-create the table in order to change the order of the database column. Right click on table -> Designers -> Re-arrange table column -> Save Note: Make sure the “Prevent saving changes that require table re-creation” is unchecked. Change this my going to SQL... » read more

Read

echiang written 1 month ago

What is the usage of UPDLOCK in Select and Update statement

The function of using UPDLOCK is to allow users to read data first (and not to block other users from reading data), and to ensure that when the data is updated later, the data has not been modified by other users during this period of time. When this option is selected, SQL Server uses a... » read more

Read

echiang written 2 months ago

SQL Availability Group Setup – The local node is not part of quorum

Problem While trying to set up a couple of SQL Enterprise servers in an Availability Group I kept getting the message “The local node is not part of quorum and is therefore unable to process this operation” I had already set up Windows Failover Clustering with a cloud witness for the quorum. and ticked “Enable AlwaysoOn... » read more

Read

echiang written 3 months ago

Not able to see a list of job history due to incorrect server name

Issue: When a server is created via replication, the sql server name might need to be changed. If not, you will see login issues in the database log and unable to see the job history. Resolution: Remember to restart SQL Server to apply the change.

Read

echiang written 3 months ago