Mirroring

Hello,

I have Microsoft SQL SERVER 2005 installed in my system.

To try out mirroring, I rightclicked on the database went to the tasks, but i found that in Status its written

"This database has not been configured for mirroring"

What shall I do now?

I think there are 2 possibilities,

1. You have Sql server RTM.......Microsoft supports mirroring from Sql 2005 SP1.......with RTM also you can configure mirroring but prior to that you need to start sql server with Trace flag -T1400 and then proceed with mirroring.

2. You need to take full backup and one transactional log backup in principal server and restore it in mirror server using the option WITH NORECOVERY for both the full backup and tran log backup.............then start configuring mirroring it will work perfectly...........

refer these links for mirroring,

http://sql-articles.com/articles.php?pageNum_alltop=3&totalRows_alltop=35

http://sql-articles.com/articles/dbmrr.htm

Revert if you need any more help............

Thanxx

Deepak

|||

downloading sp1.

Will certainly revert if any problem arises.

Thank you

|||Which SQL edition you installed? Only Standard and Enterprise supports Database Mirroring (SQL Server 2005 Features Comparison - http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx).|||

You can download the latest service pack SP2 for SQL 2005 from

http://technet.microsoft.com/en-us/sqlserver/bb331754.aspx instead of SP1..........

Mirroring

Hello,

I have Microsoft SQL SERVER 2005 installed in my system.

To try out mirroring, I rightclicked on the database went to the tasks, but i found that in Status its written

"This database has not been configured for mirroring"

What shall I do now?

I think there are 2 possibilities,

1. You have Sql server RTM.......Microsoft supports mirroring from Sql 2005 SP1.......with RTM also you can configure mirroring but prior to that you need to start sql server with Trace flag -T1400 and then proceed with mirroring.

2. You need to take full backup and one transactional log backup in principal server and restore it in mirror server using the option WITH NORECOVERY for both the full backup and tran log backup.............then start configuring mirroring it will work perfectly...........

refer these links for mirroring,

http://sql-articles.com/articles.php?pageNum_alltop=3&totalRows_alltop=35

http://sql-articles.com/articles/dbmrr.htm

Revert if you need any more help............

Thanxx

Deepak

|||

downloading sp1.

Will certainly revert if any problem arises.

Thank you

|||Which SQL edition you installed? Only Standard and Enterprise supports Database Mirroring (SQL Server 2005 Features Comparison - http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx).|||

You can download the latest service pack SP2 for SQL 2005 from

http://technet.microsoft.com/en-us/sqlserver/bb331754.aspx instead of SP1..........

Mirror Operating Mode change problem

I am unable to change the mirror operating mode on a test database mirror I have set up. I installed (from MSDN) the RTM SQL 2005 Std Edition and applied SP1, then set up a mirror, which works fine but only on High Safety mode. I want to use High Performance mode, but the operation mode radio buttons are greyed out for me in the mirror properties page. Also, I tried running:

alter database TestDB set safety off

and recieve the error:

Msg 1473, Level 16, State 6, Line 1
This SQL Server edition does not allow changing the safety level. ALTER DATABASE command failed.

Are the various mirror modes not supported on the Std edition or am I overlooking something?

Thanks.

From the db engine server properties:
Product: Microsoft SQL Server Standard Edition
OS: Microsoft Windows NT 5.2 (3790)
Platform: NT INTEL X86
Version: 9.00.2047.00

In STD saftey can only be FULL.

Thanks,

Mark

|||I was hoping this wasn't going to be the case when mirroring became fully supported in sp1. To me it doesn't make sense to give the standard edition only synchronous which requires far more resources and infrastructure. Even microsoft's own best practices suggest you should try mirroring in asynchronous form in a production environment before you transition to synchronous to make sure your infrastructure can support it. For us that just killed 90% of the scenarios we were considering using mirroring for.|||

Asynchronous mirroring is fully supported in Enterprise Edition.

Simply upgrade your installations to Enterprise Edition, and you will have your scenarios back. Yes, it costs more, but as you have found out that asynchronous mirroring has a lot more benefits as far as disaster recovery and high performance workloads. It was natural to put it in a higher SKU.

Thanks,

Mark

|||Unfortunately that would cost a few tens of thousands of dollars. I guess my point was that if you're running the kinds of machines that are needed for synchronous mirroring chances are you won't be using standard edition anyway. I'm not trying to criticize, just letting you know that cost-benefit wise it's not even close for us and I would venture to guess for a lot of other people.|||

