Dropping All Objects in a SQL Server Database

Posted by on January 26, 2012 No comments

Every once and a while I get a request from developers to drop all the objects in a database. Normally I drop the whole database and recreate it, but then you have to re-setup all the database users. This is pretty quick and I have done this in the past. But this time I wanted to write a script to manually drop all the objects without dropping the whole database so I can share the script with the developers themselves and then they can run it whenever they want.

declare @ord int, @cmd varchar(8000)

declare objs cursor for
select 0, 'drop trigger [' + name + '] on database' from sys.triggers
where parent_class = 0 and is_ms_shipped = 0
union
select 1, 'drop synonym [' + schema_name(schema_id) + '].[' + name + ']' from sys.objects o
where o.type = 'SN'
union
select 2, 'drop procedure [' + schema_name(schema_id) + '].[' + name + ']' from sys.objects o
where o.type = 'P'
union
select 3, 'drop view [' + schema_name(schema_id) + '].[' + name + ']' from sys.objects o
where o.type = 'V'
union
select 4, 'drop function [' + schema_name(schema_id) + '].[' + name + ']' from sys.objects o
where o.type = 'FN'
union
select 5, 'alter table [' + schema_name(schema_id) + '].[' + object_name(parent_object_id) + '] drop constraint [' + name + ']'
from sys.objects
where type = 'F'
union
select 6, 'drop table [' + schema_name(schema_id) + '].[' + name + ']' from sys.objects o
where o.type = 'U'
union
select 7, 'drop type [' + schema_name(schema_id) + '].[' + name + ']' from sys.types
where is_user_defined = 1
order by 1

open objs
fetch next from objs into @ord, @cmd

while @@FETCH_STATUS = 0
begin
  print @cmd
  execute (@cmd)
  fetch next from objs into @ord, @cmd
end

close objs
deallocate objs

Database triggers are dropped first because one could exist that would prevent the dropping of any object. Also, foreign keys are dropped individually before tables otherwise you would have to drop tables in a specified order, in this case child tables first. Without the foreign keys we don’t have to worry about that.

Log Shipping for Many Large Databases

Posted by on January 6, 2012 No comments

This post isn’t a walk though on how to configure log shipping. Those can be found other places. This more of a story, culminating in recommendations for a log shipping configuring for servers will many large databases. The environment I was working with happens to be used by SharePoint 2010 but these recommendations apply to any log shipping configuration of any size.

We have a fairly large SharePoint 2010 environment. Spread across 2 separate farms, there is about 3 TB of used data consisting of about 56 separate content databases, plus all the search and supporting databases. Each farm is deployed on highly available infrastructure and we have “read only” fall back farms to provide access to content during outages. To keep the databases on these read only farms in sync we used to do a simple backup/restore job, but we are now using log shipping.

As you can imagine, with such a large amount of data and a large amount of databases, a full backup/restore took some time. About 6 hours for both farms, which I thought was impressive for 3 TB of data. Over the past year the size of our SharePoint environment more than doubled. I went to some extensive lengths to keep the restore time this time at a minimum, like adding multiple NIC cards and sending data to each network interface. However, it still wasn’t ideal. Originally, we discussed log shipping, but since the farms were not as large back then we decided to move quickly with a backup/restore method and follow up later after our SharePoint 2007 to 2010 upgrade to implement log shipping.

We finished the upgrade around September 2011. So after that, I went ahead and spent the time setting up log shipping. I knew I would have some unique challenges because of the amount of databases that need to be log shipped and the size.

The SQL Server 2008 R2 Management Studio GUI for setting up log shipping is pretty good. However it is missing the logic to enable compression on standard edition servers. As you may know, Microsoft allows backup compression on standard edition for 2008 R2. However it seems they missed updating the log shipping wizard in Management Studio. To work around this issue you have to script out your configuration and manually add the parameter to enable compression. The stored procedure is sp_add_log_shipping_primary_database, and the parameter is, @backup_compression = 1.

