Moving the SQL Server Maintenance Solution to its own schema?

[olahallengren]

I am thinking about moving the SQL Server Maintenance Solution to its own schema.

The background for this, is that I am planning to introduce some new objects, and as the maintenance solution does not have its own database or schema, there is a risk that there could be naming conflicts.

I could create synonyms in the [dbo] schema for backward compatibility.

Does anyone see any issues with this?

About the schema name, I have been thinking about [ola] or [olahallengren]. [ola] is short and easy, but I am not sure that it is unique enough. [olahallengren] is a bit longer and more unique. Other ideas for schema names?

[srutzky]

Putting Maintenance Solution into its own schema is a great idea 😃 .

Creating synonyms in the dbo schema should be fine because if someone already has this installed in this particular DB, then those object names within that schema will already be reserved / in-use. Only thing to add here is that, ideally, creating the synonyms would be optional for those who do not need / want backwards compatibility. It might even be nice to default that option to "true" if those objects already exist, else keep as "false".

With regards to the schema name, it might actually be that ola is unique-enough to safely use (and it is short and easy). But, as with the synonym idea, this would ideally be configurable when installing, defaulted to ola or whatever the desired name ends up being. As for that default, with absolutely nothing negative in any way implied or intended, since the "product" is named "Maintenance Solution", why not go with something like MaintSol or something along those lines?

[dataflownow]

I already put the maintenance solution into its own schema [ola], but I agree it might not be unique enough for everyone. [olahallengren] would certainly be unique enough, I would think. Best regards.

[OliverUweHahn]

I would vote for [ola]. I would guess that 95% of all database objects are using the [dbo] schema. The chance that any application or any dba is unsing [ola] for something else is pretty limited. And even if any application is using [ola] as a schema name, we still can use a different database. If it needs to be unique and you often forget the URL of Ola's homepage, you could also do something like this: :-)

CREATE SCHEMA [https://ola.hallengren.com/] GO CREATE TABLE [https://ola.hallengren.com/].CommandLog( [ID] [int] IDENTITY(1,1) NOT NULL, [DatabaseName] [sysname] NULL, [SchemaName] [sysname] NULL, [ObjectName] [sysname] NULL, [ObjectType] [char](2) NULL, [IndexName] [sysname] NULL, [IndexType] [tinyint] NULL, [StatisticsName] [sysname] NULL, [PartitionNumber] [int] NULL, [ExtendedInfo] [xml] NULL, [Command] [nvarchar](max) NOT NULL, [CommandType] [nvarchar](60) NOT NULL, [StartTime] [datetime] NOT NULL, [EndTime] [datetime] NULL, [ErrorNumber] [int] NULL, [ErrorMessage] [nvarchar](max) NULL, CONSTRAINT [PK_CommandLog] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO

Best regards, Oliver

[ConstantineK]

Talked to a few peers and got their feedback:

Shorter

• ola - short and sweet, but people argue about the shorter schemas the more likely that it would conflict. Personally I would say its a pretty low chance of an ola schema in the target database.
• diag/maint - similar short naming conventions.
• ola_maint - shorter but still unlikely to clash.

Longer

• ola_hallengren/olahallengren - longer and less likely to crash, underscores for spacing if needed seemed like a strong contender.
• hallengren - a shorter version of the longer and less likely to clash, still clearly identifying where its coming from.

Personally I like ola (since we need to schema qualify all objects in the code that reference it) but since we dont call these procedures much (just underlying system objects) something like olahallengren sounds good to me.

[m60freeman]

With Azure SQL Database, we have to install the Maintenance Solution in the user database. We wanted to keep it out of dbo to separate it from the applications. We picked DBAdmin (we worried that we might run into future conflicts or easy mistyping if we used DBA). We also put other procedures in that schema, like Brent Ozar's sp_blitz* procedures. I don't know what benefit there would be to having a separate schema for just the Maintenance Solution objects, but I have no objection to it. [ola] might be short enough to have a conflict with something somewhere someday. Maybe [hallengren]?

[Rupp29]

My $0.02 - using schema called [ola] gets my vote, short & sweet and I would immediately know what it was. In other words, if I saw some other schema like [dba] or even [dbo], I might know it is a maintenance plan object... but [ola] tells me immediately that this maintenance object is from Mr. Hallengren's solution. (call it brand recognition). Anything longer than 3 or 4 characters is too long for a schema name (but that is just because I hate to type).

[sherriekubis]

My vote is to user a schema as it's easily found and identifiable. In our shop we may install many different databases into an instance, and using something that tells me the ownership is my preference.

I originally voted for [olahallengren], but see a lot of votes for [ola]. One is short and sweet, one is more descriptive. I'd be okay with plain old [ola}, but still vote for the longer, more descriptive name for clarity and lack of conflicts. I know, what is the chance of having another [ola] schema, probably not much.

I'm happy with either, but mostly happy that we have this product to use :)

[jeffchulg]

Hi everybody, why not OlaDbMaint or OlaDbMgmt ?

[srutzky]

If the general preference is to go with ola, why not use OlaMS for the schema name? If it is not obvious, the "MS" is short for "Maintenance Solution". This is only 2 more characters than "Ola" but almost certainly unique at this point.

Otherwise, similar to one of @ConstantineK's suggestions (also similar to one of @jeffchulg's suggestions): OlaMaint. The difference here is that it is pascal-cased to match the style used for the existing DB objects, rather than being all lower-case and using an underscore.