Understood. It's always about money, isn't it?

It all depends on the workload actually. If you have a moderate workload, and the systems are right next to each other, then synchronous mirroring will probably be OK. I have plenty of customers running like that now; in fact over half of my internal customers were synchronous at SP1 ship, and not all of them had huge powerful servers.

Thanks,

Mark

|||

Lol, yeah that's why I hate talking to hardware vendors, they only have one answer to any question.

I may look into syncrhonous mirroring more. I tried it with 2 of our test servers and it was less than stellar but I admit I didn't do any full blown application testing. Thanks for the feedback!

sql

Minimum RAM Requirement For installation

256 RAM is running on my computer. is it possible to run Microsoft SQL Server 2005 Reporting Services on it?

NOTE: I installed Microsoft SQL Server 2005 Reporting Services. By the installation, it gives an error, that system resource is not enough. Although this, installation resume, but program not worked at the end.

I

No.

http://www.microsoft.com/downloads/details.aspx?familyid=1E53F882-0C16-4847-B331-132274AE8C84&displaylang=en#Requirements

Mimic SQL Server environment

I have SQL Server 2000 installed on Windows 2003 server. SQL Server
environment was set up about a year ago. I would like to known all of the
items I need to look at on SQL Server to mimic this environment. I have
started with SQL Server configuration (sp_Configure).
What items am I missing to mimic SQL Server?
Thanks,
Depends on what you mean by mimic...
You might wish to ensure the IO subsystems are the same.
You might wish to ensure each database is set up similary in physical
structure as well as attributes ( sp_dboption)
You might with to ensure the logins match. ( look in books on line for log
shipping and there is a document about how to make the warm standby the
production server. It includes a section on making the logins and users
match.)
Think about whether replication needs to be matched.
Think about sql agent jobs, etc ( included in msdb).
Hope this helps...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Mark T." <Mark T.@.discussions.microsoft.com> wrote in message
news:D76C7697-5336-494A-ADA5-ABC75B7AB421@.microsoft.com...
> I have SQL Server 2000 installed on Windows 2003 server. SQL Server
> environment was set up about a year ago. I would like to known all of the
> items I need to look at on SQL Server to mimic this environment. I have
> started with SQL Server configuration (sp_Configure).
> What items am I missing to mimic SQL Server?
> Thanks,

Mimic SQL Server environment

I have SQL Server 2000 installed on Windows 2003 server. SQL Server
environment was set up about a year ago. I would like to known all of the
items I need to look at on SQL Server to mimic this environment. I have
started with SQL Server configuration (sp_Configure).
What items am I missing to mimic SQL Server?
Thanks,Depends on what you mean by mimic...
You might wish to ensure the IO subsystems are the same.
You might wish to ensure each database is set up similary in physical
structure as well as attributes ( sp_dboption)
You might with to ensure the logins match. ( look in books on line for log
shipping and there is a document about how to make the warm standby the
production server. It includes a section on making the logins and users
match.)
Think about whether replication needs to be matched.
Think about sql agent jobs, etc ( included in msdb).
Hope this helps...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Mark T." <Mark T.@.discussions.microsoft.com> wrote in message
news:D76C7697-5336-494A-ADA5-ABC75B7AB421@.microsoft.com...
> I have SQL Server 2000 installed on Windows 2003 server. SQL Server
> environment was set up about a year ago. I would like to known all of the
> items I need to look at on SQL Server to mimic this environment. I have
> started with SQL Server configuration (sp_Configure).
> What items am I missing to mimic SQL Server?
> Thanks,

Mimic SQL Server environment

I have SQL Server 2000 installed on Windows 2003 server. SQL Server
environment was set up about a year ago. I would like to known all of the
items I need to look at on SQL Server to mimic this environment. I have
started with SQL Server configuration (sp_Configure).
What items am I missing to mimic SQL Server?
Thanks,Depends on what you mean by mimic...
You might wish to ensure the IO subsystems are the same.
You might wish to ensure each database is set up similary in physical
structure as well as attributes ( sp_dboption)
You might with to ensure the logins match. ( look in books on line for log
shipping and there is a document about how to make the warm standby the
production server. It includes a section on making the logins and users
match.)
Think about whether replication needs to be matched.
Think about sql agent jobs, etc ( included in msdb).
Hope this helps...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Mark T." <Mark T.@.discussions.microsoft.com> wrote in message
news:D76C7697-5336-494A-ADA5-ABC75B7AB421@.microsoft.com...
> I have SQL Server 2000 installed on Windows 2003 server. SQL Server
> environment was set up about a year ago. I would like to known all of the
> items I need to look at on SQL Server to mimic this environment. I have
> started with SQL Server configuration (sp_Configure).
> What items am I missing to mimic SQL Server?
> Thanks,

