Dreaded TEXT column

I need to setup repliaction between A and B. I have high transaction /min count. Some tables have TEXT columns. This is what i am planning to do:

Example: table name is CREDITS

vertical partition the table into two. First table (will be called CREDITS_PRI) holds PK column and non TEXT columns, second table (will be called CREDITS_SEC) holds PK column and TEXT columns.

Create a view called CREDITS with INSTEAD OF triggers for inserts, updates and deletes. So far so good. Then setup replication with immediate updating subscription between A and B for tables called ..._PRI and merge replication between A and B for tables called ..._SEC.

Would this work? How do other companies handle this? thank you in advance for pointing me into the right direction.

Lars

Yes, this would work, you could also leave your tables intact the way they were and implement bi-directional transactional replication.

However I assume you're on SQL 2000, correct? If you're on SQL 2005, immediate updating would work just fine if you replaced text datatype with varchar(max) datatype, it's also more optimal and efficient in terms of data storage.

|||

Thank you,

yes, we still are on SQL 2000 and won't upgrade until another 6 months. I have never heard of bi-directional replication since it wasn't mentioned anywhere. I found a great link on MS com and will take a look at it. Thanks again.

L

DR Server using replication

Hi,

I am planning on setting up disaster recovery configuration. It is planned to use transactional replication from the production server to the DR server. Are there any recommendations for replicating changes back to the production server, once it is back online. I suppose that it would be safest to move the database back the other server over a weekend or during downtime. But I am just wondering whether anyone has experience of other practical solutions.

Many thanks
DavidDid you look into log shipping?|||Replication is a poor choice for Disaster Recovery. The cost of maintaining the replication is much higher than using Log Shipping or a home grown solution similar to Log Shipping.

The first questions that need to be answered by the owner of the budget are:
1. How much data can you afford to lose?
2. How much (business hours) downtime can be tolerated?
3. How much money are you willing to spend on Disaster Recovery?

You gotta put numbers on the cost/benefit or you'll be rolling boulders uphill for a long, long time.

Unfortunately, typical, thoughtless, knee-jerk answers are:
1. none
2. 1 minute
3. $9.95

A better approach may be to put together a menu of options to go along w/ the questions above as well as other relevant questions:
A1. Lose no more than 5 minutes of transactions, recover within 20 minutes at a cost of $40K + $10K per subsequent years.
B2. Lose no more than 60 minutes of transactions, recover within 4 hours at a cost of $20K + $5K per subsequent years.
C3. Lose no more than one day of data and recover within 4 hours at a cost of $1K + $1K per subsequent years.

Ninety percent of failures are caused by humans - e.g. accidentally Delete w/o a Where clause. Those are the types of failures from which you need to focus on recovering. For example, your DR/backup server should intentionally be kept out of sync. by however much time is needed to detect and respond to a human error: 4 hours works well.

Finally, unless you are willing to determine every failure permutation and the steps needed to recover from each, the answer to your first question is: stop production, figure out what is missing and copy the data using the most convenient, familiar approach you have.|||Sybase has a replication server product that does exactly what you need. It can replicate many database vendor's data including MSSQL.

http://www.sybase.com/products/informationmanagement/replicationserver|||Thank you for the responses.

Unfortunately the client insists that they cannot afford any downtime, which is why they are going down this route. I had asked them to look at third party tools such as emc, but they decided that this wouldn't be possible for political reasons. Log shipping doesn't suit application either, so we are left with doing transactional rep on all the data.

Thanks|||Why can't companies understand DR is not the same is High Availability? Too bad you can't cluster your production server and use log shipping to a DR solution.

I'm not very familiar with replication but if you need to get changes back to the original production server shouldn't you use merge replication?|||Thank you for the responses.

Unfortunately the client insists that they cannot afford any downtime, which is why they are going down this route. I had asked them to look at third party tools such as emc, but they decided that this wouldn't be possible for political reasons. Log shipping doesn't suit application either, so we are left with doing transactional rep on all the data.

Thanks

Did you look at the Sybase solution? What you are really looking at is Warm Standby, which replicates (transactionally) all of the changes from the "active" connection to the warm standby connection. The repserver is aware which connection is the "active" and which is the "warm standby" at all times. When you switch them, replication flows the other way automatically.

DR Question

We are planning for DR setup. Currently the database is located on shared disk storage. During DR, if we connect the database in the shared disk storage to the DR server. Will we be able to start up the SQL server at DR server? The IP address of the DR server will be different from the production since on different network segment. Is there any other things we need to take care of, such as user logins? Please comment.

If what is on the shared disk is your user(application) database, then you'll need to manually sync up all other data such as logins, stored procedures, maintenance jobs, etc.

They you would need to attach the database to the instance on the new node.

DR planning help....

I was told that my company is thinking about inplementing a "backup" server for out content loading server (aka load server)... this server loads files from several companies and then replicates the data out to our production environment to be seen. This server has serveral DTS/IS packages that do all this data movement and manipulation... I am not too up to date on technology for what they are asking so I thought I would ask here for some recommendations to head in the right direction...

I am not sure if clustering will work or maybe mirroring the database...

The server now is a publisher so the backup server would have to pick up if the #1 failed and then give command back to #1 once it is good... this is the part I have no idea about...

The #2 server would not have to really do anything but keep its data up to date unless #1 fails so you could say it would be fine to be a passive server...

Any ideas on what direction I should look into?

Thanks ahead of time.

It sounds like mirroring may be the best solution for you.

WIth synchronous mode, you can have the failover happen automatically and transparent to the application.

It also gives you a second copy of the data.

|||

Would mirroring handle the publishing also?

This is the biggest issue I have...

|||

Sorry, but I would NOT deploy Database Mirroring for this. It isn't even going to begin to cover your needs.

Clustering would provide hardware protection while at the same time keeping all of your loading and replication processes running.

Database Mirroring would require you to mirror each of your user databases. You would then have to create processes to keep all of your logins, jobs, SSIS/DTS packages, linked servers, etc. synchronized between the two machines. (Database Mirroring would NOT handle any of this stuff.) The replication engine could pretty much seamlessly failover with Database Mirroring, but you would have a LOT of heavy lifting to do to make your standby truly a standby that you could failover to. Just because it is a new technology, everyone wants to use it all over the place.

In your case, clustering would provide a much simpler DR scenario, particularly since the subscribers can continue to run while the publisher is offline. This isn't going to help if you take a hit to the entire data center, so you will want something in addition to clustering to maintain a second copy of your data which could be loaded back to a rebuilt server.

|||

So it sounds like clustering would be the best bet for the DR setup I need.

That was what I was leaning towards initially but I didn't know to much about clustering...

Is there any good documentation/white papers on clustering?

|||There are several docs in Books Online, several webcasts on MSDN, and lots of stuff on various websites, newsgroups, and mailing lists. The biggest item is getting it installed and configured. After that, there is nothing special from a SQL Server side for managing one.|||http://www.sql-server-performance.com/clustering_resources.asp|||Thanks for everything... I will start reading up on it...

Domain Rename - effect on SQL

Hi everyone,
I'm planning an Active Directory Domain Rename shortly - however we have a
number of SQL Servers (2000) on the domain running integrated security.
If I rename the domain, will it cause a problem with Intrgrated security or
not? (the domain is technically the same, but a different name - e.g. users
SID's should be the same I think...!)
Cheers,
SteveHi Steve
I believe that you are correct, as the SIDS are not changing you should be
OK, but just in case you may want to run the script on
http://support.microsoft.com/kb/246133/
John
"SteveHoot" wrote:

> Hi everyone,
> I'm planning an Active Directory Domain Rename shortly - however we have a
> number of SQL Servers (2000) on the domain running integrated security.
> If I rename the domain, will it cause a problem with Intrgrated security o
r
> not? (the domain is technically the same, but a different name - e.g. user
s
> SID's should be the same I think...!)
> Cheers,
>
> Steve|||Thanks for that John - we're a development company, so if things do go
wrong then I'm for the chop. Guess the best thing to do is fire up a test
forest, domain and SQL server and give the rename a go there...!
Anyone got experience in this please let me know!
Thanks again for the help John - appreciated.
Steve.
examnotes <jbellnewsposts@.hotmail.com> wrote in
news:7B62A81D-748F-438F-B00C-B715F0D43F9D@.microsoft.com:
[vbcol=seagreen]
> Hi Steve
> I believe that you are correct, as the SIDS are not changing you
> should be OK, but just in case you may want to run the script on
> http://support.microsoft.com/kb/246133/
> John
> "SteveHoot" wrote:
>|||Hi Steve
I have done migrations but not a rename, if you rename the actual
machine then you will need to drop the server as in
http://msdn.microsoft.com/library/d...nstall_5r8f.asp
Plan your migration and make sure that you have contingencies for
failure at each step, backup the databases, script the jobs, users
etc..
Having a test domain is a very good idea.
John
Steve Hewitt wrote:[vbcol=seagreen]
> Thanks for that John - we're a development company, so if things do go
> wrong then I'm for the chop. Guess the best thing to do is fire up a test
> forest, domain and SQL server and give the rename a go there...!
> Anyone got experience in this please let me know!
> Thanks again for the help John - appreciated.
>
> Steve.
>
> examnotes <jbellnewsposts@.hotmail.com> wrote in
> news:7B62A81D-748F-438F-B00C-B715F0D43F9D@.microsoft.com:
>

Domain Rename - effect on SQL

Hi everyone,
I'm planning an Active Directory Domain Rename shortly - however we have a
number of SQL Servers (2000) on the domain running integrated security.
If I rename the domain, will it cause a problem with Intrgrated security or
not? (the domain is technically the same, but a different name - e.g. users
SID's should be the same I think...!)
Cheers,
SteveHi Steve
I believe that you are correct, as the SIDS are not changing you should be
OK, but just in case you may want to run the script on
http://support.microsoft.com/kb/246133/
John
"SteveHoot" wrote:
> Hi everyone,
> I'm planning an Active Directory Domain Rename shortly - however we have a
> number of SQL Servers (2000) on the domain running integrated security.
> If I rename the domain, will it cause a problem with Intrgrated security or
> not? (the domain is technically the same, but a different name - e.g. users
> SID's should be the same I think...!)
> Cheers,
>
> Steve|||Thanks for that John - we're a development company, so if things do go
wrong then I'm for the chop. Guess the best thing to do is fire up a test
forest, domain and SQL server and give the rename a go there...!
Anyone got experience in this please let me know!
Thanks again for the help John - appreciated.
Steve.
=?Utf-8?B?Sm9obiBCZWxs?= <jbellnewsposts@.hotmail.com> wrote in
news:7B62A81D-748F-438F-B00C-B715F0D43F9D@.microsoft.com:
> Hi Steve
> I believe that you are correct, as the SIDS are not changing you
> should be OK, but just in case you may want to run the script on
> http://support.microsoft.com/kb/246133/
> John
> "SteveHoot" wrote:
>> Hi everyone,
>> I'm planning an Active Directory Domain Rename shortly - however we
>> have a number of SQL Servers (2000) on the domain running integrated
>> security.
>> If I rename the domain, will it cause a problem with Intrgrated
>> security or not? (the domain is technically the same, but a different
>> name - e.g. users SID's should be the same I think...!)
>> Cheers,
>>
>> Steve|||Hi Steve
I have done migrations but not a rename, if you rename the actual
machine then you will need to drop the server as in
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/instsql/in_afterinstall_5r8f.asp
Plan your migration and make sure that you have contingencies for
failure at each step, backup the databases, script the jobs, users
etc..
Having a test domain is a very good idea.
John
Steve Hewitt wrote:
> Thanks for that John - we're a development company, so if things do go
> wrong then I'm for the chop. Guess the best thing to do is fire up a test
> forest, domain and SQL server and give the rename a go there...!
> Anyone got experience in this please let me know!
> Thanks again for the help John - appreciated.
>
> Steve.
>
> =?Utf-8?B?Sm9obiBCZWxs?= <jbellnewsposts@.hotmail.com> wrote in
> news:7B62A81D-748F-438F-B00C-B715F0D43F9D@.microsoft.com:
> > Hi Steve
> >
> > I believe that you are correct, as the SIDS are not changing you
> > should be OK, but just in case you may want to run the script on
> > http://support.microsoft.com/kb/246133/
> >
> > John
> >
> > "SteveHoot" wrote:
> >
> >> Hi everyone,
> >>
> >> I'm planning an Active Directory Domain Rename shortly - however we
> >> have a number of SQL Servers (2000) on the domain running integrated
> >> security.
> >>
> >> If I rename the domain, will it cause a problem with Intrgrated
> >> security or not? (the domain is technically the same, but a different
> >> name - e.g. users SID's should be the same I think...!)
> >>
> >> Cheers,
> >>
> >>
> >> Steve

Domain Groups and Windows Authentication

Hi

We are planning implementation of a currently Sybase db. The users (about 3600) will be i 5 domains and we want single sign-on through trusted connections. We want to use the database roles to define different user access on databases and tables. There will be around 2000 roles. We also want to add the users directly to the database roles without having to grant each user database access.

So I thought that I could add the user groups from all domains and then add each domain user account to specified database roles. Am I right here or what? The Windows authentication will lookup or check the users kerberos ticket during logon process and allow logon.

The documentation here is weak and I assume it's a windows authentication question but wondered if any of you guys had been down the same road.

For creating the groups I have the following options:

Create a domain group and put all the usergroups from the other domains in this group

Add user groups from all other domains directly into the SQL Server.

Any recommendations here?

YOu don′t need to add the single users to the database. If you put the domain groups (which contain the users) to the server and the database principles, SQL Server will take care of doing the authentication.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

HI,

If you are adding your domain groups to your SQL Server and grant them to allow access of DB/objects then Windows Server will take care of logins to SQL Server tooo do not need to do anything separately.