From the beginning we decided that we wanted to kill all connections in the destination database each time the transaction log is shipped and restored. The GUI has a checkbox for this functionality and it corresponds to a parameter, @disconnect_users = 1, for the sp_add_log_shipping_secondary_database stored procedure. However, even with this I still ran into issues with the restore of the transaction log. Every once and a while a restore would fail. Looking though the error logs I could see the following error.

Error: Exclusive access could not be obtained because the database is in use.
RESTORE LOG is terminating abnormally.(.Net SqlClient Data Provider) ***

I am configuring the log shipping to disconnect users. How could this be happening? I did some tracing and came to the following conclusion. When the logship.exe executable runs on the server to do the restores, it executes “alter database set single_user with rollback immediate” to kill all user connections to the database. And immediatly after, before the logship.exe can execute the restore statement, a new connection from SharePoint comes in and consumes that single user connection. There isn’t anything I can do about this, so I looked for workarounds.

I decided to set 3 retry attempts for the lsrestore jobs. This way if it fails it will simply try to do the restore again up to three times. After letting this run for a while, it was fairly effective in reducing the failures. But it didn’t eliminate them. Every couple days there would still be a failure, which meant the restore failed 3 times in a row. Each time it was always the same error as before.

I then decided to investigate the timing of these jobs. Originally we setup the jobs to run every 15 minutes.  Now if you are not careful with the scheduling it can take up to 3 times your interval to actually have data show up on your secondary database. This happens if you schedule each set of jobs to start at the same time. So for example, if you go with the default all your jobs will start at 12:00:00 AM. The job to copy the backup file will run, and it will see no backups available to copy because the backup job also started at the same time and is still writing the backup file to disk. Same for the restore job, it will look for a file to restore and nothing will be there because the copy job also started at the same time and is still copying the file over the network. With a 15 minute interval, a 45 minute delay was acceptable for us. But I wanted to run the jobs less frequently to avoid this issue I was having. I talked to our SharePoint administrator and he was fine with running the jobs every hour. But since they were all starting at the same time, that would mean it would take 3 hours for data to show up in the secondary. The simple solution here is to delay the start of each job so that the prerequisite job can finish. So it setup all the lsbackup jobs to start at 12:00:00, then all the lscopy jobs to start at 12:10:00, then all the lsrestore jobs to start at 12:20:00. This way every hour on the hour, data would take 20 minutes or so to show up in the secondary.

This helped a little bit, but didn’t solve the problem with the restore jobs not getting exclusive access. I then decided to stagger the start time of each individual database. The first farm had 20 content databases, so I staggered the start time of each set of jobs, lsbackup, lscopy, and lsrestore, for each database by 30 seconds. Bingo. After I did this I didn’t see any more restore failures.

I think this was successful because starting the jobs for 20 databases all at the same time put too much load on the server and caused too much delay between setting it to single_user and running the restore statement. I suspected this would be an issue from the start. Most servers out there would probably experience a noticeable impact if 20 databases suddenly did a transaction log backup simultaneously. There would be noticeable network utilization spike if 20 backup files were all copied at the same time, and there would be a noticeable spike if 20 databases all restored transaction log backups at the same time.

After going through all this my recommendations for log shipping a lot of large databases are:

  • Stagger the start time for each set of log shipping jobs (lsbackup, lscopy, lsrestore) for each database by a decent amount of time to give each time time to complete before the next one starts.
  • Stagger the start time for each set of lsbackup jobs, each set of lscopy jobs, and each set of lsrestore jobs by a few seconds to spread out the load on your server.
  • Set some retry attempts for the lsrestore jobs.

 

Oracle Command Prompts on Windows

Posted by on November 17, 2011 No comments

I work in an environment where we run Oracle databases on Windows. On many of our servers we run multiple Oracle databases each with their own instance. One thing we do on our Windows servers is setup a batch file that launches a command prompt with the proper environment variables set so that you can use sqlplus, lsnrctl, and rman without any issues for the intended instance.