Migration to SQL Server 2k5

Hi all,

I have script written in SQL Server 2000.

When i tried to migrate to SQL Server 2005, it is working fine in the SQL Server 2005 installed in Windows 2003 Server. System tables(syscolumns, sysobjects etc)are created in "Database->Tables->SystemTable".

But when i tried to run in SQL Server 2005 installed in Windows XP, I'm facing the following problems.

1."The object 'CK_SavedSear_LastL_4B422AD5' is dependent on column'LastLoadedTime'

"ALTER TABLE DROP COLUMN LastLoadedTime failed because one or more objects access this column"

2.Moreover none of the tables had been created in "Database->Tables->SystemTable".

In both XP and 2003 Server, While installing, i have choosen same options.

Please anybody could help me to resolve this issue.

Thanks and Regards,

Nilavu

You should try a different forum: http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=93&SiteID=1

-Jamie

Migration to SQL Server 2k5

Hi all,

I have script written in SQL Server 2000.

When i tried to migrate to SQL Server 2005, it is working fine in the SQL Server 2005 installed in Windows 2003 Server. System tables(syscolumns, sysobjects etc)are created in "Database->Tables->SystemTable".

But when i tried to run in SQL Server 2005 installed in Windows XP, I'm facing the following problems.

1."The object 'CK_SavedSear_LastL_4B422AD5' is dependent on column'LastLoadedTime'

"ALTER TABLE DROP COLUMN LastLoadedTime failed because one or more objects access this column"

2.Moreover none of the tables had been created in "Database->Tables->SystemTable".

In both XP and 2003 Server, While installing, i have choosen same options.

Please anybody could help me to resolve this issue.

Thanks and Regards,

Nilavu

Can you give detailed steps on what exactly you're doing, it's unclear to me what you mean by "migrate". Are you running setup/upgrade, which is failing?|||

Hi,

First i have to thank you for the valuable efforts.

Problem is not with upgrade/Setup of SQL Server 2005, With scripts. Whether the scripts written in SQL Server 2000 will run in SQL Server 2005 without doing any modification .

Here is the scenario.

Actually i have scripts that are written for SQL Server 2000.

Now my clients are using SQL Server 2005.

So i have to test whether my scripts could run in SQL Server 2005 without any modification.

When i tried to run the scripts in SQL Server 2005 installed in Windows XP OS, it is giving error.

At the same time there are no tables created in "Database->Tables->SystemTable".

But Same script is executed properly in SQL Server 2005 installed in Windows 2003 Server.

In this case tables like "sysobjects" etc are created in "Database->Tables->SystemTable".

Still you are not able to understand, Please send me mail.

Please suggest me whether i have to do any changes in script level or in settings of SQL Server 2005.

Moreover Being installed SQL Server 2005, Why tables like "Sysobjects" have been created in 2003 Server OS while not in XP.

Kindly clarify my doubts.

Thanks and Regards,

Nilavu

|||I don't think this has anything to do with sql server on a given OS. If you look at the error message closely, it says the alter table drop column failed because a check constraint exists. You have to drop the check constraint first before you can drop the column.|||

Hi,

Yes Greg, you are right, i'm getting the same error message.

But the problem is, the same scripts works fine in ,

1.Windows XP, SQL Server 2000

2.Windows 2003 Server, SQL Server 2005

But i'm getting error in Windows XP, SQL Server 2005.

Thats what i could not find the solution.

I would like to mention one more different behaviour in SQL Server 2005,

In Windows 2003 Server, SQL Server 2005, "Tables->SystemTables" system tables like "sysobjects" are created.

But in Windows XP, SQL Server 2005, There are no such tables in "Tables->SystemTables".

Could you explain me why it is behaving in two different ways?

Thanks and Regards,

Nilavu.

|||

Look at the error message closely, it says a check constraint exists. You cannot drop a column if a check constraint exists on that column. Please check to see if this check constraint exists in both databases, as well as whether your script is explicitly dropping this constraint. If your script is explicitly dropping this constriant, maybe the script is failing beforehand. If your script is not dropping this constraint, then your databases are not exactly in sync, which is why it is failing on one db and not the other.

|||

