Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Moving the SQL Server Maintenance Solution to its own schema? #91

Open
olahallengren opened this issue Jul 10, 2018 · 60 comments · May be fixed by #199
Open

Moving the SQL Server Maintenance Solution to its own schema? #91

olahallengren opened this issue Jul 10, 2018 · 60 comments · May be fixed by #199
Labels

Comments

@olahallengren
Copy link
Owner

@olahallengren olahallengren commented Jul 10, 2018

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
Copy link

@srutzky srutzky commented Jul 10, 2018

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?

@baodad
Copy link

@baodad baodad commented Jul 10, 2018

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
Copy link

@OliverUweHahn OliverUweHahn commented Jul 10, 2018

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
image

Best regards,
Oliver

@ConstantineK
Copy link

@ConstantineK ConstantineK commented Jul 10, 2018

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
Copy link

@m60freeman m60freeman commented Jul 10, 2018

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
Copy link

@Rupp29 Rupp29 commented Jul 11, 2018

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
Copy link

@sherriekubis sherriekubis commented Jul 11, 2018

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
Copy link

@jeffchulg jeffchulg commented Jul 11, 2018

Hi everybody,
why not OlaDbMaint or OlaDbMgmt ?

@srutzky
Copy link

@srutzky srutzky commented Jul 11, 2018

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
Copy link

@LowlyDBA LowlyDBA commented Jul 11, 2018

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
Copy link

@cbailiss cbailiss commented Jul 11, 2018

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
Copy link

@ConstantineK ConstantineK commented Jul 11, 2018

@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
Copy link

@cbailiss cbailiss commented Jul 11, 2018

@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
Copy link

@gregfaulk gregfaulk commented Jul 11, 2018

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
Copy link

@jeffchulg jeffchulg commented Jul 12, 2018

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
Copy link

@OliverUweHahn OliverUweHahn commented Jul 12, 2018

@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
Copy link

@jeffchulg jeffchulg commented Jul 12, 2018

@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
Copy link

@TiborKaraszi TiborKaraszi commented Jul 12, 2018

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
Copy link

@OliverUweHahn OliverUweHahn commented Jul 12, 2018

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
Copy link

@bcdfeath bcdfeath commented Jul 12, 2018

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
Copy link

@bcdfeath bcdfeath commented Jul 12, 2018

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

@olahallengren
Copy link
Owner Author

@olahallengren olahallengren commented Jul 12, 2018

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
Copy link
Owner Author

@olahallengren olahallengren commented Jul 12, 2018

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
Copy link

@bcdfeath bcdfeath commented Jul 12, 2018

@bcdfeath
Copy link

@bcdfeath bcdfeath commented Jul 12, 2018

@drstonephd
Copy link

@drstonephd drstonephd commented Jul 12, 2018

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
Copy link
Owner Author

@olahallengren olahallengren commented Jul 13, 2018

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
Copy link

@ConstantineK ConstantineK commented Jul 13, 2018

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
Copy link

@m60freeman m60freeman commented Jul 14, 2018

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.

@gnotisauton
Copy link

@gnotisauton gnotisauton commented Jul 27, 2018

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
Copy link

@OliverUweHahn OliverUweHahn commented Jul 27, 2018

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
Copy link

@gnotisauton gnotisauton commented Jul 27, 2018

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
Copy link

@OliverUweHahn OliverUweHahn commented Jul 27, 2018

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
Copy link

@jseymour84 jseymour84 commented Aug 10, 2018

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
Copy link

@SQLNerd SQLNerd commented Aug 24, 2018

ohms - Ola Hallengren Maintenance Solution

@drstonephd
Copy link

@drstonephd drstonephd commented Aug 24, 2018

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

@crankydba
Copy link

@crankydba crankydba commented Sep 5, 2018

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
Copy link

@drstonephd drstonephd commented Sep 5, 2018

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
Copy link
Owner Author

@olahallengren olahallengren commented Sep 14, 2018

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
Copy link

@christianspecht christianspecht commented Oct 4, 2018

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
Copy link

@mkhawaja72 mkhawaja72 commented Oct 17, 2018

I think it would be fine having its own schema.

@jeffchulg
Copy link

@jeffchulg jeffchulg commented Dec 21, 2018

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

@olahallengren olahallengren pinned this issue Dec 30, 2018
@olahallengren olahallengren unpinned this issue Dec 30, 2018
@JulioIzquierdo
Copy link

@JulioIzquierdo JulioIzquierdo commented Jan 17, 2019

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?

@MichelZ
Copy link

@MichelZ MichelZ commented Jan 17, 2019

It can (and should!) be installed in its own database and not in every database in an "on premises" world, but this is not possible in Azure AFAIK.

KuehneThomas added a commit to KuehneThomas/sql-server-maintenance-solution that referenced this issue Jan 29, 2019
@KuehneThomas KuehneThomas linked a pull request that will close this issue Jan 29, 2019
@JohnKNess
Copy link

