MultiServer SQL 2000 SP3 Administration

Hello,
I am trying to set up multiadministration server, since my company has 2
central cluster servers, with SQL Server 2000 SP3a, and 80 local sql server
2000 SP3a.
I have recently upgraded a central to SP3, and did the same for a local sql
server. Both are in the same domain, started by an special administrator
account for SQL Server services (both SQL Server service and SQL Agent).
I'm thus using Windows authentification. I tried to set up both server for
multiadministration, using SQL queries :
==> EXEC xp_sqlagent_msx_account N'SET', N'', N'', N''
to run multiadministration server under SQL agent account, and then
==>
EXEC sp_grantlogin 'MyDomain\MyUser'
USE msdb
EXEC sp_adduser 'MyDomain\MyUser', MyDomain\MyUser', 'TargetServersRole'
on each server, both central and target (local).
Then I try to set up multiadministration using EM, but the 'Specify MSX
Account' step fails, saying the account I'm using is not a valid MSX Account.
I can't find any more help on forums and BOL.
Your help will be much appreciated...This message was posted a few weeks
ago, but no answer on the SQL server tools forum. Thanks a lot for your help.
Xavier MarcatelCheck the section on "Multi-Server Administration" at this link:
http://www.sqlservercentral.com/columnists/bkelley/sp3coresecurity.asp
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"PetitGosaimass" <PetitGosaimass@.discussions.microsoft.com> wrote in message
news:63E1D6DA-68CB-4EAA-88FC-4462FBF9C5F4@.microsoft.com...
> Hello,
> I am trying to set up multiadministration server, since my company has 2
> central cluster servers, with SQL Server 2000 SP3a, and 80 local sql
> server
> 2000 SP3a.
> I have recently upgraded a central to SP3, and did the same for a local
> sql
> server. Both are in the same domain, started by an special administrator
> account for SQL Server services (both SQL Server service and SQL Agent).
> I'm thus using Windows authentification. I tried to set up both server for
> multiadministration, using SQL queries :
> ==> EXEC xp_sqlagent_msx_account N'SET', N'', N'', N''
> to run multiadministration server under SQL agent account, and then
> ==>
> EXEC sp_grantlogin 'MyDomain\MyUser'
> USE msdb
> EXEC sp_adduser 'MyDomain\MyUser', MyDomain\MyUser', 'TargetServersRole'
> on each server, both central and target (local).
> Then I try to set up multiadministration using EM, but the 'Specify MSX
> Account' step fails, saying the account I'm using is not a valid MSX
> Account.
> I can't find any more help on forums and BOL.
> Your help will be much appreciated...This message was posted a few weeks
> ago, but no answer on the SQL server tools forum. Thanks a lot for your
> help.
> Xavier Marcatel
>

multiprocesor licence

Hello, in our organization we have already per-seat licences for sql server
standard, we're planning to installar a new central server with two
processor, and the clients access it. I have to buy licences by processor or
can we still using the per-seat licences....
FabricioHi Fabricio.
You can licence SQL Server per seat with a multi-cpu server. The per
processor licence is intended for internet usage or very high numbers of
clients where the economics of per seat become expensive.
HTH
Regards,
Greg Linwood
SQL Server MVP
"Fabricio E. Gil" <fgil@.calpia.com.sv> wrote in message
news:eWLY0PvfDHA.1088@.TK2MSFTNGP10.phx.gbl...
> Hello, in our organization we have already per-seat licences for sql
server
> standard, we're planning to installar a new central server with two
> processor, and the clients access it. I have to buy licences by processor
or
> can we still using the per-seat licences....
>
> Fabricio
>

Multiple SQL Server - Central Server Problem

Hello,
We had some problems about our project. We have 60 sql server over the
country. We want to collect all data in central server. For collecting data
we developed a project. We get data from local server and transfer them to
central server. We have to transfer huge data. While we transfer data to
central server we are unable to access to defined DB in central database so
we can not query DB. it gives time out error. How can we get over this
problem. is there any idea?
Thanks,Odyssey (saintfi@.gmail.com) writes:
> We had some problems about our project. We have 60 sql server over the
> country. We want to collect all data in central server. For collecting
> data we developed a project. We get data from local server and transfer
> them to central server. We have to transfer huge data. While we transfer
> data to central server we are unable to access to defined DB in central
> database so we can not query DB. it gives time out error. How can we get
> over this problem. is there any idea?
With this scant information, it is very difficult to say that much
intelligent. How to you get the data from the local servers to the
central servers today? Replication? DTS? Linked server? Something else?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||What I do is -
1) Create a temp db (same schema as the intended db) on the
distination, e.g. the intended database is called CUSTOMERS, you create
a temp db called CUSOMERS_IMPORT with the identical schema.
2) Do the data transfer to the temp db, in this case CUSTOMERS_IMPORT
3) After the data is fully loaded, use sp_rename to switch the temp db
to the intended db and verse versa. In this example, rename CUSTOMERS
to CUSTOMERS_IMPORT, then rename CUSTOMERS_IMPORT to CUSTOMERS.
Re-apply indexes if any.
4) Delete from CUSTOMERS_IMPORT, which was the orignal db for the next
round of data loading (if it is a repeatable thing).
This way, the CUSTOEMRS database is still available for query while the
data is loaded in the temp db CUSTOMERS_IMPORT. The rename switch
takes about a second or two.
That how I max my db availablity for long data loading.
Mel

Multiple sources and one target

I'm thinking aboug using Service Broker to aggregate transactions from 12 remote SQL Servers to a central SQL Server. Service Broker is new to me, so I don't know what the proper setup would be.

We have five different kinds of transactions that can be sent from any of the remote servers. These remote servers truly are remote, connecting to the central server over a WAN. We don't want problems with one server to interfere with any other server. We also don't want problems with one kind of transaction to interfere with the processing of any other kind of transaction.

If were only talking about one remote server, I'm guessing I'd want to create 5 different services - one for each kind of transaction. Does that mean I'd also need to create 5 different routes on both the remote server and the central server?

And how would I scale this out to a dozen remote servers? Can I stick with 5 services on the central server, or would I need to define 5 different services for each remote server, so 60 total? And would that be 60 different routes defined on the central server? Based on my limited knowledge of routes and how they're tied to services, I think that would mean 60 routes, but maybe I'm missing something.

Please let me know. Thanks.

Service Broker seems like an excellent choice for the scenario that you have described. It provides isolation mechansims to ensure that two unrelated business transactions can run concurrently.

The primitive for communication in Service Broker is a conversation, which is a reliable, durable session for exchange of messages. Two-party conversations are known as dialogs and they contain two endpoints -- an initiator and a target. Services are nothing but logical entities that can initiate or be the target for a conversation. They are addressable (via routes) and securable (via certificates). But one service can have multiple conversation endpoints to deal with concurrent conversations.

Given that, in general you would create a 'Service' to handle all conversations for some business function which needs to have a distinct identity and location. If you decide to move the service to another server or issue a new certificate, you will affect all conversations targetting or initiating from that service. So if you feel that the 5 business transactions are all distinct and may in the future be performed at different locations, you are better off with 5 services. If that seems impossible or highly unlikely, stick with a single service.

Conversations can be strongly typed by defining formal contracts that specify what type of messages may be sent by each endpoint. Since a service can expose multiple contracts, it can accept conversations belonging to different contracts. In your scenario, you might have different contracts for each of the 5 different business transaction types.

A database can certainly support more than just 60 routes, so don't feel that the number of routes is going to be a performance bottleneck. If you think it might be a managability nightmare, you could consider investing into something we call "Broker Configuration Notification". If you enable this in your database and a new conversation cannot find a route, Service Broker will send a special "Missing Route" message to a service of your choice. You could then implement this "route resolution" service to lookup the route from a directory and create the route in your local database and Service Broker will automatically try to deliver the message of former conversation.

One thing to be aware of in a WAN environment is that Service Broker requires both instances of SQL Server to be able to connect to each other over TCP/IP. That means that if either party is behind a firewall/NAT, the appropriate ports need to be opened for two-way communication.

Hope that helps,
Rushi

|||

Thanks for the information!

David