Saving Time with Differential Backups
Recently I've been migrating a lot of databases from stand-alone physical servers into a PolyServe environment. Since this obviously requires a downtime, we have to schedule the moves with limited amounts of time. To add to the issues at night, the NetApp filers are busy so copies are slower.
In order to speed up the process and reduce the application downtime, I decided to use differential backups - which I will admit to not using that often.
If my downtime starts at 11PM, I will take a Full database backup earlier in the evening with SSMS. I backed the database up to the remote server that was to become the new SQL Server (\\server\path\db.bak) in order to avoid having to copy the backup file later.
After the full backup was completed, I restored it to the new server and made sure all my logins were moved over with sp_help_revlogin. Make sure when you restore the full backup that you choose the RESTORE WITH NO RECOVERY option, or you will not be able to apply the differential backup. Also, verify that the directory structure you are restoring the files to is correct. It will try to restore to the directory structure used by the old server.
Once the downtime started, I made sure all user connections to the database were gone and kicked off my differential backup. My full backup was nearly 20GB, but my differential was less than 200MB, which I also backed up to the new database server.
Once the differential backup was completed, I restored this database (using the Restore Database functionality in SSMS) as well. This time select the option to Restore with Recovery if you do not have any transaction logs to restore. If you shut down database access and took a differential like I did, there will be no transaction logs to worry about. Once you Restore With Recovery, the database will be available for use immediately after the restore is complete.
At first I wasn't sure if I should Restore Log or Restore Database, but Restore Database seemed like the best option and it was the right one. For some reason I had to fix the paths to my databases again, because the backup was using the directory structure from the old server here as well. I hoped it would be smart enough to know what to do on its own, but wasn't.
I did not specify OVERWRITE. The restore initially was quick but then hung on 90% for a while. It finally finished up and my database was ready to go. My downtime was 15 minutes - the time it took to take the differential and restore it on the other server. For a savings of at least 30 to 40 minutes.
I'd rather put the time in up front and reduce downtime with an added bonus of not having to stay up as late waiting on it. I used the time I saved to write this article as a quick reference for anyone else who is looking for the same information.
FIX: Windows 7 Login Error “The trust relationship between this workstation and the primary domain failed.”
Recently, my workstation was suddenly unable to logon to my Windows 2008 domain. After entering my domain username and password at startup, I was presented with the error: "The trust relationship between this workstation and the primary domain failed."
Ahh, I've experienced something similar before and I knew I'd have to rejoin the domain. I hoped and prayed that my user profile wouldn't be recreated and fortunately, I found a solution that not only worked, but my profile stayed the exact same:
My Computer -> Properties -> Advanced System Settings -> Computer Name -> Network ID... -> This is part of a business network... -> My Company uses a network with a domain -> Next -> Enter your domain username and password -> "An Account for this computer ("COMPUTERNAME") has been found on the domain "DOMAIN." Would you like to use this? Yes -> Add the following domain user account (the one you usually logon with) -> Administrator (if that's how you roll) -> Finish -> Restart.
For those of you looking to resolve your trust issues, I hope this works for you as seamlessly as it did for me.
SCO UNIX: Permanently Change a Static IP Address in SCO
If you need to temporarily add an IP address in SCO, you can use ifconfig
ifconfig net0 inet 172.16.1.10 netmask 255.255.255.0
But really, that just creates an alias that will disappear once the server has been rebooted. In order to permanently change an IP address on a SCO OpenServer Release 5, I used SCO's menu driven tcp configuration tool, netconfig.
My version of SCO was so fonky and old, though, that I didn't even have the option to use DHCP. And I was told by my client that the color completely left my face when I saw the following screen once I exited netconfig:
The UNIX Operating System will now be rebuilt. This will take a few minutes. Please wait.
Root for this system build is /
The UNIX Kernel has been rebuilt.
That's right.. SCO's kernel has to be completely recompiled in order to make a change to TCP! Ridiculous. Thankfully, the kernel recompiled, and the system rebooted with the desired changes. Oh, and here's a nice resource for SCO on VMware that I found while researching for this blog post.
SQL Backup Process Hanging and Stuck in Kill/Rollback – HyperBac
One of the most important tasks a DBA should perform daily is verifying database backup jobs are successful. Using Database Mail to e-mail upon the failure of a job is something everyone should be doing. But in some cases, that is not enough. You can query the MSDB database in order to find out the last time a database was backed up. If you have a large amount of servers, consider using PowerShell's SMO capabilities to query a list of servers and compile the information into a database table. Then you can query that table for backup dates older than a day or two, depending on your backup schedule. That's the topic of another article.
Using a similar approach, I recently discovered a database that had missed being backed up for 2 days over the weekend. I had not received any email regarding a failure. Using SSMS, I pulled up the list of jobs in Object Explorer Details and noticed that the job was stuck executing since the previous Friday night. The database backup file was not actively being written to, and since backups are serialized commands, any subsequent attempt to backup the database while that process is running will be unsuccessful. Using SSMS, I stopped the executing job.
A quick query of the sys.sysprocesses table (select * from sys.sysprocesses) showed that the process was still running. I killed the process since it was being so difficult. From the sysprocesses table results, find the spid for the process. Execute the statement Kill spid where spid is the actual ID that is shown. If you prefer to use SSMS, launch Activity Monitor, find the process in the list and kill it from there.
Once I killed the process, it went into a rollback state, which is expected. I'm not 100% sure what a backup transaction is rolling back, but it sure will try. Take note of the "waittime" column in the sys.sysprocesses table. The value may increase initially, but at some point it should max out and start decreasing. It is possible to track the progress of the rollback by executing the following command:
kill spid with statusonly
The result stated that the rollback was 16% completed. I decided to wait and see how long it would take. My waittime value continued to increase. I went on with my business and checked back later. After many hours I returned to find that the rollback was still at 16% and the waittime was STILL increasing. I knew something was wrong. My first quick-fix idea was to restart the SQL Service. This actually would have fixed my problem, but being that the system is in production, I wanted to find a better way. Also, a restart would not help me figure out why the problem was occurring in the first place.
Eventually, I realized that this server was using HyperBac, which is a really cool compression tool that intercepts a native backup and zips it up. I decided to stop the HyperBac service to see if it would help. After attempting to stop the service, the service got stuck in the "Stopping" state. I checked the log file in the HyperBac (Program Files) folder and saw a line stating that it was attempting to close out the very backup that had been stuck for 3 days.
I gave the service a large amount of time to stop, but it never did. I eventually had to End Task a few times on the HyperBac process. Once the process ended, my SQL backup process disappeared.
I cannot definitively say if HyperBac caused the SQL backup process to hang, or if the SQL process caused HyperBac to hang. Of the hundreds of backups that run every night, I have run into this issue 3 times. All 3 times were on large databases (~70GB) using HyperBac to back up to a network drive. Perhaps a network blink during the backup process is to blame for initiating the issue. Whatever the reason, I opened a ticket with HyperBac and was told that version 4.2.x fixes this situation. I should also comment on how quickly HyperBac responded to my problem with an answer, so thumbs up on customer support. I plan on upgrading to 4.2.x soon, but server reboots are required. If the issue happens again before then, it is easy enough to fix.
SQL 2008 Installation Failure on Reporting Services
Recently, SQL Server 2008 was uninstalled from a Windows 2008 Server machine due to some configuration issues with Reporting Services. Attempts to re-install SQL Server with the Reporting Services option would fail. I was tasked with the re-install and as always, I welcome this type of work because there is something to learn nearly every time. Before the installation began, I went to "Programs and Features" and uninstalled anything referencing SQL Server. The Native Client installation was still listed, so I removed that. There was also a reference to SQL Server 2008 itself, but attempts to remove it failed instantly saying it was already uninstalled. Red flag number 1.
An installation summary was provided from a previous failed attempt and it provided an exception link with the following information:
Product: SQL Server
ID: 50000
Source: setup.rll
Version: 10.0
Component: SQL Server Native Client
Message: A network error occurred while attempting to read from the file '%.*ls'.
The page went on to explain that an attempt was made to install (or update) SQL Server Native Client on a computer where SQL Server Native Client is already installed, and where the existing installation was from an MSI file that was not named sqlncli.msi.
Having just removed the SQL Server Native Client, I was confident that my next installation attempt would be successful. To make a long story short, my installation failed again when trying to install Reporting Services with the same exact error. Ok, so we must be dealing with a registry key issue, so I decided to run regclean to clean up the system. I would caution to do this at your own risk. In my case, there was nearly nothing else installed on the server and if anything drastic happened, I would have been able to get a file system restore or having the system rebuilt without losing anything major. I reviewed the undo reg file that was created by regclean and found references to Reporting Services.
Attempt 2! I was confident yet again that it would work and to make another long story short - it failed. At the same point, with the same error! Ok, now it's time to go through the error log a little bit closer. And for those of you who don't care about all the steps and only the solution, you should have skipped down to this part.
After the installation failure, the setup directed me to a summary log file. In the log file, there was a path to another log containing the failure information:
C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\20100428_094920\sql_rs_Cpu64_1.log
I opened this file and located the following line:
MSI (s) (3C:80) [09:57:00:939]: Specified instance {2453DBC8-ACC4-4711-BD03-0C15353AA3D8} via transform :InstID01.mst;:InstName01.mst is already installed. MSINEWINSTANCE requires a new instance that is not installed.
Fire up RegEdit and search for the instance ID specified in the log. In my case it was {2453DBC8-ACC4-4711-BD03-0C15353AA3D8}.
Delete the registry keys that match up with that ID. You will notice that some of the values contained in that key folder reference Reporting Services and the initial installation path. In my case, I knew it was the right place to be because the previous install was done from a different location than where I was installing from.
After deleting the registry keys, I launched my Setup and added Reporting Services successfully. I was unable to choose the default configurations, however. My only option was to install without configuring. Once the installation was completed, I had to launch Reporting Services Configuration Manager and set up the new ReportServer databases. You have the option to create new databases or use existing ones.
This methodology would probably come in handy in any type of SQL Server 2008 installation failure where you suspect bad registry entries.
OS X: Show hidden files and WTF to do with an ipsw file.
I just downloaded the iPhone OS 4.0 beta and have no idea what to do with the resulting ipsw file. Apple was awesome enough not to include any obvious directions. So after searching Google, I came across some forums that recommended the following:
defaults write com.apple.finder AppleShowAllFiles TRUE
killall Finder
That didn't show the files that were supposed to appear at ~username/Library/iTunes/iPhone Software Updates/ and Finder looked dirty so I reran the above command using FALSE instead of TRUE.
Ultimately, I successfully installed the new iPhone OS by following the directions outlined at felixbruns.de: I pressed option-click on the Restore button in iTunes and selected iPhone1,2_4.0_8A230m_Restore.ipsw. Good times!
SQL Server: Error: 3041, Severity: 16, State: 1
There are probably a good number of reasons this error shows up in your SQL Server log, along with the following message:
Source: Backup
Message: BACKUP failed to complete the command BACKUP LOG DATABASE_NAME. Check the backup application log for detailed messages.
In my case, the recovery model was set to SIMPLE and the DB maintenance plan was set to backup all user databases, which included all databases set to SIMPLE. I have my suspicions as to why those production databases were set to SIMPLE but FULL is best in most cases anyway, so I set all recovery models to FULL, and manually backed up all databases. To see a list of all of your databases and their recovery models, run the following query:
select [name],databasepropertyex([name],'Recovery') as [Recovery]
from master.dbo.sysdatabases
order by [name]Note that the following system databases are set to SIMPLE by default: master, tempdb and msdb.
Search Results Blank in Windows SharePoint Services 3.0 (WSS 3.0)
Recently I experienced an issue where one of my WSS 3.0 sites stopped returning search results after having worked perfectly for a long period of time. I found that the Event Log had entries stating that the Search Gatherer service was failing.
Event Type: Warning
Event Source: Windows SharePoint Services 3 Search
Event Category: Gatherer
Event ID: 2436
The first thing I tried was just to stop and start the search service and give it some time to index again. After a day or two, I returned to see that the same issue was occurring. The next step was to focus on permissions issues, but I did not understand why something that always worked would suddenly stop, since I had not made any changes to permissions on the system. The likely issue was a recent security patch on the server.
In the SharePoint Application Management back-end, I changed the account for the Search Indexer to my own login, but even my Administrator account was unable to index the site, having received the same error messages. I decided to just access the site in the browser on my web server and received an error 401.1 loading up the site. I was unaware that the search indexer even worked in this method, but apparently it was failing because it was unable to load the FQDN (fully qualified domain name) of itself using Windows Authentication.
I then was able to find a Microsoft Support Article which stated the following:
You receive error 401.1 when you browse a Web site that uses Integrated Authentication and is hosted on IIS 5.1 or a later version
This issue occurs if you install Microsoft Windows XP Service Pack 2 (SP2) or Microsoft Windows Server 2003 Service Pack 1 (SP1). Windows XP SP2 and Windows Server 2003 SP1 include a loopback check security feature that is designed to help prevent reflection attacks on your computer. Therefore, authentication fails if the FQDN or the custom host header that you use does not match the local computer name.
There are two methods listed to fix this issue. I performed both methods by editing registry settings. The first method requires a reboot of the server. I did not want to reboot the server, but after making changes specified in method 2, the search indexer began to work, so I will assume method 2 is actually what fixed the issue.
Method 2: Specify host names
To specify the host names that are mapped to the loopback address and can connect to Web sites on your computer, follow these steps:
Set the DisableStrictNameChecking registry entry to 1.
Click Start, click Run, type regedit, and then click OK.
In Registry Editor, locate and then click the following registry key:
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa\MSV1_0
Right-click MSV1_0, point to New, and then click Multi-String Value.
Type BackConnectionHostNames, and then press ENTER.
Right-click BackConnectionHostNames, and then click Modify.
In the Value data box, type the host name or the host names for the sites that are on the local computer, and then click OK.
Quit Registry Editor, and then restart the IISAdmin service.
Accessing XML using the MSDAOSP Provider on Windows Server 2008
For quite some time now, I have been using a scheduled task that executes a VBS file on my web server in order to update local weather information in a database. After porting the site to a Windows 2008 server, the scheduled task began to fail. The script is a bit out of date since the implementation of PowerShell, but there is no reason to re-write this minor script when it usually works properly.
The VBS script does the following:
1. Access current weather conditions for a zip code using the Weather.com XML data feed.
2. Save the XML to a flat file in the local directory.
3. Using the MSDAOSP Provider, connect to the XML file and open it as a record set.
4. Transform the data appropriately and insert it into a table in SQL Server which holds the current forecast.
5. Repeat for each zip code I wish to load.
The first error I encountered was "Permission Denied" even though I was an Admin user. I quickly resolved this by deleting the XML file the script was trying to overwrite, because I had previously changed the user that the script executes as.
After that error, I began to receive another error with a code of 80040E21:
Line 65 is my MSDAOSP connection string, so I assumed it was a compatibility issue with the OS. Searching the Internet for a quick fix did not return desired results, since most matches for the connection string only demonstrated how to use it.
The solution turned out to be very simple. Earlier versions Microsoft Data Access Components have been deprecated , so the Connection string was in need of a version update.
In previous versions of Microsoft Windows Server, I was able to use the following connection string:
rs.ActiveConnection = "Provider=MSDAOSP; Data Source=MSXML2.DSOControl.2.6;"
In Windows Server 2008, the connection string must be updated to the following:
rs.ActiveConnection = "Provider=MSDAOSP; Data Source=MSXML2.DSOControl.3.0;"
So for those of you who would like to see how to open the file using ADO, it is as follows:
myXMLfile = "c:\scripts\weather.xml"
set rs = createobject("adodb.recordset")
rs.ActiveConnection = "Provider=MSDAOSP; Data Source=MSXML2.DSOControl.3.0;"
rs.Open myXMLfile 'Open a recordset from the newly created file
For more information on the MSDAOSP provider and how to use it to access XML in ADO, visit this Microsoft Support Article.
Setup WebDAV in Apache2 on SuSE Linux to Support Windows (XP/Vista/7) Clients
Earlier today, a colleague told me that she doesn't like using my servers because I don't have FTP setup thus, she couldn't map my server as a drive. Well, I showed her. I stand firmly against using FTP as any type of web-related solution and thus, decided on WebDAV to address my colleague's demanding needs.
Initially, I set the virtual host up for Basic Authentication but was unable to get Windows 7 and Windows XP to map the drive. Windows complained that "The network path could not be found." I tried mapping the drive from both the command line and from Windows Explorer with no luck. Then I read that theWindows webDAV client does not support Basic Authentication. If this server were on my domain, I'd use Kerberos without a second thought, but it's an Internet web server so that is out of the question. Digest Authentication it is.
I went enable mod_dav, mod_dav_fs, and mod_auth_digest in YaST under Network Services >> HTTP Server >> Server Modules and I restarted the service. I then created the folder /var/davlock, gave it the proper permissions and added the following to httpd.conf
<Ifmodule mod_dav.c>
DAVLockDB /var/davlock/DAVlock
</IfModule>
Then I added the new host, sample.acme.com to my vhosts.conf file and restarted the service.
<VirtualHost *:80>
ServerAdmin nobody@localhost
ServerName sample.acme.com
DocumentRoot /www/wordpress/sample
ErrorLog /var/log/apache2/sample-error_log
CustomLog /var/log/apache2/sample-access_log combined
UseCanonicalName Off
<Directory "/www/wordpress/sample">
Options Indexes FollowSymLinks
AllowOverride All
Order allow,deny
Allow from all
DAV On
#Auth in the house
AuthType Digest
AuthName "sample"
AuthDigestAlgorithm MD5
AuthDigestDomain http://sample.acme.com
AuthDigestNcCheck Off
AuthDigestNonceLifetime 0
AuthDigestQop auth
AuthDigestProvider file
AuthUserFile /etc/apache2/.htdigest
AuthGroupFile /dev/null
<LimitExcept GET HEAD OPTIONS POST>
require valid-user
</LimitExcept>
Order allow,deny
Allow from all
</Directory>
</VirtualHost>
Notice AuthUserFile /etc/apache2/.htdigest. That file was created using htdigest2 (or on most other systems, htdigest).
htdigest2 -c /etc/apache2/.htdigest sample acmeuser
The -c switch creates the file, "sample" correlates with the AuthName directive and acmeuser is the username of my demanding colleague. Also take note that the way I used LimitExcept allows all non-webDAV users to have anonymous access to the site, while any webDAV activity requires a username and password.
Next, I mapped a drive in Windows with the two methods I am familiar with. First, via the command line
W:\>net use * http://sample.acme.com
Enter the user name for 'sample.acme.com': acmeuser
Enter the password for sample.acme.com:
Drive X: is now connected to http://sample.acme.com.
And then via Windows Explorer (My Computer >> Map Network Drive [be sure to click "Connect Using Different Credentials"]). Both worked flawlessly. And of course, this is all better if you can do it over HTTPS, which I plan to setup when I have time. But for now, my servers are totally in style and ready for use by people who aren't fans of vi 
.
About
-
We are IT consultants
who grew up living and loving life
in Cajun Country.
Authors
Categories
What's Your Job Title?
Loading ...
information
wants to be free