@JohnKNess JohnKNess commented Sep 9, 2019

@SQLNerd

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

...which is also short for "On His/Her Majesty's Service".

@JohnKNess
Copy link

@JohnKNess JohnKNess commented Sep 9, 2019

I never installed Ola's SQL Server Maintenance Solution ([OSSMS] or [ossms] for schema name anybody?) in the master database as per the initial design, but always preferred to store the solution in the msdb database.

Seeing as the solution is developing in leaps and bounds and because naming conflicts are sure to happen, I would recommend storing the solution in its own database.

This leaves me with the followings two suggestions:

  1. Schema Name : OSSMS
  2. Database Name: OSSMS

As always a big THANKS to Ola for creating and developing this great solution.

@HugoShebbeare
Copy link

@HugoShebbeare HugoShebbeare commented Oct 4, 2019

DBA schema is what I have used frequently, my take, thanks again OLA, you rock!

@Xylandor
Copy link

@Xylandor Xylandor commented Oct 21, 2019

So. Lot of good comments and suggestions here. I also believe having its own database would be beneficial, but providing my opinion is not my main focus on this comment.

What I'd like to know is, when is this actually going to be put into play? What's the status on its implementation?

@bensala
Copy link

@bensala bensala commented Oct 30, 2019

+1 - I highly recommend you put it in it's own schema! I already do that anyway and would save me the extra step of going through the new code and replacing dbo. As far as the requests for a different database, I don't see how that is relevant to the schema discussion. It can be in the ola schema in whatever DBA/DBAdmin/WhateverYouCallYourDBA. I've personally never put it in the master database anyway myself.

@amcguire72
Copy link

@amcguire72 amcguire72 commented Oct 31, 2019

If you are on this thread, you are probably using the Ola maintenance solution and hopefully have it configured per your organizations RPOs and RTOs. How you decide to organize those objects is really up to you and perhaps your clients/employer - I mean, four procs, a table and a series of jobs. Pretty simple. I just make sure I use the solution in such a way that doesn't alter the base objects themselves. That's out of respect for the DBA that will one day inherit the solution.

@JimPierce42
Copy link

@JimPierce42 JimPierce42 commented Nov 20, 2019

I usually put the scripts in their own database along with Brent Ozar's stuff and any other publicly available scripts called DBATools, DBAAdmin, or something along those lines. I think I got the DBATools name from some of the stuff that Brent Ozar was working toward. There are many reasons for this, but one of the biggest is that in a large organization with security concerns and change control issues to contend with, this typically makes it easier to make changes and keep things up to date. So putting them in their own schema in their own database is a very good idea. It would also be helpful to extend the functionality some so that a Central Management Server could be used instead of needing to implement all of this stuff on every server. I know that would be a large amount of work, but the payoff could be pretty large. In an organization where you are managing a group of 100+ servers, being able to use the CMS as more than just a list of servers for SSMS could have a whole lot of benefits and give organizations more of an incentive to use the CMS.

@amcguire72
Copy link

@amcguire72 amcguire72 commented Nov 20, 2019

Well, if you are using a CMS - kudos for starters! It would be interesting to execute it against a CMS and its registered instances, but also could be dangerous and have unintended consequences. I personally am a huge fan of using a CMS but I use it not only as a central repository, but a way to execute scripts against a subset of instances based on how I have them organized in the CMS: subfolders for environment, location, SQL Server version, etc. I think right-clicking on a node in the CMS and running a multi-instance query to update your scripts is easy enough. That way the burden is on you to know exactly what you (or your co-worker DBA) are doing.

As an aside, I go so far as to standardize those jobs that are consistent across the board: CommandLog Cleanup, System Database backups, Output File Cleanup, history cleanup, etc. For those I use the MSX/TSX functionality to deploy those standard jobs that mostly run at the same time across the board.

@JimPierce42
Copy link

@JimPierce42 JimPierce42 commented Nov 20, 2019

I certainly agree that there is a certain level of risk involved in deploying things to the CMS, but that could be said of just about anything. Using the CMS as a central repository for management data could certainly be worth the risk. Being able to go to one place and find job schedules, failures, database information such as files and sizes.

One example that comes to mind is where I use the parallel backup features. It is very easy to saturate resources such as network and disk throughput if I'm not really careful about when those jobs run. Being able to use the CMS as a central place to manage job schedules would be really helpful.
Organizations also try to implement global log monitoring solutions such as LogRythm, when we could easily have that functionality or better by utilizing the CMS. Products like Idera SQLdm try to do some of the needed management tasks like keeping track of database growth, but it seems that there should be a better solution than spending huge $$ for something that could be done in a industry standard way like what has been accomplished with the SQL Server Maintenance Solution.

I guess what I'm suggesting is taking things to another level and filling an obvious hole in our existing toolset.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.