[lowlydba]

I like [ola] and [OlaMaint] as well, but [OlaMS] triggers my mind to go straight to MS = Microsoft due to how it is used in some system objects, so I think that could potentially be confusing for someone unfamiliar with it.

[cbailiss]

Absolutely support the idea of a dedicated schema, generally I dislike anything being in dbo. Could the name actually be configurable during set up, e.g. providing a version of the set up script using SQLCMD variables where the schema is a variable in the script (not sure if this has been discussed before here, apologies if it has). If a fixed name is preferred, [ola] seems a bit too short and doesn't provide much clue to its purpose. Personally, I'd prefer something like "dbmaintenance", "maintenance" or if the majority prefer the name then "olahallengren". But I appreciate it will be hard to get 100% agreement.

[ConstantineK]

@cbailiss I considered a dynamic schema but I figured that having the code dynamically generated would be pushing the scope a bit further than originally intended, and of course you would need to (as you said) either use sqlcmd (which may not be fully supported as an installation method) or dynamic SQL (which is a larger maintenance burden.)

This burden can be somewhat mitigated with a "compile" step when producing the final code for consumption, but it was what I was thinking over at the time.

edit: Also for what its worth I believe @olahallengren already has such a step that creates the full installer script, so maybe not as big a burden as I am thinking.

[cbailiss]

@ConstantineK Yep, I wasn't sure whether it would be overkill too. I can see there being broad agreement on a dedicated schema but then perhaps lots of debate about the name (and many places seem to have rigid naming standards). So I decided to raise the idea, but happy to have it discussed and rejected if that's the consensus.

[gregfaulk]

I've been implementing the maintenance solution in it's own schema for years by modifying each release by hand. I'd prefer a short schema name like [ola] or [olah]. If that produces a schema name collision then the user can simply rename the schema with global search and replace. Making the schema name configurable seems like excessive complexity to address what will likely be very rare name collisions.

[jeffchulg]

One other suggestion: why not create the solution without naming the schema (not even dbo). In that way, the first installation step would be to create an "orphan user" (or a SQL Server login mapped to a user) in the administration database with the default schema of your choice then run EXECUTE AS [your_user] and finally run the installation script.

Isn't it a way to comply with everybody's desire for schema name?

In addition, it would allow the use of the solution in an impersonation scenario...

[OliverUweHahn]

@jeffchulg: I am not a big fan of encapsulating commands with EXECUTE AS. The installation script is currently fully qualified. And we would need to change the script. There is currently also no need for an additional user in the database. I think we should keep it simple. If someone does not like [ola] or [olahallengren] (or whatever will be chosen), there is also the option to globally replace the schmea string in the installation script as already noted before. I also do not like dynamic SQL. The T-SQL code should still be readable. But this is only my personal preference and my 2c. Regards, Oliver

[jeffchulg]