Hi,

Thanks Greg.

Before dropping the column, code which is trying to find check constraint fails.

The "info" column in the "sysobjects" table contains only zero values for all objects in case of SQL Server 2005.

But in case of SQL Server 2000, there are certain values have entered.

Could you tell me, Where are the "info" column's values are mapped? Which table and column i have to refer instead of "sysobjects->info".

Here is my code,

IF ((SELECT COUNT(sysobjectsconstraint.id) FROM syscolumns

INNER JOIN sysobjects AS sysobjectstable ON syscolumns.id = sysobjectstable.id

INNER JOIN sysobjects AS sysobjectsconstraint ON syscolumns.id = sysobjectsconstraint.parent_obj

AND sysobjectsconstraint.info = syscolumns.colid AND sysobjectsconstraint.xtype = 'C'

WHERE syscolumns.name = 'RemoveFromSyncTime'

AND sysobjectstable.xtype = 'U' AND sysobjectstable.category & 2 <> 2

AND sysobjectstable.NAME = 'SavedSearchLoadScheduleItem') = 1)

BEGIN

SET @.sqlcommand = (

SELECT 'ALTER TABLE ' +

QUOTENAME(USER_NAME(sysobjectstable.uid)) + '.' +

QUOTENAME(sysobjectstable.name) +

' DROP CONSTRAINT ' +

QUOTENAME(sysobjectsconstraint.name)

FROM syscolumns

INNER JOIN sysobjects AS sysobjectstable ON syscolumns.id = sysobjectstable.id

INNER JOIN sysobjects AS sysobjectsconstraint ON syscolumns.id = sysobjectsconstraint.parent_obj

AND sysobjectsconstraint.info = syscolumns.colid AND sysobjectsconstraint.xtype = 'C'

WHERE syscolumns.name = 'RemoveFromSyncTime'

AND sysobjectstable.xtype = 'U' AND sysobjectstable.category & 2 <> 2

AND sysobjectstable.NAME = 'SavedSearchLoadScheduleItem'

)

The "If" condition fails in case of SQLServer 2005, whereas in case of SQLServer 2000, i'm getting value of 1.

Thanks and Regards,

Nilavu.

|||Moving to TSQL forum, someone more familiar with metadata and system tables can offer a better answer.|||The issue is that you are using columns reserved for internal use in your query. So any assumptions made as to what values it can contain will be wrong in SQL Server 2005. It might even break in SQL Server 2000 with a future service pack for example. You can determine CHECK constraints on a table by using INFORMATION_SCHEMA.CHECK_CONSTRAINTS view along with INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE view. Or just the INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE view with OBJECTPROPERTYEX metadata function to filter check constraints. This will work the same in SQL Server 2000/2005. Or you could use sp_helpconstraint system SP for example. It is best to use the documented objects so that your code works consistently and you don't rely on undocumented interfaces that can be modified anytime by us.|||

Hi,

Thank you for your timely help Umachandar.

Its working fine now.

Regards,

Nilavu.

Migration Assistant For Access Can''t Find SQL Database?

I've installed SQL 2005 Express Addition which created a default database using Windows Authentication -- MachineName/SQLEXPRES. I want to convert some access databases to sql server in the migration assistant, however, when I try to connect the migration assistant to the default database I can't find the database in the drop down box wizard? I'm perplexed? Please shed some light or point me to some articles that will help. Thanks.

It is not practical to use standard Express to migrate Access so see if you can use the one with Management Studio instead, this will give you control of all the steps. Hope this helps.

http://msdn.microsoft.com/vstudio/express/sql/download/

|||

Yes, Management Studio is the one I used. I am able to connect via Management Studio, however, the Migration Assistant can't locate it? Looking for other suggestions? Thanks.

|||

Try the eval good for 6 months, you could buy the developer for under $40 online if you have the ram this can help you move Access if all you have is tables and queries. If you have VBA you may need professional help. I have seen Access migration taking six months or more because it lets users add stuff SQL Server will not accept and it uses limited data types.

http://www.microsoft.com/sql/downloads/trial-software.mspx

|||

I'm not sure I understand what you mean by "default database" in this context. SQL Server does not create any default databases when you install it.

In my use of SSMS I find that the drop down box that show the database name does not actually show the list of databases in the server because it has not actually connected to the server yet, so it can not list the databases. You can either specify a new database to be created or specify an existing database to open once you connect, but you need to type the name of the database, it won't populate the list for you.

Regards,

Mike