Press "Enter" to skip to content

Category: Administration

Oracle Password-Related Profile Settings

David Fitzjarrell takes a look at some settings:

Passwords expire, and, depending upon how various profiles are configured, accounts are either locked or provided a grace period during which the old password can be changed. In any recent enterprise password verification functions are provided to police new passwords to ensure a modicum of security. Let’s dig into what Oracle provides to assist in password security.

Basic elements of password security that Oracle provides start with the profile; listed below are the associated resources:

Read on for the available options you can set on a per-profile basis.

Leave a Comment

DBAs and Data Access

Brent Ozar wraps up a survey:

Last week, I asked if your database administrators could read all of the data in all databases. The results (which may be different from this post, because I’m writing the post ahead of time and the poll is still open):

In a lot of cases, this doesn’t really matter much. In places where it does matter (for example, reading protected health information or critical financial data), there should be controls in place. I’ve always been on the side of this issue that says that yes, you do need to be able to trust your administrators at the end of the day, because somebody’s going to need a way to get to that data in case of company emergency. But as a company grows and there are additional opportunities for division of labor and specialization, you do open up the possibility of stronger controls, proper auditing, limiting certain data access to privileged accounts, and consequences for violating the rules.

Leave a Comment

Column Expansion and Compression

Ed Pollack goes big or goes home:

One of the most hassle-prone changes that can be made in a SQL Server database is to widen an existing column. Depending on the column’s use and data type, this process may involve hundreds of schema changes, app downtime, and a mountain of risk-prone code to review and deploy. 

This article dives into a fun (and interesting!) strategy for widening fixed-width columns in SQL Server, to reduce downtime, risk, and runtime at the time when a column’s data type needs to be changed. 

If you have ever suffered through a lengthy INT to BIGINT release, then this guide is for you! 

Read on for a step by step guide on expanding a column with minimum downtime, as well as a quick description of row- and page-level compression and how you can use those to ensure you’re using as few bytes as necessary when storing a smaller number in a larger data type.

Leave a Comment

Measuring Delay in Availability Groups

Matt Gantz wants to see how far behind we are:

Availability Groups are useful for creating Highly Available (HA) database systems, but that doesn’t mean they are entirely immune to performance problems. In busy systems, limitations in the infrastructure can introduce replication lag that is severe enough to affect database performance in ways that aren’t immediately obvious.

In SQL Server Availability Groups, the relevant difference between synchronous and asynchronous replicas comes down to how and when a transaction on the primary server is considered “committed”: Although it is easy to measure the lag between asynchronous replicas by using the dashboard in SSMS or by querying the DMVs (Dynamic Management Views), it takes more work to find the latency between synchronous replicas. This article explains how to measure that latency using internal performance counters, offering a simple technique for monitoring the cost of synchronous replication.

Click through to read the whole thing.

Leave a Comment

Post-Install Configuration Tips for SQL Server

Kevin Hill shares some suggestions:

The SQL Server installer has gotten better: tempdb configuration, MAXDOP, and even max memory can now be configured during setup.

But don’t be fooled: there’s still a post-install checklist that can make or break your environment over time. If you’ve ever inherited a server that “just ran” for years and started getting slower over time you’ve likely seen what happens when this list gets ignored.

These are not in any particular order, but some do require a restart of the server or the SQL Server Engine service to take effect:

Click through for several tips. Most of them I agree without reservation. For a couple of them, like backup compression, there are specific circumstances in which I’d recommend against turning it on, namely when working with storage subsystems that deduplicate your data automatically. In that case, you might want to think harder about backup compression (or backup encryption, for that matter) before turning it on.

Leave a Comment

Managing TempDB Growth with Resource Governor

Haripriya Naidu digs into what’s new in SQL Server’s Resource Governor:

In this article, we’ll focus on how to control TempDB datafile growth using Resource Governor (RG).

  • RG is disabled by default and available only on Enterprise edition.
  • Until SQL Server 2022, RG could only manage user databases.
  • Starting with SQL Server 2025, RG can now manage TempDB as well.

Click through for the demo and additional information.

Leave a Comment

Stolen Memory in SQL Server

Chad Callihan is overdrawn at the memory bank:

When you specify the max amount of memory that SQL Server can use, you may think that’s all the memory to be used for the buffer pool. That’s not the case. SQL Server will use memory not only for the buffer pool but also in tasks such as sorts, memory grants, and other internal tasks. These tasks can swoop in and “steal” memory from the buffer pool, causing more work for SQL Server.

Click through for some notes on the topic, as well as your reminder that “128 GB for the buffer pool” in Standard Edition doesn’t mean “128 GB total memory usage for SQL Server.”

Leave a Comment

PostgreSQL for the Oracle DBA: Tuples and MVCC

Kellyn Gorman continues a series on PostgreSQL for Oracle DBAs:

After our first two weeks of ensuring Grant and I didn’t burn down SQLServerCentral figuring out how Steve Jones has kept the pace he has for so long, (quite an impressive feat, I think we’d both agree!) I’m back to working with my comparisons and building more knowledge in PostgreSQL.  What caught my attention this week was the simple concept of a row (or tuple) which might seem universal in relational systems, after all, data is data no matter the platform, right? But under the hood, the way databases store, manage, and control visibility of that data can differ drastically. This is especially true when comparing PostgreSQL with how Oracle manages rows.

Read on to learn more about how these systems work.

Leave a Comment

Zone Redundancy in Azure SQL Managed Instance

Arun Sirpal explains what zone redundancy is in Azure:

Do you know what happens when you enable zonal redundancy for your SQL managed instance?

Lets define it first (in the context of Business-Critical tier) – zonal redundancy is achieved by placing compute and storage replicas in different availability zones (3) and then using underlying Always On availability group to replicate data changes from the primary instance to standby replicas in other availability zones. 

Availability zones are in the same Azure region, so it works well for high availability but isn’t as good for disaster recovery: if an entire region goes down, zone redundancy won’t help you very much. Also, be aware that you’re paying for what’s running in those three zones because TANSTAAFL.

Comments closed