Feb 06 2017

Configure Default full-text language Parameter Server Configuration in SQL Server 2016

Category: AdministrationFatih Acar @ 15:33

You can change default full-text language server configuration option in SQL Server 2016 by using SQL Server Management Studio or Transact-SQL. default full-text language option specifies a default language value for full-text indexes. Linguistic analysis is performed on all data that is full-text indexed and is dependent on the language of the data. The default value of this option is the language of the server. For a localized version of SQL Server, SQL Server Setup sets the default full-text language option to the language of the server if an appropriate match exists. For a non-localized version of SQL Server, the default full-text language option is English.

Change Parameter With SQL Server Management Studio

  1. Open Object Explorer, right-click a server and select Properties.
  2. Click Advanced Step.
  3. Under Miscellaneous, use Default Full Text Language to specify a default language value for full-text indexed columns.

Change Parameter With Transact-SQL

I will change option to 1055 that is Turkish language code. You can see LCID codes of language with below query. You can change with below queries.

select * from sys.fulltext_languages;

USE ;
GO
EXEC sp_configure ‘show advanced options’, 1 ;
GO
RECONFIGURE
GO
EXEC sp_configure ‘default full-text language’, 1055 ;
GO
RECONFIGURE
GO

2,120 total views, 10 views today

Tags: MsSQL Server, SQL Server Administration


Jan 10 2017

How to Create Linked Server From SQL Server To SQL Server Database

Category: AdministrationFatih Acar @ 15:58

You can connect another SQL Server database or different type database from SQL Server database with Linked Server feature. You can use below operations to create new linked server for SQL Server database to SQL Server database.

Create Linked Server Steps

  1. Create a user on remote database to use connection and give permission on which database using.
  2. SQL Server Management Studio with sysadmin privilege.
  3. Open SQL Server Management Studio with sysadmin privilege.
  4. Click New Linked Server below Server Objects > Linked Servers
  5. General Section > Select SQL Server as Server type.
  6. Write remote Instance Name to Linked Server text box.
  7. Security Section > Add > write Local and Remote User information for connection, and OK.
  8. You can see added new Linked Server below Linked Server section

1,525 total views, no views today

Tags: Database Administration, MsSQL Server, SQL Server Administration


Jan 05 2017

How to Find Required Missing Indexes For Performance Tuning in SQL Server Database

Category: Administration,SQLFatih Acar @ 08:44

Indexes play vital role for performance tuning on database systems. When you adjust correct indexes on tables, queries can run faster. Otherwise, a proper index can improve the performance and a bad index can hamper the performance. You can find missing indexes with below query. But, result of the query do not mean that you have to create indexes which are all results of query. You have to think which index will be helpful. If you create a lot index on the same table, your database size will increase and DML operation speed will be able to decrease.

Find Missing Indexes Query



SELECT TOP 25
dm_mid.database_id AS DatabaseID,
dm_migs.avg_user_impact*(dm_migs.user_seeks+dm_migs.user_scans) Avg_Estimated_Impact,
dm_migs.last_user_seek AS Last_User_Seek,
OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) AS [TableName],
'CREATE INDEX [INX_' + OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) + '_'
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.equality_columns,''),', ','_'),'[',''),']','') 
+ CASE
WHEN dm_mid.equality_columns IS NOT NULL
AND dm_mid.inequality_columns IS NOT NULL THEN '_'
ELSE ''
END
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.inequality_columns,''),', ','_'),'[',''),']','')
+ ']'
+ ' ON ' + dm_mid.statement
+ ' (' + ISNULL (dm_mid.equality_columns,'')
+ CASE WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns 
IS NOT NULL THEN ',' ELSE
'' END
+ ISNULL (dm_mid.inequality_columns, '')
+ ')'
+ ISNULL (' INCLUDE (' + dm_mid.included_columns + ')', '') AS Create_Statement
FROM sys.dm_db_missing_index_groups dm_mig
INNER JOIN sys.dm_db_missing_index_group_stats dm_migs
ON dm_migs.group_handle = dm_mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details dm_mid
ON dm_mig.index_handle = dm_mid.index_handle
WHERE dm_mid.database_ID = DB_ID()
ORDER BY Avg_Estimated_Impact DESC
GO

Query Result Example

Action For Performance Increase

You can use create scripts which are query result to create indexes. Avg_Estimated_Impact column give index average impact and according to this value you can think which index is important. You do not forget that if you create a lot index on the same table, this increase size of database and decrease DML operation performance. You should create index which has only high Avg_Estimated_Impact value.

664 total views, 2 views today

Tags: MsSQL Server, SQL Server Performance Tuning


Oct 21 2016

MsSQL Server 2012 Availability Groups (AG) Manual Failover

Category: AdministrationFatih Acar @ 09:57

If primary side of Availability Groups (AG) is down and can not be online, you can perform failover operation. When primary side is down, probably, you can see state of Availability Groups at the secondary side like below.

sqlserverfailoversteps1

Failover Steps

sqlserverfailoversteps2

sqlserverfailoversteps3

If you get an error after click Failover button, probably your AG has a problem. You can use force manual failover steps. Steps is here : http://www.fatihacar.com/blog/mssql-server-2012-availability-groups-ag-force-manual-failover/

sqlserverfailoversteps4
Continue reading “MsSQL Server 2012 Availability Groups (AG) Manual Failover”

2,246 total views, 2 views today

Tags: MsSQL Server, SQL Server Administration, SQL Server Availability Group


Oct 20 2016

MsSQL Server 2012 Availability Groups (AG) Force Manual Failover

Category: AdministrationFatih Acar @ 08:15

When you can not access primary side of AG and you can not run failover on second side (disaster side), you can perform force manual failover operation on second side. You have to know that force manual failover operation can cause loss data which is able to last some database operation.

