What do you know, it’s been 4 years since I’ve last written in here. Believe it or not, I had a great many plans and posts prepared! I just never quite got around to them… Much to my surprise, my little site still gets quite a few visits, seemingly from folks very interested in how I use dbatools to improve patching or how I Install SQL Server unattended! Thanks folks for all the visits over the years, hopefully you found the content useful!
But it shouldn’t surprise anyone that in the years since I’ve written these posts, I’ve continued to work on those tools, as well as written other tools (hello Poshbot — to be clear, I didn’t write Poshbot, but I sure wrote a lot of stuff that uses it). It’s time I reward all the people that have come looking for these posts with up-to-date stuff, so those should be coming up in the near future.
My life has become quite busier in the last few years — I was already dabbling in virtualization, but now I’m not only a DBA, I’m also an infrastructure manager! I picked up a few new responsibilities, including VMWare, BigIP, Storage, Patching, Enterprise backups, Disaster Recovery, etc. I probably have a few things to talk about on those topics.
I also decided to make more room on my site for other things besides work. Along with that comes a bit of a site redesign, but for some reason Edge (don’t @ me) is struggling with it. Don’t worry, I’m reliably told that no one besides me uses edge, so you probably didn’t even notice!
One of the most important duties of a production DBA is to ensure backups are happening regularly. No one wants to face a manager and tell him/her “I’m sorry, but the most recent backup I have is from a week ago”. Create a SQL backup report, so you can review your situation!
My production environment is rather large, so it would be easy to rely on backup failure alerts. Don’t fall into that trap!
You are trusting that both your backup system, your notification system AND your email system are all in working order, and that’s a lot of trust. I much prefer being proactive, which includes verifying manually (as often as i can afford) that my backups ran.
To address that need, I wrote the following script. Once a week, I run it against a Registered Server list (you have one of those, right?). After a few seconds, you will have a complete list with the last time each database was backed up, as well as backup type, and age of the backup.
SELECT DISTINCT DatabaseNames.NAME AS NAME,
Max(bs.backup_start_date) AS
[Latest Backup],
Datediff(dd, Max(bs.backup_start_date), Getdate()) AS
[Backup Age],
CASE
WHEN type = 'D' THEN 'Full'
WHEN type = 'I' THEN 'Differential'
WHEN type = 'L' THEN 'Log'
ELSE 'Some other type'
END AS
Backup_Type,
CASE
WHEN Max(bs.backup_start_date) IS NULL THEN 'NEEDS BACKUP'
WHEN Datediff(dd, Max(bs.backup_start_date), Getdate()) > 1
THEN
'NOT UP TO DATE'
WHEN Datediff(dd, Max(bs.backup_start_date), Getdate()) = 1
THEN
'DATABASE BACKED UP'
WHEN Datediff(dd, Max(bs.backup_start_date), Getdate()) = 0
THEN
'DATABASE BACKED UP'
ELSE 'PLEASE REVIEW MANUALLY'
END AS Status
FROM sys.databases AS DatabaseNames
LEFT OUTER JOIN msdb.dbo.backupset AS bs
ON bs.database_name = DatabaseNames.NAME
WHERE databasenames.NAME NOT IN ( 'tempdb', 'reportservertempdb' )
GROUP BY DatabaseNames.NAME,
type
HAVING Datediff(dd, Max(bs.backup_start_date), Getdate()) > 0
--more than 1 day since last backup
ORDER BY NAME,
backup_type
Look at all those backups!
From here, there’s a couple of directions you could go. You could save the output somewhere, if that’s part of your audit requirements! Or you could automate this report to yourself, as long as you have enough discipline to still review it… I find that emails are easy to ignore when you get > 100 emails/day, so if that’s you, I would keep on running it manually.
If your environment looks anything like mine, you’ll be spinning up new SQL instances in a pretty regular basis. The SQL install process has gotten much better over the years, but it’s still a slog to go through the same prompts over and over again. My favorite solution for this conundrum is to install SQL Server using a unattended script. It does take a bit of work to get the config file just right, but it will save you a lot of time in the long run.
The heart of the Unattended process is the Configuration file: During a regular SQL install, and as you select options, services, paths, etc., those options are saved to the configuration file. Once you’re ready to start, the installer reads through the file and sets everything up. The key, then, is to save the configuration file before you actually kick off the install. As is, the file is not yet usable, but once we’ve make a few changes to it, you’ll be able to use this same file over and over again.
1st step – Start a new SQL install
Here you’ll just start a server install, like you’ve might have done many, many times already:
Select all the options you’d normally do. For my servers, I always install the engine itself, Integration services and replication, among a few others. You can always change the config file afterwards to add or remove features, or even have multiple files.
Note that in the service settings, you’ll be prompted to enter the service account and password. The password is not actually saved in the config file, but you can’t progress until you enter one, so go ahead and fill it in.
In the “Ready to Install” window, you’ll get a chance to review all your options before you click on the “Install” button, effectively starting the install. DO NOT click Install! Instead, note the location of the configuration file (which I highlighted in the screenshot above), and open that file, then save it somewhere safe! I support multiple SQL versions, so I keep each file in their respective ISO folder.
2nd Step – Edit the config file
And here’s the file! For a smooth and easy server install, you’ll need to modify the items below:
IACCEPTSQLSERVERLICENSETERMS – Set it to True. This is the SQL server terms of licensing, and you have to accept it if you want to use it!
UIMODE – On SQL 2016 and older, I’ve always just set this to Normal, and QUIETSIMPLE to True. SQL2017 has changed things a bit, so now I’m setting QUIETSIMPLE to FALSE, passing QUIET via the command line (with /Q), and commenting out UIMODE altogether.
UPDATEENABLED – This is really handy if you want to install and patch your install at the same time. At work, I have our installs folders organize like the image below. You can’t see it in this screenshot, but in each version folder I keep both the ISO and the decompressed image. You can’t use unattended with the ISO file alone, but I keep the ISO around just in case.
In the Updates folder, I keep the last few CU and SP files for their respective SQL versions. When UpdateEnabled is configured, and a proper path is set (you’ll see that below) SQL will scan that directory and install the latest versions of whatever files you have in there.
FEATURES – This is what specific services and features you’d like installed. You can always change this later.
UPDATESOURCE – This is location where the installer will look for the patch files, as mentioned above. You’ll probably want to use a network share for this.
AGTSVCACCOUNT, ISSVCACCOUNT, SQLSVCACCOUNT – This is where you’ll specify the service account for SQL Agent, SSIS and SQL Server. You can use the same account for all 3, or a different account for each, or however your organization prefers it.
SECURITYMODE – This enables SQL auth, along with Windows Auth. If you set this to SQL, you’ll also need to specify an SA password in the command line. Generally you don’t want to do that, as each of your SQL servers should have a different SA password. For my lab, I’m just going to set this to SQL. At work, we leave this on windows auth, then after the machine is up and running, I go back in and generate a unique SA password, as part of our configuration checklist (you do have one of those, right?)
A lot of the other details, such as install location, were taken from when you went through the wizard, so they don’t need to be reentered here. Once you’re satisfied with your changes, go ahead and save the file. We’re ready to test it!
Part 3 – SQL Install – for reals now
Launch the command prompt as Administrator, navigate into your installation media directory, then type
setup /?
So helpful! For SQL 2017, you’ll be greeted with all the parameters that can be passed into setup, including a full example of an unattended install command. We just have to fill in the blanks, more or less. Do note that you can run this from a network share, just pass the full FQDN path.
Another thing to note is that we only enter the password in the command line. I’m using the same password for all the services, but again, you should use different accounts, and your service account password should be different from your SA password. Think security!
Once you enter all that in, go ahead and hit enter. After a few mins, it should be all done! You’ll get an error message if there are any issues, otherwise you’ll just be returned to the prompt.
And that’s it! You’re now the owner of a brand new SQL instance. I have the install string saved without the passwords, so next time I need to install a new server, I copy the string, enter in the passwords, then hit enter and go about my day. 10 mins later, it’s up and ready for final touch ups (like MaxDop, Memory settings, etc). Happy installing!
SQL Server Security should be in the top of your priority list. In the perfect world, once a user has left the company, you’d receive a notification to remove the login from your SQL server. Unfortunately, that doesn’t happen often enough. Too many stale users can increase your security footprint and make SQL server security harder to manage. Also ideally, you’d be manage your security via groups, instead of users. That way, once an AD account is disabled/dropped, then the group is automatically updated.
I’m big on being proactive and keeping my servers clean and secure, so I wrote the following script. This script builds a list of AD logins, check their status, and optionally drops them. If ActiveDirectory returns any kind of errors, then it drops the login. If you feel like this is a bit too drastic, then you can comment out line 25! Then all the script does is print out the list of candidates. Once you have that list, you can verify the candidates before you actually pull the trigger on them.
USE master
DECLARE @users TABLE ( pk INT IDENTITY(1,1),
username sysname)
INSERT INTO @users ([username])
SELECT name FROM sys.server_principals
WHERE type_desc = 'windows_login'
DECLARE @username sysname
DECLARE @i INT = 1
WHILE @i <= (SELECT MAX(pk) FROM @users)
begin
SELECT @username = username
FROM @users WHERE pk = @i
BEGIN try
EXEC xp_logininfo @username
END TRY
BEGIN CATCH
IF ERROR_NUMBER() >0
PRINT @username
EXEC ('DROP login ['+@username+']')
PRINT ERROR_NUMBER()
PRINT ERROR_MESSAGE()
END CATCH
SELECT @i = @i + 1
end
Another thing to keep in mind is that this script does not remove the users from the databases, so the permissions in the database itself are preserved. This presents a bit of a fork on the road, and the direction you go depends on your security policies (or paranoia)
If your requirement is to just totally clean up old users, then you should loop through each database and remove the respective users from there as well.
If there’s a chance the login should not be removed (maybe an old service account?), then preserving the user within the database would preserve the permissions. That way, if you have to add the user back, most of the database-level permissions are still there.
We’ve all been there, at some point: You set up a new QA server, but you’re a busy guy, and put off setting up alerts for later. The server gets a ton of usage, and all of a sudden, it runs out of space before you had a chance do something about it. This post is about one we way I use to recover from disk full errors.
In this particular case, we created a new SSIS catalog. The SSISDB database is created by the system, so at first you don’t get to select where the files are located. Sure, you could’ve modified the database defaults post-setup, but you didn’t do that either! Now the log file is in the data volume, and the volume is all filled up. You’d like to move the log file, but you can’t detach SSISB because, again, the volume is full and nothing works right. So what do you do?
Whenever SQL server restarts, it reads the entries from sys.master_files and sys.databases to figure out where the databases are. When you alter any of the database properties, those changes are registered in that table. So what we need to do here is update those entries (not directly, please!) and then restart the service. Since this particular server is non-prod, restarts are ok! So here’s the syntax:
--run this first to get the current logical name, you'll need this for the next step
SELECT DB_NAME(database_id),
name,
physical_name
FROM sys.master_files;
--Now the actual trick, where filename is the physical name of the file
ALTER DATABASE SSISDB
MODIFY FILE
(
NAME = 'log',
FILENAME = 'L:\sqllogs\ssisdb.ldf'
);
After this, stop SQL and manually move the file to the new location (as defined in your script — SQL will not move the files for you). When done, start SQL again. Your database should come right up!
Now, let’s say that, in your hurry to get things back up, you restarted the service but forgot to actually move the files. Despair not! As long as SQL hasn’t acquired a filesystem lock on the files, the following commands will allow you to move the files to the proper places. Once everything is in the proper places, the following commands will initialize the database:
ALTER DATABASE SSISDB SET OFFLINE;
ALTER DATABASE SSISDB SET ONLINE;
Index maintenance is one of those things that you have to stay on top of, in order to keep your data flowing smoothly. In no particular order:
Index defrags/rebuilds
Removing unused indexes
Optimizing existing indexes
Saving index definitions (ever had someone complain an index “went missing?”)
Move them to a separate filegroup/disk
Etc…
A few months back, we were having issues with heavy I/Os, so one of our plans was to move all the indexes into a separate filegroup, located in a different disk. This way, we’d end up with separate threads: One for the update itself, and one for the index update. The snag was that, overtime, new indexes were added to the original data filegroup, so now they were all over the place.
In an attempt to figure out the lay of the land, I wrote the following query. This script will tell you the filegroup an index lives in, which will help me identify what needs to be moved where. It does not include heaps (which are not indexes) and system indexes (because you should probably leave those alone).
SELECT ds.name AS FileGroupName, s.name AS schemaname, o.name AS TableName, i.name AS indexname FROM sys.objects o INNER JOIN sys.indexes i ON o.object_id = i.object_id INNER JOIN sys.data_spaces ds ON ds.data_space_id = i.data_space_id INNER JOIN sys.schemas s ON o.schema_id = s.schema_id WHERE o.is_ms_shipped = 0 AND i.index_id > 1 ORDER BY ds.name, o.name, i.index_id;
Once I had them identified, it was easy to write some dynamic sql to move rebuild them into a new location. The syntax is easy:
CREATE INDEX [indexname]
ON table
WITH (ONLINE = ON, DROP_EXISTING = ON)
ON [filegroupname];
You have to write out the entire index definition as it currently stands, but the Drop_existing = ON will instruct SQL to recreate the index in the specified filegroup, instead of just complaining that the index already exists. Online = ON (if you’re on Enterprise edition) will allow you to run this without dropping the index first, or additional blocking.
Don’t you wish to know how far along that backup/restore has come? If you’re running it manually, and you remembered to add the STATS parameter, you can see it in the default output, but what if it’s coming from an enterprise tool, like Commvault?
It would also be great to get the progress on other operations, like shrinks, DBCC CheckDB, etc. While not every operation will report its progress, this little script will report progress on whatever operations do provide it.
SELECT r.session_id
,DB_NAME(r.database_id)
,r.status
,r.command
,r.wait_type
,r.percent_complete
,CASE WHEN estimated_completion_time < 36000000 THEN '0'
ELSE ''
END
+ RTRIM(r.estimated_completion_time / 1000 / 3600) + ':'
+ RIGHT('0' + RTRIM(( r.estimated_completion_time / 1000 ) % 3600 / 60),2)
+ ':' + RIGHT('0' + RTRIM(( r.estimated_completion_time / 1000 ) % 60), 2)
AS TimeRemaining
,GETDATE() + ( CASE WHEN estimated_completion_time < 36000000 THEN '0'
ELSE ''
END
+ RTRIM(r.estimated_completion_time / 1000 / 3600)
+ ':' + RIGHT('0' + RTRIM(( r.estimated_completion_time / 1000 ) % 3600 / 60), 2)
+ ':' + RIGHT('0' + RTRIM(( r.estimated_completion_time / 1000 ) % 60), 2) )
AS EstimatedCompletionTime
FROM sys.dm_exec_requests r
WHERE r.percent_complete > 0
It’s late at night, I’m watching Yours, Mine and Ours (the 1968 version), and it’s time to upgrade one of our SQL2014 dev instances to SQL2017. We’d already gone through the upgrade advisor, the issues raised (mostly CLR) were not a big deal, so let’s go ahead and get this done. It’s all smooth sailing until I get this error:
Very odd! After a quick googling, I find this page by Pinal Dave –> https://blog.sqlauthority.com/2017/01/27/sql-server-sql-installation-fails-error-code-0x851a001a-wait-database-engine-recovery-handle-failed/
His suggestion is that there’s an issue with the AD service account, and the fix is to switch to network service. This is dev anyway, and I feel a little careless, so why not? Unfortunately, though, it didn’t work! Not only that, when I tried switching service accounts, I got even weirder errors:
I absolutely love the %1 to logoff.
After a restart and another attempt at the upgrade, and getting the same results, I decided to ditch the upgrade summary and take a peek at the sql server log itself. This is the actual service log, saved under MSQL12.MSSQLSERVER\MSSQL\Log. In there, I’m able to verify a few things:
The server starts
It tries to run the upgrade scripts
So that’s good, right? Then I find the real culprit (database names hidden to protect the innocent):
Based on this message, what the issue appears to be is that the upgrade script is trying to upgrade some system objects (you can tell they’re system objects because the begin with MS%), and when that fails, it fails the entire upgrade. Interesting! I know that this particular object is left over from some weird replication issues that have not been properly cleaned up, so it’s safe to drop them. But how? I can’t even get the service to come up, but I know I have a few options:
I can look up the trace flags that skip upgrade scripts (This very handy link lists all the trace flags –> http://www.sqlservercentral.com/articles/trace-flag/152989/ ) The correct flag is 992 902 — this flag will skip upgrade scripts in the master database. Once the server comes up, I can fix the issue, remove the flag, then retry the upgrade
I can be lazy and rename the data file and log files in the filesystem, then restart the upgrade. In theory, SQL should fail to attach the database (because I renamed the files!), then apply the upgrade scripts on any of the databases that actually mounted, and then complete the upgrade. I’m feeling adventurous, so I try that, and it worked! I have a working instance again.So now I have to clean after my mess. I switched the service account back to the proper AD account, and I also stop SQL and rename the data files to the correct extensions. Once I start SQL again, the database mounts properly… I think it’s odd that it didn’t fail the upgrade scripts, so I can only assume that the upgrade scripts did not run again. I’m not super worried, at this point, because next up is fixing the issue by dropping the orphaned replication objects. Since I have to apply CU11 afterwards, I figure that at that point the upgrade scripts will be applied. 10 mins later, I was done.
What are the lessons learned? First of all, before you run an upgrade, make sure your databases are clean, with no weird system objects laying around. Second, if you have a failed upgrade, take a look at the SQL server log itself, there’s usually a ton of useful information in there to help you get up and running again.
So now I have to clean after my mess. I switched the service account back to the proper AD account, and I also stop SQL and rename the data files to the correct extensions. Once I start SQL again, the database mounts properly… I think it’s odd that it didn’t fail the upgrade scripts, so I can only assume that the upgrade scripts did not run again. I’m not super worried, at this point, because next up is fixing the issue by dropping the orphaned replication objects. Since I have to apply CU11 afterwards, I figure that at that point the upgrade scripts will be applied. 10 mins later, I was done.