SQL Server Soldier :: News from the frontlines of the database wars : How do I .... ?

Database Mirroring FAQ: Why is my mirror database in a restoring state?

Robert Davis — Sat, 07 Mar 2009 06:47:00 GMT

Question: Why is my mirror database in a restoring state? Hi, I am still working on DB mirroring with prinicpal and mirror. There will be no Witness. This is what I did and my current issue: 1. I have two SQL servers with a database called SCMAX on them. This is as simple as File ⇨ New database. There is no application involved here. 2. I did set up mirroring with Balmukund’s help. He was great. There is no witness. It is asynchronous mode. Issue: Now all I want to do is – I created a table called Contacts in the principal and hoped it would show up in the mirror as well. I created some data in it too. But mirrored Database doesn’t do anything. I can’t even view the tables in it. It is as if it froze. All it says is Restoring. So I did paused the mirror. That didn’t help. I deleted the mirroring session. Even then the Mirrored database says restoring. How can I get rid of that? I want to view if my contacts moved over to mirrored database. ...(read more)

Database Mirroring FAQ: How do we handle transaction log maintenance for a mirrored database?

Robert Davis — Thu, 05 Mar 2009 04:43:00 GMT

Question: How do we handle transaction log maintenance for a mirrored database? This question was asked on a technical discussion group. My reply follows. Hi, My customer would like to know how to handle the txn log maintenance with DB Mirroring? They normally truncate the txn log after a full backup without DB Mirroring. But with DB mirroring, can they truncate the txn log? What happen at the mirror DB if the txn log is truncated? Do they need a different maintenance plan at the mirror DB?...(read more)

Database Mirroring FAQ: What would be the best practice to configure Failover Clustering and Database Mirroring?

Robert Davis — Tue, 24 Feb 2009 06:03:00 GMT

Question: What would be the best practice to configure Failover Clustering and Database Mirroring? This question is frequently asked in one of the technical discussion groups I frequent. My reply follows. I have a customer that wants to combine Failover Clustering with Database Mirroring in which the last one will be for disaster recovery. In order that the mirroring will act as primary only if the cluster fails, does the mirroring should be configured as manual? How do we configure mirroring in automatic when both nodes in the cluster fail? ...(read more)

How do I ... Determine Database Growth If I Am Not Tracking It? -- Error Logs Part III

Robert Davis — Fri, 20 Feb 2009 16:59:00 GMT

How do I ... Determine Database Growth If I Am Not Tracking It? If your database has grown considerably and you're not sure when or why it happened, you'll be hard pressed to figure out the answer unless you're tracking changes in database size. Luckily, if you are performing regular full backups, you can get this information from the SQL logs. ...(read more)

Database Mirroring FAQ: Why does Database Mirroring Monitor report that my mirror is disconnected?

Robert Davis — Thu, 19 Feb 2009 00:18:00 GMT

Why does Database Mirroring Monitor report that my mirror is disconnected? This question was sent to a discussion list via email. My reply follows. Sometimes, I have seen a behavior where the Principal was unable to connect to the Mirror. What is the reason for this behavior? Could it be network? I could not find any information that explain this behavior. Any help here would be appreciated. ...(read more)

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Robert Davis — Tue, 17 Feb 2009 16:41:00 GMT

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future. I have not tried this, need mirroring setup and was hoping to get a jump on the future upgrade. ...(read more)

Database Mirroring FAQ: If the principal fails while running in high performance mode, what options do I have for bringing the mirror online?

Robert Davis — Tue, 17 Feb 2009 03:44:00 GMT

Question: If the principal fails while running in high performance mode, what options do I have for bringing the mirror online? This question was sent to me via email. My reply follows. Quick question on DB mirroring client redirection after Principal fails ... Consider a simple scenario with Asynchronous mirroring: 1. We are implementing asynchronous [high performance] mirroring without witness. 2. The client application connects to the mirroring session using below connection string: Data Source={PrincipalServerName};Initial Catalog={DBName};Integrated Security=SSPI;Failover Partner= {MirrorServerName} 3. Now due to some disaster the Principal Server went offline and will be offline for let’s say next 48 hours. 4. We notice that client connections fail since primary is offline and a failover has not yet be done. 5. What steps should be taken now to connect the client application to the mirrored database? ...(read more)

How Do I Configure SSIS to Work With a Named Instance

Robert Davis — Fri, 12 Dec 2008 03:56:00 GMT

By default, SSIS (SQL Server Integration Services) uses the msdb database of the default instance on the server for storing packages. SSIS can still manage packages stored as files in the file system without any changes to the current setup. To be able to use the msdb database of a named instance, you have to manually edit the SSIS configuration file. Here are the steps for that .......(read more)

Deleting Duplicates without a unique key

Robert Davis — Thu, 21 Aug 2008 17:58:00 GMT

The following was sent to me by my friend and colleague Dave Miller: Wanted to pass along something I hadn't used before and found useful to easily get rid of duplicates in a set of data. The functionality has existed in the SQL language and was supported in SQL Server 2005. This uses Common Table Expressions (CTE) and the ROW_NUMBER() function ...(read more)

What every DBA that manages a large number of SQL Servers should know about SQL 2008!!

Robert Davis — Tue, 19 Aug 2008 16:50:00 GMT

I discovered the already known bug in SQL 2008 that you can't import a SQL 2005 server list (exported as a regsrvr file). For someone like me who deals with a large numger of SQL Servers, this is a major issue. Fortunately, Buck Woody has provided a simple solution on his Blog: Importing SQL Server 2005 Registered Servers to SQL Server 2008. This saved me a great deal of time and, more importantly, a great deal of annoyance!!...(read more)

How do I automatically respond to mirrored database failovers?

Robert Davis — Mon, 18 Aug 2008 17:54:00 GMT

Automatic failover with Database Mirroring is great, but what about all of the other things that you need to happen when the database fails over? There is no built-in support in Database Mirroring for anything external to the database. This is where automatic activation in the Service Broker comes to the rescue....(read more)

How do I .... grant access permissions for SSIS to users?

Robert Davis — Wed, 13 Aug 2008 21:43:00 GMT

SSIS access permissions are managed via Component Services. It is the DCOM object named MSDTSServer. Here are the steps to follow....(read more)