Failover State

When primary side is down, you can see your Availability Groups and database on SQL Server Management Studio at the secondary (disaster side) like below.

sqlserverforcemanualfailover1

Failover Cluster Manager is down. Probably, you can not see any failover cluster on Failover Cluster Manager.

sqlserverforcemanualfailover2

Force Manual Failover

You can use the forced quorum procedure on a WSFC cluster node to override the safety controls that took the cluster offline. This effectively tells the cluster to suspend the quorum voting checks, and lets you bring the WSFC cluster resources and SQL Server back online on any of the nodes in the cluster.

You have to restart your WSFC (Quorum) with below commands.

Open a command prompt and execute below commands.

net.exe stop clussvc
net.exe start clussvc /forcequorum

Continue reading “MsSQL Server 2012 Availability Groups (AG) Force Manual Failover”

2,606 total views, 4 views today

Tags: MsSQL Server, SQL Server Administration, SQL Server Availability Group


Oct 11 2016

SQL Server 2012 AlwaysOn Availability Groups Insallation and Configuration

Category: AdministrationFatih Acar @ 09:00

Always On Availability Group is a new feature in SQL Server Database with 2012 release. Before this feature, we was using database mirroring feature in SQL Server 2005. But, mirroring has some limitations and weaknesses. and mirroring feature will be disabled near future.

AlwaysOn Availability Groups Features

  • Multi or Single Database Failovers
  • Multiple Secondaries
  • Active (Readably) Secondaries
  • Integrated HA Management

sqlserveravailabilitygroup

System Information

OS: Windows 2012 Server
DB: SQL Server 2012
Side 1: TESTDB1
Side 2: TESTDB2
Failover Cluster Name: TESTDB
Availability Group Name: TESTDB_AG
Listener Name: TESTDBVIP
Listener Port: 1433

AlwaysOn Availability Groups Installation Steps
Continue reading “SQL Server 2012 AlwaysOn Availability Groups Insallation and Configuration”

2,631 total views, 8 views today

Tags: Database Administration, MsSQL Server, SQL Server Administration, SQL Server Availability Group


Jan 20 2016

MsSQL Server Unknown Server Hostname Problem Solution

Category: Administration,Errors and SolutionsFatih Acar @ 12:25

When you try to connect MsSQL Server database, you can get an error that is Unknown Server Hostname or similar sometimes. You can resolve this problem with a few operation.

Suggest

If you use SERVERNAME\INSTANCENAME statement, you can try SERVERNAME;instance=INSTANCENAME statement

4,687 total views, no views today

Tags: Database Administration, MsSQL Server, MsSQL Server Error and Solutions


Jul 01 2015

How to Move Datafiles to New Disk in MsSQL Server 2008

Category: AdministrationFatih Acar @ 15:38

If database data disk space is full or you have another problem about of disk, you can move datafiles to new disk in MsSQL Server 2008 database.

Show Current Paths and Names

use master
go
select name,physical_name from sys.master.files where database_id = DB_ID(‘DATABASENAME’)
dbf1 D:\OLDLOCATION\disk_1.mdf
dbf1_log D:\OLDLOCATION\disk1_log.ldf
go

Shutdown Database

use master
go
alter database DATABASENAME modify file(dbf1,filename=’E:\NEWLOCATIOB\disk_1.mdf’);
go
alter database DATABASENAME modify file(dbf1_log,filename=’E:\NEWLOCATIOB\disk1_log.ldf’);

Move Datafiles to New Disk
Continue reading “How to Move Datafiles to New Disk in MsSQL Server 2008”

6,495 total views, 10 views today

Tags: Database Administration, MsSQL Server


Jun 05 2014

How to Enable Agent XPs in MsSQL Server 2008 (Agent Xps Disabled)

Category: Administration,Errors and SolutionsFatih Acar @ 09:28

I upgraded MsSQL database version from MsSQL Server 2008 Express Edition to MsSQL Server 2008 Enterprise Edition. After upgrade operation, Agent XPs was disabled and MsSQL Server Agent servis could not start. I reconfigured Agent XPs configuration. The problem resolved.

Management Studio Object Explorer does not display the contents of the SQL Server Agent node unless these extended stored procedures are enabled regardless of the SQL Server Agent service state.

Agent XPs Possible Values

0, indicating that SQL Server Agent extended stored procedures are not available (the default).
1, indicating that SQL Server Agent extended stored procedures are available.

Show Current Value

EXEC SP_CONFIGURE ‘Agent XPs’

Reconfigure Agent XPs

EXEC SP_CONFIGURE ‘show advanced options’, 1;
GO
RECONFIGURE;
GO
SP_CONFIGURE ‘Agent XPs’, 1;
GO
RECONFIGURE
GO

After these parameter change operations you can restart agent service or sql server service to show Agent XPs enabled.

After all operations, If agent service does not start, you can try that Agent service on services.msc user change to local. That can resolve problem.

Continue reading “How to Enable Agent XPs in MsSQL Server 2008 (Agent Xps Disabled)”

189,846 total views, 15 views today

Tags: Agent XPs Disabled, Database Administration, MsSQL Server, SQL Server Administration


Feb 25 2014

Error : Cannot Open User Default Database in MsSQL Server

Category: Administration,Errors and SolutionsFatih Acar @ 17:21

If you delete default database of user, users of default database can not connect to database. You have to connect with sqlcmd on master database. After you have to change default database of user.

Error

Cannot open user default database. Login failed.

Solution

Open CMD prompt
C:\>sqlcmd -E -d master
1> alter login [domain\LoginUser] with default_database = master
2> go
3> quit

15,070 total views, 5 views today

Tags: Database Administration, MsSQL Server, SQL Server Administration


Next Page »