Hi
I am in the process of setting up replication between two SQL boxes - the first being the production SQL Server and the second being a secondary server which is to be used as part of a web portal solution. (In fact there are two pairs of SQL boxes - SQL 7 and SQL 2k, one of each on the LAN and one of each at the hosting site.)
We need to have near realtime replication, so transactional replication is the desired mechanism. The trouble is that not all tables have primary keys defined. Oh yes, and the main database is 20G in size and has >900 user tables! :-0 (The other databases are much better behaved!)
When I set up transactional replication, I am allowed only to include tables which have PKs defined. AFAIK, the initial transactional replication snapshot will also only include these same tables.
If I set up snapshot replication (separately), I can include all the tables in the database. However, I cannot then replicate in real time.
Can I combine the two replication schemes to deliver updates to the same target database:
- transactional replication delivering realtime updates to the tables with PKs during the day and
- snapshot replication updating all the tables once per 24hrs at night?
Or is there a better way of doing this?
I am not sure whether I can modify the existing schemas, as some of the databases are 'maintained' by an external provider. Even if I could, if I had to add a column to have a PK, I would potentially be adding to my diskspace requirement rather significantly...
TIACertainly, you can setup two publication - one for those tables with no PK and one for those tables with PK. They will have the same subscriber and destination but the one will be snapshot and one will be transactional. The other option you have is to create a surrogate key for each table without PK. I don't know how feasible it is since your database schema is controlled by the person other than you. You can also consider Log Shipping if the function of the subscriber is read-only.|||Thank you for your quick response. I'll set up two publications as you suggest. For some reason I was hung up on including the PK tables in both publications and I was wondering why I was getting errors... <doh!>
Regarding log shipping, I've had a quick look around for info. It seems that it is not as easy as "standard" replication to set up and keep tabs on. Also, in my environment, although the replicated database will be read only on the target, one of the target SQL boxes will have a database which will capture input from web-connected users. Will this make a difference? Do you think that log shipping will be the better solution for me?
I appreciate your help
Showing posts with label setting. Show all posts
Showing posts with label setting. Show all posts
Thursday, March 29, 2012
Sunday, March 25, 2012
Combining full text search results with index server/service
I have a solutions database that I'm setting up Full text search on. Part of
the "solutions" is a huge folder of attachments on the lan.
I've done both FTS alone and also Index server alone.
Are there any whitepapers or good websites that talk about combining the
two? I'd like to conduct the searches via sql server - ideally expanding the
full text index to include the content of the files on the lan.
- Jack
Please refer to the above post.
There is no white paper per se focusing on this. However you might want to
check out this paper which does touch on it.
http://msdn.microsoft.com/library/de...filedatats.asp
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"jack" <jack@.discussions.microsoft.com> wrote in message
news:10938D81-5E92-483F-ABC1-208DDF331112@.microsoft.com...
> I have a solutions database that I'm setting up Full text search on. Part
of
> the "solutions" is a huge folder of attachments on the lan.
> I've done both FTS alone and also Index server alone.
> Are there any whitepapers or good websites that talk about combining the
> two? I'd like to conduct the searches via sql server - ideally expanding
the
> full text index to include the content of the files on the lan.
> - Jack
the "solutions" is a huge folder of attachments on the lan.
I've done both FTS alone and also Index server alone.
Are there any whitepapers or good websites that talk about combining the
two? I'd like to conduct the searches via sql server - ideally expanding the
full text index to include the content of the files on the lan.
- Jack
Please refer to the above post.
There is no white paper per se focusing on this. However you might want to
check out this paper which does touch on it.
http://msdn.microsoft.com/library/de...filedatats.asp
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"jack" <jack@.discussions.microsoft.com> wrote in message
news:10938D81-5E92-483F-ABC1-208DDF331112@.microsoft.com...
> I have a solutions database that I'm setting up Full text search on. Part
of
> the "solutions" is a huge folder of attachments on the lan.
> I've done both FTS alone and also Index server alone.
> Are there any whitepapers or good websites that talk about combining the
> two? I'd like to conduct the searches via sql server - ideally expanding
the
> full text index to include the content of the files on the lan.
> - Jack
Sunday, February 12, 2012
Collations ... blah!
What collation setting(s) do I need to set with RebuildM to get the
SQL_Latin1_General_CP1_CI_AS collation?
3rd time going and I can't seem to set/select the right configuration.
Thanks,
MorganFound it...
Dictionary Order, case insensitive for use with 1252 Char Set.
Thanks,
Morgan
"Morgan" <mfears@.spamcop.net> wrote in message
news:ueb0z8jxDHA.2328@.TK2MSFTNGP10.phx.gbl...
> What collation setting(s) do I need to set with RebuildM to get the
> SQL_Latin1_General_CP1_CI_AS collation?
> 3rd time going and I can't seem to set/select the right configuration.
>
> Thanks,
> Morgan
>
SQL_Latin1_General_CP1_CI_AS collation?
3rd time going and I can't seem to set/select the right configuration.
Thanks,
MorganFound it...
Dictionary Order, case insensitive for use with 1252 Char Set.
Thanks,
Morgan
"Morgan" <mfears@.spamcop.net> wrote in message
news:ueb0z8jxDHA.2328@.TK2MSFTNGP10.phx.gbl...
> What collation setting(s) do I need to set with RebuildM to get the
> SQL_Latin1_General_CP1_CI_AS collation?
> 3rd time going and I can't seem to set/select the right configuration.
>
> Thanks,
> Morgan
>
Friday, February 10, 2012
Collation setting
Hi
We have a sqlserver 2005 DB which is mainly used by sharepoint. We are
using "Latin1_General_CI_AS_KS_WS" collation for SharePoint Portal Server
2003 databases. When I have installed the server I have installed
"Latin1_General_CI_AS
" collation which is the default collation setting for canada.
So now we have different collation settings for the the system databases and
sharepoint databases. Which collation setting should I keep?
Thanks
--
ontario, canadaI would go with the default collation (sort id 52) that is
SQL_Latin1_General_CP1_CI_AS. (This depends on what your need)
However, in your current situation it appears that your databases are using
a different collation and the server is installed using a different
collation. you can do one of the following things:
1) Change the collation at the database level to match the collation of the
server.
2) Change the collation at the server level. (recommended)
Take a look at this tip on how to change the collation at the server level:
http://www.sqlcommunity.com/SQLTips/tabid/77/grm2id/21/Default.aspx
--
Thank you,
Saleem Hakani
HTTP://WWW.SQLCOMMUNITY.COM (World Wide SQL Server Community)
SQLTips, Scripts, Discussions, Blogs, Articles, Radio and a lot of SQL
Server Fun.
"db" wrote:
> Hi
> We have a sqlserver 2005 DB which is mainly used by sharepoint. We are
> using "Latin1_General_CI_AS_KS_WS" collation for SharePoint Portal Server
> 2003 databases. When I have installed the server I have installed
> "Latin1_General_CI_AS
> " collation which is the default collation setting for canada.
> So now we have different collation settings for the the system databases and
> sharepoint databases. Which collation setting should I keep?
> Thanks
> --
> ontario, canada|||On Oct 4, 5:40 pm, db <d...@.discussions.microsoft.com> wrote:
> Hi
> We have a sqlserver 2005 DB which is mainly used by sharepoint. We are
> using "Latin1_General_CI_AS_KS_WS" collation for SharePoint Portal Server
> 2003 databases. When I have installed the server I have installed
> "Latin1_General_CI_AS
> " collation which is the default collation setting for canada.
> So now we have different collation settings for the the system databases and
> sharepoint databases. Which collation setting should I keep?
>
Sharepoint's Collation.
If Sharepoint is the only application that server serves, then it
would be better
you rebuild the server with Sharepoint's collation. If the server
serves other
applications too, then you can maintain the server with multiple
collations,
but this is a risky business.
Regards,
Sasi.
> Thanks
> --
> ontario, canada|||Thanks saleem and sasiraj. What is the difference between
"Latin1_General_CI_AS_KS_WS" and "Latin1_General_CI_AS" collation.
ontario, canada
"sasiraj" wrote:
> On Oct 4, 5:40 pm, db <d...@.discussions.microsoft.com> wrote:
> > Hi
> > We have a sqlserver 2005 DB which is mainly used by sharepoint. We are
> > using "Latin1_General_CI_AS_KS_WS" collation for SharePoint Portal Server
> > 2003 databases. When I have installed the server I have installed
> > "Latin1_General_CI_AS
> > " collation which is the default collation setting for canada.
> >
> > So now we have different collation settings for the the system databases and
> > sharepoint databases. Which collation setting should I keep?
> >
> Sharepoint's Collation.
> If Sharepoint is the only application that server serves, then it
> would be better
> you rebuild the server with Sharepoint's collation. If the server
> serves other
> applications too, then you can maintain the server with multiple
> collations,
> but this is a risky business.
> Regards,
> Sasi.
> > Thanks
> > --
> > ontario, canada
>
>|||db wrote:
> Thanks saleem and sasiraj. What is the difference between
> "Latin1_General_CI_AS_KS_WS" and "Latin1_General_CI_AS" collation.
The difference is unimportant for most parts of the world (except
Asia). Latin1_General_CI_AS_KS_WS is Kana-sensitive and
Width-sensitive. The Latin1_General_CI_AS is much more common, because
it's the default in most installations.
"Kana-sensitive" means that it distinguishes between the two types of
Japanese kana characters: Hiragana and Katakana. If this option is not
selected, SQL Server considers Hiragana and Katakana characters to be
equal for sorting purposes.
"Width-sensitive" means that it distinguishes between a single-byte
character and the same character when represented as a double-byte
character. If this option is not selected, SQL Server considers the
single-byte and double-byte representation of the same character to be
identical for sorting purposes. In this context, double-byte characters
does not reffer to Unicode characters in general. It reffers to
Double-Byte Character Sets, such as Shift-JIS, GB2312, etc.
--
Razvan Socol
SQL Server MVP
We have a sqlserver 2005 DB which is mainly used by sharepoint. We are
using "Latin1_General_CI_AS_KS_WS" collation for SharePoint Portal Server
2003 databases. When I have installed the server I have installed
"Latin1_General_CI_AS
" collation which is the default collation setting for canada.
So now we have different collation settings for the the system databases and
sharepoint databases. Which collation setting should I keep?
Thanks
--
ontario, canadaI would go with the default collation (sort id 52) that is
SQL_Latin1_General_CP1_CI_AS. (This depends on what your need)
However, in your current situation it appears that your databases are using
a different collation and the server is installed using a different
collation. you can do one of the following things:
1) Change the collation at the database level to match the collation of the
server.
2) Change the collation at the server level. (recommended)
Take a look at this tip on how to change the collation at the server level:
http://www.sqlcommunity.com/SQLTips/tabid/77/grm2id/21/Default.aspx
--
Thank you,
Saleem Hakani
HTTP://WWW.SQLCOMMUNITY.COM (World Wide SQL Server Community)
SQLTips, Scripts, Discussions, Blogs, Articles, Radio and a lot of SQL
Server Fun.
"db" wrote:
> Hi
> We have a sqlserver 2005 DB which is mainly used by sharepoint. We are
> using "Latin1_General_CI_AS_KS_WS" collation for SharePoint Portal Server
> 2003 databases. When I have installed the server I have installed
> "Latin1_General_CI_AS
> " collation which is the default collation setting for canada.
> So now we have different collation settings for the the system databases and
> sharepoint databases. Which collation setting should I keep?
> Thanks
> --
> ontario, canada|||On Oct 4, 5:40 pm, db <d...@.discussions.microsoft.com> wrote:
> Hi
> We have a sqlserver 2005 DB which is mainly used by sharepoint. We are
> using "Latin1_General_CI_AS_KS_WS" collation for SharePoint Portal Server
> 2003 databases. When I have installed the server I have installed
> "Latin1_General_CI_AS
> " collation which is the default collation setting for canada.
> So now we have different collation settings for the the system databases and
> sharepoint databases. Which collation setting should I keep?
>
Sharepoint's Collation.
If Sharepoint is the only application that server serves, then it
would be better
you rebuild the server with Sharepoint's collation. If the server
serves other
applications too, then you can maintain the server with multiple
collations,
but this is a risky business.
Regards,
Sasi.
> Thanks
> --
> ontario, canada|||Thanks saleem and sasiraj. What is the difference between
"Latin1_General_CI_AS_KS_WS" and "Latin1_General_CI_AS" collation.
ontario, canada
"sasiraj" wrote:
> On Oct 4, 5:40 pm, db <d...@.discussions.microsoft.com> wrote:
> > Hi
> > We have a sqlserver 2005 DB which is mainly used by sharepoint. We are
> > using "Latin1_General_CI_AS_KS_WS" collation for SharePoint Portal Server
> > 2003 databases. When I have installed the server I have installed
> > "Latin1_General_CI_AS
> > " collation which is the default collation setting for canada.
> >
> > So now we have different collation settings for the the system databases and
> > sharepoint databases. Which collation setting should I keep?
> >
> Sharepoint's Collation.
> If Sharepoint is the only application that server serves, then it
> would be better
> you rebuild the server with Sharepoint's collation. If the server
> serves other
> applications too, then you can maintain the server with multiple
> collations,
> but this is a risky business.
> Regards,
> Sasi.
> > Thanks
> > --
> > ontario, canada
>
>|||db wrote:
> Thanks saleem and sasiraj. What is the difference between
> "Latin1_General_CI_AS_KS_WS" and "Latin1_General_CI_AS" collation.
The difference is unimportant for most parts of the world (except
Asia). Latin1_General_CI_AS_KS_WS is Kana-sensitive and
Width-sensitive. The Latin1_General_CI_AS is much more common, because
it's the default in most installations.
"Kana-sensitive" means that it distinguishes between the two types of
Japanese kana characters: Hiragana and Katakana. If this option is not
selected, SQL Server considers Hiragana and Katakana characters to be
equal for sorting purposes.
"Width-sensitive" means that it distinguishes between a single-byte
character and the same character when represented as a double-byte
character. If this option is not selected, SQL Server considers the
single-byte and double-byte representation of the same character to be
identical for sorting purposes. In this context, double-byte characters
does not reffer to Unicode characters in general. It reffers to
Double-Byte Character Sets, such as Shift-JIS, GB2312, etc.
--
Razvan Socol
SQL Server MVP
Collation Problem on Sql Server 2000
Hi all,
My database's collation setting is Turkish_CI_AS and I need to change as
SQL_Latin_General_CP1_CI_AS
which I use command for this operation as follows
"Alter Database [ORDER] collate SQL_Latin_General_CP1_CI_AS"
but I have error message like that :
Server: Msg 5030, Level 16, State 2, Line 1
The database could not be exclusively locked to perform the operation.
Server: Msg 5072, Level 16, State 1, Line 1
ALTER DATABASE failed. The default collation of database 'ORDER' cannot be
set to SQL_Latin1_General_CP1_CI_AS.
so how can I solve tihs problem and how can I turn my collation setting as
SQL_Latin_General_CP1_CI_AS
thanks for helps.ss
It is not going to be an easy task ,because sql server will throw an error
of you hasve any indexes on those columns , so first thing I'd remove all
indexe and the run
ALTER TABLE ... ALTER COLUMN ... statement
Moreover if you issue ALTER DATABASE ...COLLATION... all objects that have
an 'old' collation will not be affected
"ss" <ss@.ss.com> wrote in message
news:ur$XkyLoGHA.4332@.TK2MSFTNGP03.phx.gbl...
>
> Hi all,
> My database's collation setting is Turkish_CI_AS and I need to change as
> SQL_Latin_General_CP1_CI_AS
> which I use command for this operation as follows
> "Alter Database [ORDER] collate SQL_Latin_General_CP1_CI_AS"
> but I have error message like that :
> Server: Msg 5030, Level 16, State 2, Line 1
> The database could not be exclusively locked to perform the operation.
> Server: Msg 5072, Level 16, State 1, Line 1
> ALTER DATABASE failed. The default collation of database 'ORDER' cannot be
> set to SQL_Latin1_General_CP1_CI_AS.
> so how can I solve tihs problem and how can I turn my collation setting as
> SQL_Latin_General_CP1_CI_AS
> thanks for helps.
>
>
My database's collation setting is Turkish_CI_AS and I need to change as
SQL_Latin_General_CP1_CI_AS
which I use command for this operation as follows
"Alter Database [ORDER] collate SQL_Latin_General_CP1_CI_AS"
but I have error message like that :
Server: Msg 5030, Level 16, State 2, Line 1
The database could not be exclusively locked to perform the operation.
Server: Msg 5072, Level 16, State 1, Line 1
ALTER DATABASE failed. The default collation of database 'ORDER' cannot be
set to SQL_Latin1_General_CP1_CI_AS.
so how can I solve tihs problem and how can I turn my collation setting as
SQL_Latin_General_CP1_CI_AS
thanks for helps.ss
It is not going to be an easy task ,because sql server will throw an error
of you hasve any indexes on those columns , so first thing I'd remove all
indexe and the run
ALTER TABLE ... ALTER COLUMN ... statement
Moreover if you issue ALTER DATABASE ...COLLATION... all objects that have
an 'old' collation will not be affected
"ss" <ss@.ss.com> wrote in message
news:ur$XkyLoGHA.4332@.TK2MSFTNGP03.phx.gbl...
>
> Hi all,
> My database's collation setting is Turkish_CI_AS and I need to change as
> SQL_Latin_General_CP1_CI_AS
> which I use command for this operation as follows
> "Alter Database [ORDER] collate SQL_Latin_General_CP1_CI_AS"
> but I have error message like that :
> Server: Msg 5030, Level 16, State 2, Line 1
> The database could not be exclusively locked to perform the operation.
> Server: Msg 5072, Level 16, State 1, Line 1
> ALTER DATABASE failed. The default collation of database 'ORDER' cannot be
> set to SQL_Latin1_General_CP1_CI_AS.
> so how can I solve tihs problem and how can I turn my collation setting as
> SQL_Latin_General_CP1_CI_AS
> thanks for helps.
>
>
Collation Problem on Sql Server 2000
Hi all,
My database's collation setting is Turkish_CI_AS and I need to change as
SQL_Latin_General_CP1_CI_AS
which I use command for this operation as follows
"Alter Database [ORDER] collate SQL_Latin_General_CP1_CI_AS"
but I have error message like that :
Server: Msg 5030, Level 16, State 2, Line 1
The database could not be exclusively locked to perform the operation.
Server: Msg 5072, Level 16, State 1, Line 1
ALTER DATABASE failed. The default collation of database 'ORDER' cannot be
set to SQL_Latin1_General_CP1_CI_AS.
so how can I solve tihs problem and how can I turn my collation setting as
SQL_Latin_General_CP1_CI_AS
thanks for helps.ss
It is not going to be an easy task ,because sql server will throw an error
of you hasve any indexes on those columns , so first thing I'd remove all
indexe and the run
ALTER TABLE ... ALTER COLUMN ... statement
Moreover if you issue ALTER DATABASE ...COLLATION... all objects that have
an 'old' collation will not be affected
"ss" <ss@.ss.com> wrote in message
news:ur$XkyLoGHA.4332@.TK2MSFTNGP03.phx.gbl...
>
> Hi all,
> My database's collation setting is Turkish_CI_AS and I need to change as
> SQL_Latin_General_CP1_CI_AS
> which I use command for this operation as follows
> "Alter Database [ORDER] collate SQL_Latin_General_CP1_CI_AS"
> but I have error message like that :
> Server: Msg 5030, Level 16, State 2, Line 1
> The database could not be exclusively locked to perform the operation.
> Server: Msg 5072, Level 16, State 1, Line 1
> ALTER DATABASE failed. The default collation of database 'ORDER' cannot be
> set to SQL_Latin1_General_CP1_CI_AS.
> so how can I solve tihs problem and how can I turn my collation setting as
> SQL_Latin_General_CP1_CI_AS
> thanks for helps.
>
>
My database's collation setting is Turkish_CI_AS and I need to change as
SQL_Latin_General_CP1_CI_AS
which I use command for this operation as follows
"Alter Database [ORDER] collate SQL_Latin_General_CP1_CI_AS"
but I have error message like that :
Server: Msg 5030, Level 16, State 2, Line 1
The database could not be exclusively locked to perform the operation.
Server: Msg 5072, Level 16, State 1, Line 1
ALTER DATABASE failed. The default collation of database 'ORDER' cannot be
set to SQL_Latin1_General_CP1_CI_AS.
so how can I solve tihs problem and how can I turn my collation setting as
SQL_Latin_General_CP1_CI_AS
thanks for helps.ss
It is not going to be an easy task ,because sql server will throw an error
of you hasve any indexes on those columns , so first thing I'd remove all
indexe and the run
ALTER TABLE ... ALTER COLUMN ... statement
Moreover if you issue ALTER DATABASE ...COLLATION... all objects that have
an 'old' collation will not be affected
"ss" <ss@.ss.com> wrote in message
news:ur$XkyLoGHA.4332@.TK2MSFTNGP03.phx.gbl...
>
> Hi all,
> My database's collation setting is Turkish_CI_AS and I need to change as
> SQL_Latin_General_CP1_CI_AS
> which I use command for this operation as follows
> "Alter Database [ORDER] collate SQL_Latin_General_CP1_CI_AS"
> but I have error message like that :
> Server: Msg 5030, Level 16, State 2, Line 1
> The database could not be exclusively locked to perform the operation.
> Server: Msg 5072, Level 16, State 1, Line 1
> ALTER DATABASE failed. The default collation of database 'ORDER' cannot be
> set to SQL_Latin1_General_CP1_CI_AS.
> so how can I solve tihs problem and how can I turn my collation setting as
> SQL_Latin_General_CP1_CI_AS
> thanks for helps.
>
>
Subscribe to:
Posts (Atom)