Category: SQL-Tips and Tricks

SQL Server – Single Instance vs. Multiple Instances

SQL, SQL-Advanced, SQL-General, SQL-Tips and Tricks

Single Instance Pros Only one instance needs to be administered on the single machine. There is no duplication of components or processing overhead, such as having to run multiple database engines on the same computer. This means that the overall performance of a server with a single instance may be higher than a server running multiple instances. A single instance of SQL Server is capable of handling the processing growth requirements of the largest Web sites and enterprise data-processing systems,

» Read more
, ,

Unable to connect the SQL Server Instance – 26 – Error Locating Server/Instance Specified

SQL, SQL-Advanced, SQL-Tips and Tricks, Varinder Sandhu

Problem\Issue: User is not able to connect the SQL Server Named Instance ServerName\InstanceName Additional Error Messages: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (Provider: SQL Network Interfaces, error: 26 – Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1) Environment: SQL Server default single instance is running on

» Read more
, ,

SQL Server – How to read the SQL Server Error log files using TSQL

SQL, SQL-Advanced, SQL-Query, SQL-Scripts, SQL-Tips and Tricks, Varinder Sandhu

There is undocumented system stored procedure sp_readerrorlog which allows us to read the SQL Server error log files directly using T-SQL. This procedure has total 7 parameters as given below: Parameter Values First Parameter Value of error log file you want to read: 0 = current, 1 = Archive #1, 2 = Archive #2, etc. Second Parameter Log file type : 1 – Reads SQL Server error logs, 2 – Reads SQL Server Agent error logs Third Parameter Search string

» Read more
, , ,

SQL Server – Database mirroring vs Log Shipping

SQL, SQL-Advanced, SQL-Tips and Tricks, Varinder Sandhu

I would like to start this topic with very common question for database administrators. Question: What is difference between the Database Mirroring and Log Shipping? Which is preferable solution? Database Mirroring: Database mirroring is functionality in the SQL Server engine that reads from the transaction log and copies transactions from the principal server instance to the mirror server instance.  Database mirroring can operate synchronously or asynchronously. Database mirroring supports only one mirror for each principal database. Database mirroring also supports

» Read more
, ,

SQL Server – Backend version is not supported to design database diagrams or tables. (MS Visual Database Tools)

SQL, SQL-Tips and Tricks, Varinder Sandhu

Error Messages : This backend version is not supported to design database diagrams or tables. (MS Visual Database Tools) Cause  : The database to which you are connected does not allow schema modification with the Visual Database Tools. Resolution : Be sure you are connected to the correct database or Use the correct version SQL Server Management tool.

» Read more
,

SQL Server – Unattended Installation of SQL Server 2012 Using Command Line

SQL, SQL-Advanced, SQL-Server 2012, SQL-Tips and Tricks, Varinder Sandhu

Many times we face a requirement to perform the unattended installation of SQL Server. In this post we will see the steps to perform the unattended installation of SQL Server 2012 using command line. There are two ways to install the unattended installation of SQL Server 2012 using command line. Specify each required parameter , you may take the parameter help Setup.exe /ACTION=Install /FEATURES=SQL /InstanceID=… /IACCEPTSQLSERVERLICENSETERMS /INDICATEPROGRESS Specify Configuration file that contains all the parameter require for installation : Setup.exe

» Read more
, ,

SQL Server – Understanding of SQL Server Role and Database Role

SQL, SQL-Advanced, SQL-General, SQL-Server 2012, SQL-Tips and Tricks

SQL Server supports the two authentication mode as Windows authentication mode and Mixed authentication mode. Once the user is authenticated, SQL server will allow access to the user based on the permission that user has. All the user permissions are based on Server Role and Database Role. So let try to user understand the meaning of each server role and database role. Server Role: Role Name Permission user can have bulkadmin Can run the BULK INSERT statement. dbcreator Can create,

» Read more
, , ,

SQL Server – Migrate/Transfer Maintenance Plan from one Instance to another

SQL, SQL-SSIS, SQL-Tips and Tricks, Varinder Sandhu

There are very few simple steps to migrate/ transfer the maintenance plan from one SQL Server instance to another. Maintenance Plan is nothing but SSIS package which SQL Server creates and deploys it under MSDB Database. I have created one maintenance plan to for the demonstration; you may migrate/ transfer as many you have with the same steps (as shown in the below image). Now connect to SQL Server Integration Services and Navigate to Stored Packages >>MSDB >> Maintenance Plans

» Read more
, , ,

SQL Server – How Like operator work with datetime

SQL, SQL-General, SQL-Query, SQL-Scripts, SQL-Tips and Tricks, Varinder Sandhu

Today, my one of the friend shared a very interesting fact about like operator. With the help of this post I am sharing with you all. We need to convert it to character data while comparing with the like operator. Let‘s check with example Like operator with datetime Like Operator with datetime using convert function If anybody have any suggestion please share as comment.

» Read more
, ,

SQL Server – Authentication Mode – Windows Mode and Mixed Mode

SQL, SQL-Advanced, SQL-Tips and Tricks, Varinder Sandhu

During the installation of SQL Server you can choose the authentication mode for Database Instance. SQL Server support the two authentication mode as Windows authentication mode: Windows Authentication mode enables Windows Authentication and disables SQL Server Authentication.  Even if you choose Windows Authentication mode during SQL Server installation “sa” user is created but disable for SQL Server Authentication. But later we you can use “sa” user by enabling for SQL Server Authentication .Any window user can configure as system administrator

» Read more
, , , ,

Best way to fix SQL error 8942

Guest Post, SQL, SQL-Advanced, SQL-Tips and Tricks

Structured query language is applied to manage any kind of data extracted from relational database. But during these specific operations several errors can come up into the field. Amongst those, Error 8942 is much common in unexpected occurrence. Though Sql server is much reliable, effective and faster environmental platform, it creates a result like DBCC errors. The main reason is nothing but the overlapping of multiple slots. Here the offsets are greater than previous slots for any exceptional causes. So

» Read more
, ,
1 2 3 … 8