@OliverUweHahn I totally agree with you, but the EXECUTE AS can be a manual (or scripted) operation before executing the installation script. Nevertheless, it's just a suggestion that I think quite simple to implement and flexible, that would allow those who have already changed the schema from previous versions to keep their habits.

[TiborKaraszi]

I like to keep the accidental DBA in mind, who want to keep the modifications of the install script to a minimum. This and the pretty high risk for conflict with only three characters means my vote goes to [olahallengren]. I would avoid stuff such as DbMaint (or similar) for conflict reasons as well as immediate recognition by schema of what it is. Also I loathe mixed case for these things. ;-)

[OliverUweHahn]

Hi Tibor, I cannot see a high risk when replacing "[ola]" to e.g. "[sla]". It is the same like replacing "[olahallengren]" to e.g. "[slahallengren]". As long as you use the square brackets. Ola uses always square brackets to write the schema name. If you do it manually without search/replace I can understand that this is a risk. Anyhow. The schema is just a name. For me it's the same which schema name wins. It is pretty easy to change the schema name afterwards. Of cause, it is always the best solution to just use the original code, otherwise you would run at risk to have multiple schemas, if you forget to replace it once you deploy a new script. :-) Best regards, Oliver

[bcdfeath]

Personally, I hate to put anything in master, model, & msdb. What I have done with your code is to create a single combined database on each SQL instance named Monitoring to hold your work, Brent Ozar’s sp_Blitz... series of sprocs, and my own periodic capture of file and wait stats. Everything in the Monitoring database is part of schema dbo because I am your typical lazy DBMonkey. If you do decide to break out your code to a different schema could you name it OH! Or Olé! ? (I live in Minnesota & am of Swedish heritage. I ALWAYS mis-pronounce your first name incorrectly, so I figure it should be mangled in a standardized fashion. [Minnesota has a lot of Sven & Ole jokes.])

[bcdfeath]

On second hand, why not name the schema Sweetness because that is what the software is?

[olahallengren]

Thank you for all the feedback. Please keep it coming.

Here are some thoughts.

Let's say that I have been developing a new version, that will drop and re-create some tables. How can I assure that the new version can be deployed safely everywhere? Dropping a table that is not mine, on one server, for one customer, would be a disaster.

Or let's say that I release a new version that has a new table. Shortly after some customers start reporting errors like this: "Invalid column name 'Column1'.". They got this error because they already had a table with the same name.

A schema with a very unique name would would solve these problems.

If a schema name is more common / has higher risk for conflicts, then I would need to do additional checks when I am dropping or altering a table.

[olahallengren]

About making the schema name configurable:

There are some downsides with that.

1. I would need to use SQLCMD variables, and users would have to use SQLCMD Mode in SSMS. (Not all users are familiar with that.)

2. The user could choose dbo, or some other existing schema, and then there could still be naming conflicts.

3. A user could end up with having multiple versions of the maintenance objects in the same database, but in different schemas.

[bcdfeath]

Absolutely agree.

The KISS solution is to use a unique schema name for all the installed work. Otherwise there has to be a full bore maintenance routine that does check everything.

Personally, I am leaning towards schema Sweetness at this time, though Sweet would be less typing.

Because your free software is so widely used, no matter what you do some bone-head will complain anyway instead of solving the problem.

In the end, there is no way that anyone can ensure that a schema name is not in use somewhere. About all you can do is to prominently warn folks all over the announcements & instructions.

Brad

P.S. I had already written optimizing indexing/statistics code in TSQL when I found yours. I dropped my parallel work like a rock when I found yours because it already had more in it than mine.

P.P.S.: This is ONLY because I am a lazy data monkey.

[bcdfeath]

Making the schema name configurable would turn into a maintenance death march for you. If that happens you will have to start charging for the software just for the headaches.

Pretty much anyone can open up the TSQL Scripts you deliver and mass-change the schema name you use to what they want. If you want to go crazy, you can write up instructions about how to do so. You have to assume a minimal amount of coding competence.

Brad

[drstonephd]

I used to manually update the script to change the schema name and the default path for backups. I'm not going to do this now that the path is configurable. I would welcome a change of the schema, but I would say to do as before and make it fixed. I would not worry about avoiding a "safe" long schema name because the setup is scripted. We will not probably be typing the schema very often.

If there is a schema conflict because a "OlaHallengren" identity thief is making objects, I'd say let them deal with it. They can manually update the script to use a different schema. Or they could deploy to a user database.

Before installing or updating, insuring the schema is the "real" ola schema would be important, just to be safe. Perhaps a version table in the schema would identify the objects. The objects could also be marked with an extended property that clearly identifies the object as being part of maintenance and provide a version.

BTW, is there any reason not to use a user database for the maintenance? If there is going to be a local table with configuration data for parameters, then it might be best to backup a user database rather than restore master should things go wrong.

As for dealing with table changes, perhaps create, transfer, rename, and rename? Create the new table with version in the name. Transfer data from the existing table. Rename the existing table with the old version number. Then rename the new table to not use the version number. Delete the old table if there is no error?

Another option might be to always include the version number the table names. A view, with no version in the name, could than be used to point to the current table in code.

If special code is needed to detect older "dbo" versions and cleanup, I'd keep it separate. It can eventually be ignored by existing users. New users will not need it at all, once a new schema is in place.

[olahallengren]

I was thinking about some things, about the length of the schema name:

When you going to execute one of the stored procedures, you need to specify the schema name, the name of the stored procedure, and some parameters with values. So regardless of the length of the schema name, the total command can be quite long.

Another thing is that the creation of the jobs are, in most environments, scripted (as drstonephd was writing).

In the cases where you need to execute a stored procedure ad hoc, aren't you then often copying the command (e.g. from the web site or from a job)?

[ConstantineK]

I would agree that length isnt nearly as important as it would be in a normal user database where you might create/script/query the objects regularly.

The only impact I can think of would be real are potential downstream projects, and even then its probably fairly minor because intellisense would just prompt based on the new version chosen, and no schema starts with o in the master database (if they are using the normal conventions.)

[m60freeman]

I've been using the Maintenance Solution for many years at three different employers with very different environments, and I don't recall ever executing one of the stored procedures outside of a scheduled or manual start of a job.

[OliverUweHahn]

I sometimes use the stored procedures also ad-hoc. Of cause, first and foremost the IndexOptimize and DatabaseBackup procedures are the ones I am using ad-hoc.

[scorellis]

Maybe go with Ola Hallengren Maintenance Solution (OHMS)...

[mwolfe02]

I vote for [olahallengren].

I waffled between [ola] and [olahallengren] for all of the reasons discussed previously. I ultimately sided with [olahallengren] because of its far greater discoverability and self-documenting nature for DBAs that are supporting environments they did not set up.

https://www.google.com/search?q=olahallengren (ola.hallengren.com is the top result)

vs.

https://www.google.com/search?q=ola (ola.hallengren.com is currently on page 7)

[cbaytala]

I would vote for [olahallengren] as well. It's longer but it identifies itself much more clearly. While I appreciate the short and sweet [ola] I feel that it lacks a certain amount of clarity on what it is. While not necessarily needed for everybody who is familiar with these Ola scripts or set them up themselves, I think there is significant value in using a naming convention which would make it quickly identifiable to anybody.

[drstonephd]

Not having met Ola, I don't know about short or sweet. :) I agree on "olahallengren". I was thinking a trademark symbol might be appropriate. After a few minutes of typing "olahallengren™" just now, I realize this would not be a good idea.

[drstonephd]

I do sometimes run the commands outside of a job. Even so, I copy the syntax of the command from the job and paste it into SSMS as a template.

[marcingminski]

Yes good idea @olahallengren. I have actually done something similar with your solution. I have put your original objects into their own schema and also renamed them to match existing naming standards within our maintenance database:

Procedures:

Tables:

Synonyms

Renaming objects is not an issue as I too have synonyms so no changes required to the code. I have this run for few years now on hundreds of instances so you are good to go ahead.

On a side note, I have also developed a wrapper procedure that takes parameters from a table which makes it easier to customise parameters per database without having too many agent jobs. I never got round to publishing it on GitHub but happy to send you a copy. This could become your sp_MaintenanceSolution

[gnotisauton]

