Do we need to choose the Latin1_General_CI_AS_KS_WS collation setting while installation of SQL Server 2005
-
Hi All,
do we need to choose the Latin1_General_CI_AS_KS_WS collation setting while installation of SQL Server 2005, when we are planning to store sharepoint databases in sql server?
We installed sql server with SQL_Latin1_General_CP1_CI_AI.
system databases have the collation settings as SQL_Latin1_General_CP1_CI_AI
user databases have the collation settings as Latin1_General_CI_AS_KS_WS(as sharepoint installation creates the databases with this collation)
In general, people who are going to store sharepoint databases in SQL Server 2005, what collation settings need to choose while installing SQL Server 2005?
I also went through the link http://support.microsoft.com/kb/843578 In this link they saying that:
Note SharePoint Portal Server 2003 and SharePoint Server 2007 will automatically create databases with the correct collation settings. Notice that the collation settings apply to the database level, but not to the SQL Server level
So that means can we choose SQL_Latin1_General_CP1_CI_AI while sql server installation? or do we need to choose Latin1_General_CI_AS_KS_WS?
So my question is should we install SQL Server with collation SQL_Latin1_General_CP1_CI_AI or not? when the share point databases are created with the collation Latin1_General_CI_AS_KS_WS
Advice me exactly which collation settings do I need to select while installing SQL Server 2005?(Considering sharepoint application)
Thank you
-
Hi,
From the link http://technet.microsoft.com/en-us/library/cc263187.aspx#section4
'SQL Server and database collation
The SQL Server collation must be configured for case-insensitive. The SQL Server database collation must be configured for case-insensitive, accent-sensitive, Kana-sensitive, and width-sensitive. This is to ensure file name uniqueness consistent with the Windows operating system. For more information about collations, see "Selecting a SQL Collation" or "Collation Settings in Setup" in SQL Server Books Online'.
So from the Bolded line...If SQL Server collation is case-insensitive then everything will be ok considering sharepoint application .
So we have the SQL Server collation as SQL_Latin1_General_CP1_CI_AS. Here the bolded one CI means case sensitive right? If it is yes, there is no need to rebuild the system databases to make the collation as Latin1_General_CI_AS_KS_WS right?(sharepoint setup creates databases with collation settings Latin1_General_CI_AS_KS_WS )
Even if we have case insensitive collation in SQL_Latin1_General_CP1_CI_AS, do we need to rebuild the system dbs to match Latin1_General_CI_AS_KS_WS ?
If yes could you please tell me the steps to rebuild the system databases in clustered environment
Please advice me.....
-
I was hoping someone will help you but if you use different collation for the server you may need your SharePoint collation in all your code because the SharePoint collation can be used with more languages than your server collation. Check below for more about this issue.
http://justgeeks.blogspot.com/2008/10/error-cannot-resolve-collation-conflict.html
Kind regards,
Gift Peddie -
madhu
what is the geographical location of your sql server?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
-
LA,CA USA
-
Madhu, I have on my SQL Server de Latin1_General_CI_AI collation and it does not give me any problems with the SharePoint databases. So I think you can just keep your system databases with the Latin1_General_CI_AI collation.
-
Thank you Rangel,
So far We did not get any issue with the collation settings but my concern is not to get any problem in future. As you are maintaining the sharepount databases,I have couple of questions here and could you please tell me how you are managing them at your organization:
1.How to create multiple secondary data files and log files for Content database. As there is special stored procedures to create secondary datafiles and move the tables for Search database(from the link http://blogs.msdn.com/enterprisesearch/archive/2008/09/16/sql-file-groups-and-search.aspx, we have two stored procedure named MoveTableToFileGroup.sql and MoveCrawlTablesToFileGroup.sql). So I followed this link created multiple secondary files and moved the data using these stored procedures. Similarly is there any method or procedure to create multiple secondary data n log files for Content Database?
2.How to reorganize/rebuild indexes of sharepoint databases to defragment?
From the link http://support.microsoft.com/kb/943345/, there is stored procedure which reorganize/rebuilds the indexes of Search,Profile and Content databases. So my question is do we need to create this Stored proc in each if these 3 databases? or can we create this Stored procedure in master database and schedule job?(how to do this)
3.These databases indexes are getting fragmented rapidly and I checked the fill factor, it is the default value 100? So can we change the fill factor server level to 70? and is this improves the performance? how actually you are dealing with this?
4.If you are created multiple secondary data n log files for content and search databases, how you are taking backups and restores?
Your suggestions will be great help for me
Thank You
-
Hi,
Could you please anybody working on maintaing share point databases in SQL Server 2005 ,advice me how to maintain the databases considering creating multiple secondary data and log files for content databases
-
I have couple of questions here and could you please tell me how you are managing them at your organization:
1.How to create multiple secondary data files and log files for Content database. As there is special stored procedures to create secondary datafiles and move the tables for Search database(from the link http://blogs.msdn.com/enterprisesearch/archive/2008/09/16/sql-file-groups-and-search.aspx, we have two stored procedure named MoveTableToFileGroup.sql and MoveCrawlTablesToFileGroup.sql). So I followed this link created multiple secondary files and moved the data using these stored procedures. Similarly is there any method or procedure to create multiple secondary data n log files for Content Database?
I don't use multiple data files, but multiple content databases.
2.How to reorganize/rebuild indexes of sharepoint databases to defragment?
From the link http://support.microsoft.com/kb/943345/, there is stored procedure which reorganize/rebuilds the indexes of Search,Profile and Content databases. So my question is do we need to create this Stored proc in each if these 3 databases? or can we create this Stored procedure in master database and schedule job?(how to do this)
I think that you will have to create the store procedure per database, or you will have to change the script. I don't use the procedure. I rebuild the indexes every sunday.
3.These databases indexes are getting fragmented rapidly and I checked the fill factor, it is the default value 100? So can we change the fill factor server level to 70? and is this improves the performance? how actually you are dealing with this?
I use a fill factor of 70. I don't know if 100 is the default factor, but I don't think so. At the moment I don't have performance problems.
4.If you are created multiple secondary data n log files for content and search databases, how you are taking backups and restores?
This question is not applicable to my environment.
If you want to try something, test it first.
Good luck!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply