Migrating to SQL 2005: network

Showing posts with label network. Show all posts

Friday, March 30, 2012

Mirroring dedicated traffic

We are using the following configuration:

2 stand alone Windows 2003 servers 64 bit.

Each have two Gig Network cards configured with different IP addresses.

Each running multiple instances of SQL Server 64.

I am trying to set up a mirror where mirroring traffic between servers will be dedicated to a secondary IP address on the second NIC.

I am also trying to avoid Windows authentication.

Interesting enough: Security Configuration screen suggesting you to use fully qualified TCP addresses and, at the same time, does not give you such an option...

Would someone please point me in the right direction?

Thank you.

Use certificate authentication. Look up "database mirroring [SQL Server], certificates" in Books Online.|||

Thank you very much for your response.

That is definetely a lot of information.

As about "database mirroring, certificates" - there is no such a topic in BOL. There is a "Setting Up database mirroring using certificates (Transact SQL)". That is a great topic! However, I thought that my question was specific? Sorry if not. I asked about dedicating NIC No.2 to a traffic generated by mirroring process. Unfortunately, topic mentioned by you does not have anything about TCP/IP; adding port numbers and/or netowrk cards to the mirror. (Actually, it is roughly covering even the main topic since witness installation totally skipped).

And if we will go here: http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirfaq.mspx and go down to the networking section we will read a great article:

How do I specify a specific network adapter/network just for mirroring?

Use multiple network adapters in the server. For the network adapter that you want to dedicate to mirroring, associate a specific IP address to that adapter. Then, associate a particular name to that IP address. Use that particular name when you set up database mirroring.

I can' believe that nobody didn't get to the point where he will have several NICs on the server? Is it such an exclusive installation?

|||

Thank you for all your help.

Resolved.

|||Is another way to do this just to change the hosts file on the Servers to point to the other network address as opposed changing the name?|||

You can simply bind the mirroring endpoint to listen only on the dedicated NIC's IP. This will apply to outgoing traffic as well as incomming.

alter endpoint [endpointname] as TCP (listener_ip = (<4 part ip of dedicated NIC>));

|||

Mark,

simple 'on surface' type of decision is not always a correct one. What is the sense in certificate based security and ecryption if you are exposing your DB network structure ? Wouldn't it be a security risk?

|||

Sorry, Remus.

I marked your response as an answer. It is incorrect though. (I am novice here and do not know how to reverse it).

There is one endpoint per instance. Then all traffic: 1. Database requests from a client application and 2. mirroring traffic are going through the same endpoint. As such altering endpoint will redirect all the traffic. Question was; how to separate the mirroring traffic through a separate NIC?

And solution is:

ALTER DATABASE [DB] SET PARTNER = 'TCP://<4part.IP.address>:<port_number>'

repeating this statement on both partners (principle and mirror) and then submitting

ALTER DATABASE SET [DB] WITNESS= 'TCP://<4part.IP.address>:<port_number>'

on principle or on mirror.

|||

I though you want to separate mirroring traffic from user connections traffic (TDS). Separting witness traffic from partner traffic is somehow unusual considering that connectivity with the witness is just as important as connectivity with the partner, otherwise why have a witness in the first place?

HTH,
~ Remus

|||

Glen Sidelnikov wrote:

There is one endpoint per instance. Then all traffic: 1. Database requests from a client application and 2. mirroring traffic are going through the same endpoint. As such altering endpoint will redirect all the traffic.

There are many enpoints in an instance: user connection endpoints (shared memory, named pipes, TCP), mirroring endpoint, service broker endpoint, HTTP enpoints. The settings for mirroring endpoint and user endpoints are distinct, therefore you can set the user endpoints (TDS listenning port) on one NIC and the mirroring endpoint on another NIC.

Another way to achieve this is to listen on all NICs, but rely on underlying IP to choose the appropiate NIC. That is, set the partner/witness names to a name that is only reachable through the desired NIC.

HTH,
~ Remus

sql

Mirroring dedicated traffic

We are using the following configuration:

2 stand alone Windows 2003 servers 64 bit.

Each have two Gig Network cards configured with different IP addresses.

Each running multiple instances of SQL Server 64.

I am trying to set up a mirror where mirroring traffic between servers will be dedicated to a secondary IP address on the second NIC.

I am also trying to avoid Windows authentication.

Interesting enough: Security Configuration screen suggesting you to use fully qualified TCP addresses and, at the same time, does not give you such an option...

Would someone please point me in the right direction?

Thank you.

Use certificate authentication. Look up "database mirroring [SQL Server], certificates" in Books Online.|||

Thank you very much for your response.

That is definetely a lot of information.

And if we will go here: http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirfaq.mspx and go down to the networking section we will read a great article:

How do I specify a specific network adapter/network just for mirroring?

I can' believe that nobody didn't get to the point where he will have several NICs on the server? Is it such an exclusive installation?

|||

Thank you for all your help.

Resolved.

|||Is another way to do this just to change the hosts file on the Servers to point to the other network address as opposed changing the name?|||

You can simply bind the mirroring endpoint to listen only on the dedicated NIC's IP. This will apply to outgoing traffic as well as incomming.

alter endpoint [endpointname] as TCP (listener_ip = (<4 part ip of dedicated NIC>));

|||

Mark,

|||

Sorry, Remus.

I marked your response as an answer. It is incorrect though. (I am novice here and do not know how to reverse it).

And solution is:

ALTER DATABASE [DB] SET PARTNER = 'TCP://<4part.IP.address>:<port_number>'

repeating this statement on both partners (principle and mirror) and then submitting

ALTER DATABASE SET [DB] WITNESS= 'TCP://<4part.IP.address>:<port_number>'

on principle or on mirror.

|||

HTH,
~ Remus

|||

Glen Sidelnikov wrote:

There is one endpoint per instance. Then all traffic: 1. Database requests from a client application and 2. mirroring traffic are going through the same endpoint. As such altering endpoint will redirect all the traffic.

HTH,
~ Remus

Friday, March 9, 2012

Migration to sql server on network for access database

hi,

I migrated the Northwind database using SQL Migration Assistant for Access. it worked fine and I was able to link the access forms to Local machine SQL Server 2005 and it worked fine.

Now i want to link the same Northwind access database to Another Server on the network.

When i try to run the wizard again, it won't migrate the user objects like tables to server, it will just create a database and that is it.

it will give me this error too.

No User objects were selected.

any idea,

Thanks,

Fahim

Since you have already migrated to your local SQL Server, you could use the Import/Export Wizard to move the tables to a different SQL Server.

In the Object Explorer, right-click on the database, select [Task..], and then click on Export Data...

Monday, February 20, 2012

Migrating to SQL 2003

We are running SQL 7, using Access 2000 as a front end. Our network person
is wanting to migrate to Windows 2003 (we're currently on Windows 2000), and
wants to know if we should migrate to SQL 2003 at the same time. Are there
major changes between SQL 7 and SQL 2003, and how hard of a task would it be
to migrate our single database to a new version of SQL?
Thanks,
NeilHi
You would be better to migrate to SQL 2005! Adding this migration will
increase the number of potential issues, but if you test it thoroughly then
you should be ok.
Check out the SQL 2005 Upgrade Advisor
http://www.microsoft.com/downloads/details.aspx?familyid=6107C27A-662C-4319-AFE7-B52D1568C30A&displaylang=en
and upgrade handbook
http://www.microsoft.com/technet/prodtechnol/sql/2005/sqlupgrd.mspx
John
"Neil" wrote:
> We are running SQL 7, using Access 2000 as a front end. Our network person
> is wanting to migrate to Windows 2003 (we're currently on Windows 2000), and
> wants to know if we should migrate to SQL 2003 at the same time. Are there
> major changes between SQL 7 and SQL 2003, and how hard of a task would it be
> to migrate our single database to a new version of SQL?
> Thanks,
> Neil
>
>|||Ummm, do you want to migrate to SQL 2000 or SQL 2005?
MC
"Neil" <nospam@.nospam.net> wrote in message
news:Y_anf.2725$QQ1.23@.newsread3.news.pas.earthlink.net...
> We are running SQL 7, using Access 2000 as a front end. Our network person
> is wanting to migrate to Windows 2003 (we're currently on Windows 2000),
> and wants to know if we should migrate to SQL 2003 at the same time. Are
> there major changes between SQL 7 and SQL 2003, and how hard of a task
> would it be to migrate our single database to a new version of SQL?
> Thanks,
> Neil
>|||Neil (nospam@.nospam.net) writes:
> We are running SQL 7, using Access 2000 as a front end. Our network
> person is wanting to migrate to Windows 2003 (we're currently on Windows
> 2000), and wants to know if we should migrate to SQL 2003 at the same
> time. Are there major changes between SQL 7 and SQL 2003, and how hard
> of a task would it be to migrate our single database to a new version of
> SQL?
There is no SQL 2003. There is SQL 2000 which is very mature now, and
there is SQL 2005 which was released just over a month ago.
While there are vast improvements in SQL 2005 over SQL 2000, migrating
your database to SQL 2005 can very well be a simple task, although there
are some incompatibilities, particularly if you head for compatibility
level 90 (which I recommend).
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||Hi, Erland. Thanks for your reply. Given the incompatibilities between SQL
2005 and SQL 97, do you think it would be wise to just upgrade to SQL 2000?
Also, any ideas about which one might be better suited for Windows 2003
and/or an ODBC environment (using Access MDB as a front end)? Thanks!
Neil
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns972AE1D20309BYazorman@.127.0.0.1...
> Neil (nospam@.nospam.net) writes:
>> We are running SQL 7, using Access 2000 as a front end. Our network
>> person is wanting to migrate to Windows 2003 (we're currently on Windows
>> 2000), and wants to know if we should migrate to SQL 2003 at the same
>> time. Are there major changes between SQL 7 and SQL 2003, and how hard
>> of a task would it be to migrate our single database to a new version of
>> SQL?
> There is no SQL 2003. There is SQL 2000 which is very mature now, and
> there is SQL 2005 which was released just over a month ago.
> While there are vast improvements in SQL 2005 over SQL 2000, migrating
> your database to SQL 2005 can very well be a simple task, although there
> are some incompatibilities, particularly if you head for compatibility
> level 90 (which I recommend).
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||Neil (nospam@.nospam.net) writes:
> Hi, Erland. Thanks for your reply. Given the incompatibilities between
> SQL 2005 and SQL 97, do you think it would be wise to just upgrade to
> SQL 2000? Also, any ideas about which one might be better suited for
> Windows 2003 and/or an ODBC environment (using Access MDB as a front
> end)? Thanks!
SQL 97? Do you mean Access 97?
I can't give any advice that involves Access as this is an unknown product
to me.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||On Thu, 26 Jan 2006 05:02:36 GMT, Neil wrote:
>Hi, Erland. Thanks for your reply. Given the incompatibilities between SQL
>2005 and SQL 97, do you think it would be wise to just upgrade to SQL 2000?
>Also, any ideas about which one might be better suited for Windows 2003
>and/or an ODBC environment (using Access MDB as a front end)? Thanks!
>Neil
Hi Neil,
If you're upgrading anyway (whether from SQL 7 or fram Access 97, I
don't know), then I'd recommend you to go for SQL 2005. Every upgrade
you ever do will require you to deal with SOME incompatibilities. But
from SQL 7 to SQL 2005 should be relatively easy. (From Access [any
version] to SQL Server [any version] might be more trouble).
The payoff will be bigger if you go straight for 2005 - especially when
you start using the new features. Plus you'll be settled for some time
to come!
--
Hugo Kornelis, SQL Server MVP|||I meant SQL 7.
I'm mainly looking for advice re. compatibility with Windows 2003, and
possibly ODBC environments.
Thanks.
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9757815ED3247Yazorman@.127.0.0.1...
> Neil (nospam@.nospam.net) writes:
>> Hi, Erland. Thanks for your reply. Given the incompatibilities between
>> SQL 2005 and SQL 97, do you think it would be wise to just upgrade to
>> SQL 2000? Also, any ideas about which one might be better suited for
>> Windows 2003 and/or an ODBC environment (using Access MDB as a front
>> end)? Thanks!
> SQL 97? Do you mean Access 97?
> I can't give any advice that involves Access as this is an unknown product
> to me.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||Hi, Hugo. Thanks for the reply. See below.
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info> wrote in message
news:1kcit1hfo208tb1pr1quci8o43ob8g1d1s@.4ax.com...
> On Thu, 26 Jan 2006 05:02:36 GMT, Neil wrote:
>>Hi, Erland. Thanks for your reply. Given the incompatibilities between SQL
>>2005 and SQL 97, do you think it would be wise to just upgrade to SQL
>>2000?
>>Also, any ideas about which one might be better suited for Windows 2003
>>and/or an ODBC environment (using Access MDB as a front end)? Thanks!
>>Neil
> Hi Neil,
> If you're upgrading anyway (whether from SQL 7 or fram Access 97, I
> don't know),
It's from SQL 7. We are currently using Access with a SQL 7 back end.
> then I'd recommend you to go for SQL 2005. Every upgrade
> you ever do will require you to deal with SOME incompatibilities. But
> from SQL 7 to SQL 2005 should be relatively easy.
Erland mentioned "compatibility level 90" as possibly entailing some
incompatibilities. Would you concur?
(From Access [any
> version] to SQL Server [any version] might be more trouble).
> The payoff will be bigger if you go straight for 2005 - especially when
> you start using the new features. Plus you'll be settled for some time
> to come!
I certainly agree with that. The only issue would be if there were a main
difference between upgrading to 2000 vs. 2005. But if upgrading to 2000 or
2005 were more or less the same, then I'd agree that 2005 would be the way
to go.
Thanks!
Neil
> --
> Hugo Kornelis, SQL Server MVP|||On Fri, 27 Jan 2006 08:27:25 GMT, Neil wrote:
>> then I'd recommend you to go for SQL 2005. Every upgrade
>> you ever do will require you to deal with SOME incompatibilities. But
>> from SQL 7 to SQL 2005 should be relatively easy.
>Erland mentioned "compatibility level 90" as possibly entailing some
>incompatibilities. Would you concur?
Hi Neil,
Compatibility level 90 means that you unlock all the new features that
were implemented in SQL Server 2005. Upgrading to SQL Server 2005
without striving for compatibility level 90 is, IMHO, a bit silly.
Lower compatibility levels are included to make upgrading easier. At
compatibility level 70, many features still behave the same as in SQL
Server 7.0. Not all, though - some changes to the core of the DB engine
were just too drastic to allow mimicking the "old" behaviour.
Upgrading from SQL Server 7.0 to SQL Server 2005 at compatibility level
70 means that you have to make only minimal changes - you don't have to
change the features that are still supported in the compatibility level.
But this should only be a first step - the next step should be to
replace old functionality with new functionality, then set the
compatbility level to 90. And after that, you can begin to unleash the
true power of all the new features!
--
Hugo Kornelis, SQL Server MVP|||Neil (nospam@.nospam.net) writes:
> Erland mentioned "compatibility level 90" as possibly entailing some
> incompatibilities. Would you concur?
You can choose compatibiliy level 70. There still are cases of what
is is illegal syntax bot not caught by SQL 2000. (Don't know if SQL 7
has the same issues.) But with compatibility level 70, some functionalit
will not be available to you.
Beside mew reserved keywords, the main issue is the old-style outer
join (*= and =*) that are not permitted in level 90. If you have this,
rewrite it, because this is crap.
Note that you could run into issues if you upgrade to SQL 2000 as
well, althogh I think difference between level 70 and 80 is just
three keywords.
I've seen some mentions that Access 2003 is not co-operative with SQL2005,
but you probably get better answer on that in an Access newsgroup.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||Sounds like a plan. Thanks.
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info> wrote in message
news:ct7lt1l2q78ulrbj4jm7p7se9spq00c62u@.4ax.com...
> On Fri, 27 Jan 2006 08:27:25 GMT, Neil wrote:
>> then I'd recommend you to go for SQL 2005. Every upgrade
>> you ever do will require you to deal with SOME incompatibilities. But
>> from SQL 7 to SQL 2005 should be relatively easy.
>>Erland mentioned "compatibility level 90" as possibly entailing some
>>incompatibilities. Would you concur?
> Hi Neil,
> Compatibility level 90 means that you unlock all the new features that
> were implemented in SQL Server 2005. Upgrading to SQL Server 2005
> without striving for compatibility level 90 is, IMHO, a bit silly.
> Lower compatibility levels are included to make upgrading easier. At
> compatibility level 70, many features still behave the same as in SQL
> Server 7.0. Not all, though - some changes to the core of the DB engine
> were just too drastic to allow mimicking the "old" behaviour.
> Upgrading from SQL Server 7.0 to SQL Server 2005 at compatibility level
> 70 means that you have to make only minimal changes - you don't have to
> change the features that are still supported in the compatibility level.
> But this should only be a first step - the next step should be to
> replace old functionality with new functionality, then set the
> compatbility level to 90. And after that, you can begin to unleash the
> true power of all the new features!
> --
> Hugo Kornelis, SQL Server MVP|||Thanks, and thanks for the note about Access 2003 and SQL 2005.
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9758F0BBCCE23Yazorman@.127.0.0.1...
> Neil (nospam@.nospam.net) writes:
>> Erland mentioned "compatibility level 90" as possibly entailing some
>> incompatibilities. Would you concur?
> You can choose compatibiliy level 70. There still are cases of what
> is is illegal syntax bot not caught by SQL 2000. (Don't know if SQL 7
> has the same issues.) But with compatibility level 70, some functionalit
> will not be available to you.
> Beside mew reserved keywords, the main issue is the old-style outer
> join (*= and =*) that are not permitted in level 90. If you have this,
> rewrite it, because this is crap.
> Note that you could run into issues if you upgrade to SQL 2000 as
> well, althogh I think difference between level 70 and 80 is just
> three keywords.
>
> I've seen some mentions that Access 2003 is not co-operative with SQL2005,
> but you probably get better answer on that in an Access newsgroup.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Friday, March 30, 2012

Mirroring dedicated traffic

Mirroring dedicated traffic

Friday, March 9, 2012

Migration to sql server on network for access database

Monday, February 20, 2012

Migrating to SQL 2003

Migrating to SQL 2003

Migrating to SQL 2003

Migrating to SQL 2003

Migrating to SQL 2005

Blog Archive

About Me