SQL-Articles

To improve the query performance we decided to have separate filegroup for indexes. We decided to move the indexes of very large tables indexes to the new filegroup. Our intention was to separate the index from the data i.e. have the table data (Clustered index) in one filegroup and Nonclustered indexes in another separate filegroup to improve I/O.

We have a table with 1.5 million records; the following is the table schema and its present in [Primary] filegroup which is the default:

CREATE TABLE [dbo].[testmember](
 [memberid] [bigint] IDENTITY(1,1) NOT NULL,
 [name] [nvarchar](50) NULL,
 [emailaddress] [nvarchar](100) NULL,
 [firstname] [nvarchar](50) NULL,
 [createddate] [datetime] NULL,
 [testindex] [varchar](4000) NULL,
 CONSTRAINT [PK_testmember] PRIMARY KEY CLUSTERED (
 [memberid] ASC
 )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF,

ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)) ON [primary]

 Read more...

VN:F [1.0.8_357]
please wait...
Rating: 0.0/5 (0 votes cast)
				

					Troubleshoot
					Index
				
				

			

I was reading PSS MSDN blogs and found an useful information about SQL 2008 installation and thought of sharing this with you. There is a know issue with SQL 2008 setup (refer KB article http://support.microsoft.com/kb/957453/) and they have fixed this in cumulative update 1. Lets see how to use CU1 to fix this installation issue.

• Before Installing SQL 2008 from CD \ DVD, download cumulative update 1(http://support.microsoft.com/kb/956717) and install it in the machine, this will install the newly updated setup support files alone with the version 10.00.1763.0.

• Once this is done, you can now proceed with SQL 2008 installation. When the installation starts it will use the newly installed setup support files bypassing the know issue with the KB article 957453.

• After successful installation of SQL 2008 you need to re-run cumulative update 1 to update all SQL 2008 components.

That’s it you have done with the installation!!

To know more on this topic you can refer this PSS blog link “How to fix your SQL Server 2008 Setup before you run setup…“.

SQL Server 2008, Troubleshoot Administration, SQL Installation

Introduction:

I could see most of them are asking known errors of database mirroring in many forums and we are repeatedly providing the same solution. Hence I thought of consolidating all the know errors and its solutions for database mirroring in this post as FAQ. Probably I must pickup all the know errors, If I have missed anything you can very well post in FORUMS section.

Question:

Database mirroring is disabled by default. Database mirroring is currently provided for evaluation purposes only and is not to be used in production environments. To enable database mirroring for evaluation purposes, use trace flag 1400 during startup. For more information about trace flags and startup options, see SQL Server Books Online. (Microsoft SQL Server, Error: 1498)

Answer:
This is a common error & everyone is know to this error. Database mirroring is officially supported from SQL Server 2005 SP1, hence in the RTM version database mirroring is disabled by default. You can use TRACE 1400 to enable in RTM version or else update your SQL Server with latest service pack.
Adding Trace Flag to Startup parameter

• Goto RUN –> Type sqlservermanager.msc
• Right click on SQL Server(instancename) service and click on properties
• Click on Advanced tab
• In the startup parameters enter this ;-T1400 and click on OK
• Restart SQLservices and then try configuring db mirroring

Or

• Update SQL Server to latest service pack.

—————————————————

Question:

When I configure mirroring I’m receiving the below errror,
One or more of the server network addresses lacks a fully qualified domain name (FQDN). Specify the FQDN for each server, and click start mirroring again

Answer:

Assume that your principal server is A and mirror server is B and you have configured mirroring for Adventure Works database. The fully qualified computer name of each server can be found running the following from the command prompt:
IPCONFIG /ALL
Concatenate the “Host Name” and “Primary DNS Suffix”. If you see something like:
Host Name . . . . . . . . . . . . : A
Primary Dns Suffix . . . . . . . : corp.mycompany.com
Then the computer name is just A.corp.mycompany.com. Prefix ‘TCP://’ and append ‘:’ and you then have the partner name.

– Specify the partner from the mirror server
ALTER DATABASE [AdventureWorks] SET PARTNER = N‘TCP://A.corp.mycompany.com:5022′;

– Specify the partner from the principal server
ALTER DATABASE [AdventureWorks] SET PARTNER = N‘TCP://B.corp.mycompany.com:5022′;

Note: Replace the dbname before using the above script

—————————————————

Question:

Why I’m getting the below error message while configuring database mirroring?
Msg 1416, Level 16, State 31, Line 3. Database is not configured for database mirroring

Answer:

You need to restore the Full backup from principal server using With NoRecovery option and also one transactional log backup from principal server using With NoRecovery option and then start configuring mirroring.

—————————————————

Question:
IS it possible to configure mirroring between Standard Edition & Enterprise Edition or Vice Versa?

Answer:
Nope its not possible, both principal and mirror should have same edition. Refer the KB article http://msdn.microsoft.com/en-us/library/ms366349.aspx

—————————————————

Question:
Can I load balance my mirrored database (i.e can i use mirrored for SELECT query) like log shipping in standby mode?

Answer:
Yes offcourse its possible if your mirror server is running Enterprise edition. You can take snapshot of your mirrored database and then you can query against the snapshot to retrive data’s. Refer the article Database Snapshot.

—————————————————

Question:
Is it possible to take backup of mirrored database in mirror server?

Answer:
No, you won’t be able to run BACKUP command against a mirrored database in mirror server.

—————————————————

Question:
Why I’m getting login failed error after failovering to mirrored database?

Answer:
This is because there might be mismatch in SID between the logins & user in database else the login doesn’t exist in the mirror server.

Consider you have a login test in principal server and this test also exists in the principal database as a user. When you mirror the database, in the mirror server that database will be in restoring state and user test will have the same SID in the database on mirror server as in principal server. Once you have failovered to mirror server and mirror database is become principal, if you try to login with the test ID and then if you connect to the database you will be getting login failed because the test login in the mirror server will have different SID and the database user test will have different SID (i.e principal server login SID), hence to resolve this conflict you need to use sp_change_users_login procedure to map the original SID of the login to db users SID. If the login doesn’t exist in the mirror server then you need to create a new login in the same name and map the SID back using the above procedure. You can refer the article How to fix orphaned SQL Server users.

Consider the above situation happens in your production environment which will increase the downtime (eventhough mirroring is configured in High availability mode) since you need to map the logins back. Hence the best way to avoid this situation is that before configuring mirroring copy the logins (i.e the logins used for the application and have access to the mirror db) from the principal server to destination server with same SID. You can use Transfer Logins Task SSIS utility or else use How to transfer logins and passwords between instances of SQL Server

—————————————————

Question:
Can I create multiple endpoints for configuring different databases for mirroring and point each database to unique endpoint.

Answer:
No, thats not possible. You can create only one endpoint in a server for database mirroring you need to use this endpoint to configuring db mirroring for all the databases.

—————————————————

Question:
What is the recommended maximum number of mirror database I can configure for an instance.

Answer:
For 32-bit system MS recommends not more than 10 database to participate in database mirroring, refer link. There is actually no limit on this you can configure many databases, but adding more and more databases into mirroring will hurt the server performance and network IO, refer the link “How many databases can you *really* mirror per instance?”

—————————————————

Question:
I don’t have a witness server, Incase if primary server goes down how can I failover the mirrored database.

Answer:
The best thing I can suggest you is to refer the link Failover in Database Mirroring to know the steps for manual failover.

—————————————————

Question:
Can I configure a single database to be mirrored to more than one server. i.e) One source & many destination like logshipping.

Answer:
No, thats not possible in Database mirroring, its one to one configuration.

—————————————————

Question:
How can I increase Heartbeat time between principal and mirror server?? By default its 10 sec and I need to change this (this fills up eventlog too)

Answer:
You can use the below command to change the heartbeat time between principal and mirror. Execute the below script in principal server
Syntax
ALTER DATABASE AdventureWorks SET PARTNER TIMEOUT <TimeOutInSeconds>
Example
ALTER DATABASE AdventureWorks SET PARTNER TIMEOUT 30

Note: Before using the script change the dbname.

—————————————————

Question: Is High performance mode supported in Standard Edition of SQL 2005?

Answer: Sql Server 2005 Standard edition supports only Full transaction safety level i.e it does not support High performance mode (Asynchronous).

—————————————————

Question: Where can I get database mirroring features supported by various editions of SQL Server 2005???

Answer : You can check the subtopic “Database Mirroring and SQL Server 2005 Editions” in the link “Database mirroring in SQL Server 2005“ 

—————————————————

General, Troubleshoot Database Mirroring

OWC11 Installation Failure

Sample Error from errorlog–>MSI (s) (C4:C8) [19:04:30:953]: Product: Microsoft Office 2003 Web Components — Error 1706. Setup cannot find the required files. Check your connection to the network, or CD-ROM drive. For other potential solutions to this problem, see C:\Program Files\Microsoft Office\OFFICE11\1033\SETUP.CHM.

Solution 1: Uninstall Sql server 2005 completely and then try to manually run the OWC11.msi file available in Tools folder of sql 2005.Then try to install sql 2005 freshly !

Solution 2: Just uninstall Office web components from Add \ Remove programs and then try to install sql 2005

—————————————–

Read more…

SQL Server 2005, Troubleshoot SQL Installation

Microsoft SQL Server, Error: 14262
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
The specified server name does not exist

Solution:

Need to ensure that the Sql server name is the same as Windows server name. You can check this by running SELECT @@VERSION. The output of this query should be the name of your Windows server name else you need to perform the below steps,

sp_dropserver ‘old server name’
go
sp_addserver ‘new server name’, local
go

You need to restart the sql services for the above command to take effect.

—————————————————-

Read more…

Troubleshoot