The batch file that we setup dynamically creates a temporary batch file with the all the commands that need to be run, and then opens a new command prompt and runs that temporary batch file. Here is the template we use to create new ones.

@ECHO OFF
set temp=c:\temp

SETLOCAL
SET SID=<<INSERT SID HERE>>
SET ORACLE_HOME=<<INSERT ORACLE HOME HERE>>

SET ADMIN_HOME=%ORACLE_HOME%\database
D:

echo Title *** %SID% *** > %temp%\prompt.bat
echo color 07 >> %temp%\prompt.bat
echo set ORACLE_SID=%SID% >> %temp%\prompt.bat
echo set ORACLE_HOME=%ORACLE_HOME%>> %temp%\prompt.bat
echo set PATH=%ORACLE_HOME%\bin;%ORACLE_HOME%\OPatch;%PATH%>> %temp%\prompt.bat
echo Prompt=%SID% $t$h$h$h $_$p$g>> %temp%\prompt.bat
echo cd """%ADMIN_HOME%"""  >> %temp%\prompt.bat
echo cls >> %temp%\prompt.bat
ENDLOCAL
cmd.exe /k %temp%\prompt.bat

When we install a new Oracle database on a server, we simply take this template and modify the two spots where we enter the new SID, and the new Oracle home directory. Many of the commands deal with the graphical display of the prompt, so you can keep those in place if you want. The setting of ORACLE_SID, ORACLE_HOME, and PATH are the important lines. The ORACLE_SID and ORACLE_HOME environment variables are pretty straight forward. However the PATH variable has caused some confusion for us in the past.

When you install Oracle it will modify the system default value for the PATH environment variable with the bin folder of the Oracle home you just installed. This can cause problems if you install multiple homes, because each one’s path will end up in the system default in the reverse order of what you installed. Then, if you use the system default PATH, when you go to execute an Oracle utility, sqlplus for example, it will use the exe out of the last home you installed, regardless of what SID you are connecting to. While running the wrong sqlplus.exe usually doesn’t cause problems, it can cause problems when using things like oradim.exe and lsnrctl.exe, since both those provision new services with the path environment to get to oracle and tnslsnr executables. Because of this behavior, after we install any Oracle home, we manually remove any Oracle paths from the system default PATH environment variable. We then rely on our batch script to append the right path to the bin folder for each session.

We also manually add the path to the OPatch folder to the session’s PATH environment variable. This is so you can run lsinventory without having to set it manually. Oracle doesn’t add this one to the system default so it can be somewhat tricky. Before we did this, I always had to lookup how to get the current patch set level of an Oracle home, and it always took much longer than it should.

Once we have the script setup we name it the same as the SID it is used for and put it in a folder that we use to hold other administrative stuff. Another benefit of this is being able to open multiple command windows for different SIDs without any issues.

SharePoint 2010 Group By Content Type

Posted by on October 24, 2011 No comments

In SharePoint 2010 the “Content Type” is not in the list of available fields to group by. A quick search shows it is still possible, but it requires editing the page in the SharePoint 2010 Designer. Michal Pisarek in his post How to Group By Content Type in SharePoint 2010 explains is pretty well.

Basically once you have the page opened in the editor you modify the XML definition of the view you want to have the grouping on by adding the following elements. Or, if its already grouped you just need to change the Name attribute on the FieldRef element.

<Query>
  <GroupBy Collapse="TRUE" GroupLimit="1000">
    <FieldRef Name="ContentType"/>
  </GroupBy>
</Query>

Now go back to your site and refresh the page and you should see the grouping. One thing to be careful of, that I ran into, was the item limit. If your first 30 items are all of one type, and your limit is set to 30, you will only see one grouping header. I increased the item limit to 1000 on my list to it would display all the groups on the view.

Also security wise, you will most likely need some elevated permissions to do this. I was a site collection administrator for the site containing this list so I had no issues. I am not sure what the minimum permissions would be.