combining sql server instances, need help

I have two sql servers, each with several databases on them. Is there an automated, or way to script, combining these servers into one? I want to create a 3rd server, and put all the databases from both server 1 and 2 on it as a back up percaution. My theory is that on server 1 and 2, I will back those databases up nightly via script, and then on server 3, restore the databases nightly. If either server 1 or 2 dies, I simply put that IP address on server 3, and poof! The databases do not go down (however the backup may be some hours old). That gives me time to troubleshoot the down server, and customers do not suffer downtime. Server 3 would also need to be complete with jobs, full text catalogs, logins, etc...

Has anyone developed a system like this? I'm not a wiz in qa yet, so this will take me some time to develop and refine. If you have any tips, I would appreciate it.

Thanks
TomIt's called Log Shipping in SQL 2000. There are informations in BOL and Microsoft website regarding how to set it up and change primary or secondary role when disaster strike.|||Thanks for your answer.
Keep in mind, I don't just want 1 primary, and 1 secondary server. Actually, I have several sql servers. I would like to have all these databases ready on one single spare sql server. Is this possible with log shipping?

Thanks
Tom|||Remember , log shipping is available in SQl2000 only for SQl 7.0 Servers you hav to manually set it up . If you are moe intrested , let me know and I can send you some sql files . I have L/shipping setup on all of my production servers .. (17 Primary Servers use 9 Standby servers consolidating 2 Server to 1|||aashu:

That would be great! For example, I have 10 sql servers, with 10 databases each. Now I want to set up one sql server as a backup, and put all 100 databases on it. so when one sql server goes down, I just put that IP address on my backup server, and delete the other 90 databases.

Thanks Tom|||PS, they are all sql 2000 servers|||CAUTION! Log shipping is only available in Enterprise Edition of SQL Server. This could be quite costly, depending on how many machines you have...|||Thanks, they are all sql 2000 Enterprise edition servers|||I am attaching files|||Save the file on the the PATH as mentioned in the previous file as synch_data.bat . The previous file creates a job (has to be run on SQl Qry Analyzer) . The step of the job is calling this batch file attached. You will have to check all the physical references according to your servers .
If you need more detail Let me know|||thanks aashu, I will dig into this next week, I really appreciate it.

Tommy|||No Problm .. I will help you out and make it work for you

collection name question

1 of my SQL 2000 servers has few databases that acts as backend for web based
..NET applications, most of them is in English and they have collection name:
SQL_Latin1_General_CP1_Cl_AS.. I just noticed that recently deployed db which
stores Italian text has collection name: <empty> .. my questions are what it
should be? are there any potential problems we can run into? can you change
it afterwords?
TIA
Hi Rafal,
Databases can not have an empty collation. You can check the collation for
the database in Enterprise Manager by right-clicking the database and
selecting Properties, or by executing
SELECT DATABASEPROPERTYEX('<db name>', 'Collation')
in Query Analyzer.
The only place I can think of where you might see an 'empty' collation, is
when you use the table designer in Enterprise Manager and you look at a
character column that has the default collation for the database.
Jacco Schalkwijk
SQL Server MVP
"Rafal W." <RafalW@.discussions.microsoft.com> wrote in message
news:EA462D24-9ECE-4916-A41D-7CEDF1EE969D@.microsoft.com...
>1 of my SQL 2000 servers has few databases that acts as backend for web
>based
> .NET applications, most of them is in English and they have collection
> name:
> SQL_Latin1_General_CP1_Cl_AS.. I just noticed that recently deployed db
> which
> stores Italian text has collection name: <empty> .. my questions are what
> it
> should be? are there any potential problems we can run into? can you
> change
> it afterwords?
> TIA
>

collection name question

1 of my SQL 2000 servers has few databases that acts as backend for web base
d
.NET applications, most of them is in English and they have collection name
:
SQL_Latin1_General_CP1_Cl_AS.. I just noticed that recently deployed db whic
h
stores Italian text has collection name: <empty> .. my questions are what it
should be? are there any potential problems we can run into? can you change
it afterwords?
TIAHi Rafal,
Databases can not have an empty collation. You can check the collation for
the database in Enterprise Manager by right-clicking the database and
selecting Properties, or by executing
SELECT DATABASEPROPERTYEX('<db name>', 'Collation')
in Query Analyzer.
The only place I can think of where you might see an 'empty' collation, is
when you use the table designer in Enterprise Manager and you look at a
character column that has the default collation for the database.
Jacco Schalkwijk
SQL Server MVP
"Rafal W." <RafalW@.discussions.microsoft.com> wrote in message
news:EA462D24-9ECE-4916-A41D-7CEDF1EE969D@.microsoft.com...
>1 of my SQL 2000 servers has few databases that acts as backend for web
>based
> .NET applications, most of them is in English and they have collection
> name:
> SQL_Latin1_General_CP1_Cl_AS.. I just noticed that recently deployed db
> which
> stores Italian text has collection name: <empty> .. my questions are what
> it
> should be? are there any potential problems we can run into? can you
> change
> it afterwords?
> TIA
>

collection name question

1 of my SQL 2000 servers has few databases that acts as backend for web based
.NET applications, most of them is in English and they have collection name:
SQL_Latin1_General_CP1_Cl_AS.. I just noticed that recently deployed db which
stores Italian text has collection name: <empty> .. my questions are what it
should be? are there any potential problems we can run into? can you change
it afterwords?
TIAHi Rafal,
Databases can not have an empty collation. You can check the collation for
the database in Enterprise Manager by right-clicking the database and
selecting Properties, or by executing
SELECT DATABASEPROPERTYEX('<db name>', 'Collation')
in Query Analyzer.
The only place I can think of where you might see an 'empty' collation, is
when you use the table designer in Enterprise Manager and you look at a
character column that has the default collation for the database.
--
Jacco Schalkwijk
SQL Server MVP
"Rafal W." <RafalW@.discussions.microsoft.com> wrote in message
news:EA462D24-9ECE-4916-A41D-7CEDF1EE969D@.microsoft.com...
>1 of my SQL 2000 servers has few databases that acts as backend for web
>based
> .NET applications, most of them is in English and they have collection
> name:
> SQL_Latin1_General_CP1_Cl_AS.. I just noticed that recently deployed db
> which
> stores Italian text has collection name: <empty> .. my questions are what
> it
> should be? are there any potential problems we can run into? can you
> change
> it afterwords?
> TIA
>

Collect SSRS/SSAS schema (metadata)

Hi all,

I would like to collect metadata from cubes&reports automatically from servers SSAS and SSRS. Metadata include name, description, dimensions, members, permissions, etc. Then I store it into a table to be searchable.

I am searching the best solution for this.
Maybe it would be a SQL stored procedure.
Do anybody has an idea or some piece of help?

thx.
attila

Both servers expose management APIs. The Report Server has web service management APIs while SSAS comes with AMO library.

Collect SSAS / SSRS schema (metadata)

Hi all,

I would like to collect metadata from cubes&reports automatically from servers SSAS and SSRS. Metadata include name, description, dimensions, members, permissions, etc. Then I store it into a table to be searchable.

I am searching the best solution for this.
Maybe it would be a SQL stored procedure.
Has anybody an idea or some piece of help?

thx.
attila

Hey attila,

This can be done using AMO and a little code to store the metadata in some sort of db.

There is some good information here

(really good code examples, too)

Hope that helps a little,
C

Collation problem with Oracle as linked server

I have the following configuration of the servers:
1.Oracle 9i (database codepage is CL8MSWIN1251)
2. MS SQL Server 2000 Standard Edition (server codepage is
Cyrillic_General_CI_AS)
Oracle is the linked server in the SQL. When I use
query "Select * from openquery(ORASRV, 'select field1,
field2 from table1')" I receive question symbols ("?")
instead of russian letters.
What should I do to correct this situation?Sergey,
I don't know Oracle (sorry) but the COLLATE command is used by SQL Server to
move characters into a particular collation.
I see that Cyrillic_General is considered to be in code page 1251, which is
what Oracle is using, so I don't know why you would be getting back only
?.
Try:
'select field1 COLLATE Cyrillic_General_CI_AS,
field2 COLLATE Cyrillic_General_CI_AS from table1')
See if that resolves the problem.
Russell Fields
"Sergey Khanzhin" <skhan@.mail.ru> wrote in message
news:0d7d01c36177$e7ba61e0$a101280a@.phx.gbl...
> I have the following configuration of the servers:
> 1.Oracle 9i (database codepage is CL8MSWIN1251)
> 2. MS SQL Server 2000 Standard Edition (server codepage is
> Cyrillic_General_CI_AS)
>
> Oracle is the linked server in the SQL. When I use
> query "Select * from openquery(ORASRV, 'select field1,
> field2 from table1')" I receive question symbols ("?")
> instead of russian letters.
>
> What should I do to correct this situation?
>