I responded to @olahallengren 's e-mail two weeks ago, he pointed me to github and asked if I could put my comments here. So two more cents from my perspective :) I already move all of Ola's stored procedures onto a different schema. I have largely automated the process with a few release management stored procedures. These stored procedures also include default schedules for jobs and some other settings (like updating statistics in IndexOptimise). Needless to say, I'd be quite happy if the new version of the Maintenance Solution lived on its own schema.

I don't mind [ola], [olahallengren], [OlaHallengren] (for those audacious enough to have created a case sensitive MSSQL instance), or [CoolestDBAinSweden] for that matter, so long as it stays the same.

Here's my list of tricky places that I needed to change maintenancesolution.sql to make it work on my schema [OlaHallengren]:

• replace "schemas.[name] = 'dbo'" with "schemas.[name] = 'OlaHallengren'"
• replace "dbo.CommandLog" with "OlaHallengren.CommandLog" (in [dbo].[CommandExecute])
• replace '-Q "EXECUTE [dbo].[' with '-Q "EXECUTE [OlaHallengren].[' (Agent job creation)
• replace "[dbo].[CommandLog]" with "[OlaHallengren].[CommandLog]" (in [dbo].[CommandExecute])
• replace "[dbo].[PK_CommandLog]" with "[OlaHallengren].[PK_CommandLog]" (in [dbo].[CommandExecute])
• replace "[dbo].[CommandExecute]" with "[OlaHallengren].[CommandExecute]", !! Except the DROP PROCEDURE and CREATE PROCEDURE statements !!

Everything else (moving from [dbo] to [OlaHallengren]) was handled by the following code:

CREATE PROCEDURE [Meta].[PostUpdate_generic]
(   -- the schema that contains the old versions of the stored procedures
        @toSchema nvarchar(255)
    -- the schema that contains ONLY the new versions of ALL stored procedures
    ,@fromSchema nvarchar(255) = '255'
)
AS
BEGIN

    SET NOCOUNT ON;

    -- check parameters (also prevents SQL injection)
    IF( SCHEMA_ID(@toSchema) IS NULL )
    BEGIN
        RAISERROR( 'Target schema [%s] does not exist', 11, 1, @toSchema );
        RETURN;
    END
    IF( SCHEMA_ID(@fromSchema) IS NULL )
    BEGIN
        RAISERROR( 'Source schema [%s] does not exist', 11, 1, @fromSchema );
        RETURN;
    END

    -- determine which procedures to move
    SELECT   SPECIFIC_NAME
            ,SPECIFIC_SCHEMA
    INTO    #relevant_routines
    FROM    INFORMATION_SCHEMA.ROUTINES
    WHERE   SPECIFIC_CATALOG = 'mgmtDB'
        AND SPECIFIC_SCHEMA IN (@toSchema,@fromSchema)

    -- check if no procedures are missing or new (to prevent "oops" when specifying the wrong schemas)
    DECLARE  @disparity INT

    SELECT  @disparity = COUNT(*)
    FROM    (   (   SELECT  SPECIFIC_NAME
                    FROM    #relevant_routines
                    WHERE   SPECIFIC_SCHEMA = @toSchema
                EXCEPT
                    SELECT  SPECIFIC_NAME
                    FROM    #relevant_routines
                    WHERE   SPECIFIC_SCHEMA = @fromSchema
                )
            UNION
                (   SELECT  SPECIFIC_NAME
                    FROM    #relevant_routines
                    WHERE   SPECIFIC_SCHEMA = @fromSchema
                EXCEPT
                    SELECT  SPECIFIC_NAME
                    FROM    #relevant_routines
                    WHERE   SPECIFIC_SCHEMA = @toSchema
                )
            ) a;

    IF( IsNull(@disparity,1) <> 0 )
    BEGIN
        RAISERROR( 'Objects [%s].* do not match [%s].*. Please compare manually', 11, 1, @fromSchema, @toSchema );
        RETURN;
    END

        -- actually move the procedures
-- begin loop preamble
        DECLARE C CURSOR
            FOR SELECT DISTINCT SPECIFIC_NAME
                FROM    #relevant_routines

        OPEN    C;
        DECLARE @c nvarchar(255);
        DECLARE @sql varchar(MAX);

        FETCH NEXT FROM C
        INTO    @c

        WHILE @@FETCH_STATUS = 0
        BEGIN
-- end loop preamble        

            SET @sql = 'DROP PROCEDURE [' + @toSchema + '].[' + @c + '];
                        ALTER SCHEMA [' + @toSchema + '] TRANSFER [' + @fromSchema + '].[' + @c + '];'

            EXEC( @sql );

-- begin loop postamble
            FETCH NEXT FROM C
            INTO    @c
        END

        CLOSE       C;
        DEALLOCATE  C;
-- end loop postamble

END

[OliverUweHahn]

Hi @gnotisauton , I would guess Ola will implement the schema change for us. ;-) Once it is in a separate schema, you only need to replace "[olahallengren]" to "[your_preferred_schema]". Nothing more to do. :-) Best regards, Oliver

[gnotisauton]

Yes, and I won't be moving it after that. Just thought I'd include it because it has a few more release management considerations in there (check for name changes / new procedures) :).

[OliverUweHahn]

Yes. Definitely. :-) I will go with the standard as well. I did many many changes in the past. But, with the addition of the latest features, I am glad to say "I will leave it as it is". :-) Regards, Oliver

[jseymour84]

Usually the way I deploy the maintenance script is to create a new database and then a new schema inside that database. That database exists as a Visual Studio project in source control so when my scripts get updates I can generate an update script and use DBATools to deploy them to my servers.

I also use Brent Ozar's First Responder Kit so inside my DBAdmin database I have an [ola] schema and a [brent] schema just in case naming conflicts every pop up in future releases.

I am not a fan of having maintenance scripts in a system database such as master just because I like to keep those pristine. However, I am a fairly new DBA so my preferences might not be the best practices.

[SQLNerd]

ohms - Ola Hallengren Maintenance Solution

[drstonephd]

SQLNerd, you might get some resistance to ohms. :)

[crankydba]

Being loathe to put anything in master like some others, we use a DBAMaintenance database for the maintenance solution and a few other useful things. Using an ola schema would invalidate the existing jobs, schedules, etc. as well as any custom jobs we've done. Making those changes on forty servers would be inconvenient, but setting a variable @SchemaName in the installation script would make that easy to maintain compatibility with my existing environment. I do understand your justification for wanting a new schema, and agree it would be beneficial. It would be more convenient in my environment if that schema name was configurable. [ola] as default, but let me change it to [dbo].

Thanks for the great tools, Ola!

[drstonephd]

If it is hard coded as [ola], it would not be that hard update the script with a replace all on " [ola].[" with " [dbo].[". It's just one more customization you will need to do to be different. I did this the other way to customize my schema to "maint". I am trying very hard not to customize now, using dbo in my next deploy...or ola.

I think a simple script to update job steps to change the "[dbo]" to "[ola]" would be one way to handle the change on many servers. However, I would like something more complete to handle jobs, schedules and possible updates to these.

[olahallengren]

Using an ola schema would invalidate the existing jobs, schedules, etc. as well as any custom jobs we've done. Making those changes on forty servers would be inconvenient, but setting a variable @SchemaName in the installation script would make that easy to maintain compatibility with my existing environment.

The idea is to create synonyms in MaintenanceSolution.sql for backward compatibility.

There are some downsides with making the schema name configurable. Please see my comment from July 13.

[christianspecht]

For me, the schema change wouldn't be necessary because I have a separate "DBA" database which contains your maintenance solution, Brent Ozar's First Reponder Kit etc.

So as long as the schema change doesn't break anything I have already set up, I'm fine with it (and I don't care about the schema name).

[mkhawaja72]

I think it would be fine having its own schema.

[jeffchulg]

Hi there,

here is a point that could be interesting for all....

Instead of moving all objects to a schema (and possibly break existing installations), why not provide a script that allows an end user to process the scripts and generate a version that meet their desire?

It's not finished yet, but I developed such a PS script for my own usage of Brent Ozar's first responder kit

I'm attaching the current version just in case!

Prepare-NewSourceFilesForSrcInclusion.ps1.txt

[JulioIzquierdo]

I'm new here and haven't looked at the script yet but couldn't this script be installed in its own database where it can utilized by all databases in the instance? Does the script need to be installed in each database in order to perform maintenance?