sql-server-maintenance-solution
sql-server-maintenance-solution copied to clipboard
olahallengren
Moving the SQL Server Maintenance Solution to its own schema?
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?
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?
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.
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
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.
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]?
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).
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 :)
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.
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.
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.
@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.
@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.
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.
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...
@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
@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.
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. ;-)
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
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.])
On second hand, why not name the schema Sweetness because that is what the software is?
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.
About making the schema name configurable:
There are some downsides with that.
-
I would need to use SQLCMD variables, and users would have to use SQLCMD Mode in SSMS. (Not all users are familiar with that.)
-
The user could choose dbo, or some other existing schema, and then there could still be naming conflicts.
-
A user could end up with having multiple versions of the maintenance objects in the same database, but in different schemas.
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.
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
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.
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)?
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.)
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.
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.