Browsing:

Tag: MS SQL

Linked server setup – Back to basics (error 7399)

I recently received this question:

Why am I getting an error ‘Invalid authorization specification’. I used my SA account, but i keep getting the error that the credentials specified are incorrect.

There is a very simple answer to this question, which I will share with you.

The error you get when the credentials are invalid:LSSLinked server is a connection between two servers in your network. If you want to connect the servers, the specified credentials need to be known on both servers, So, unless you use the same password for sa acounts on all your network server, which is never recommended in the first place! I advise you to create a specific Linked server sql account, for example I created a LS_2014 account on both servers and specified on which databases the Linked server is allowed to read and select data, as you can see in the example below.

ls_account

ls_acc

This keeps your databases secure and easier to manage.

You could also set it to use your current logged in account, but you need enough permissions on all servers and depending on who is using the SQL server, the linked server might not be available.

If you use the account specifically created for the Linked server and test the linked server account, the connection will be successful and you will see the table you granted read permissions on is available in the linked server dropdown.


Create and restore a database with multiple secondary data files

SQL databases are all about performance, if databases grow very large, it might be a good idea to spread your files across multiple disks.

A database can consist of three files, .mdf, .ldf and .ndf, in which .ndf is a user defined secondary data file, these files can be created to split up a fast growing .mdf. For example you create 3 files, Data1.ndf, Data2.ndf, and Data3.ndf spread on three disk drives and assigned to a filegroup e.g.’fg1′. Tables can be created specifically on the filegroup ‘fg1’. The queries for data from the table can be spread across multiple disks; this will improve your database performance.

Here’s a script to create a database with multiple filegroups:

USE [master]
GO

/****** Object:  Database [AdventurePark]    Script Date: 01/01/2015 21:13:57 ******/
IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'AdventurePark')
DROP DATABASE [AdventurePark]
GO

USE [master]
GO

/****** Object:  Database [AdventurePark]    Script Date: 01/01/2015 21:13:57 ******/
CREATE DATABASE [AdventurePark] ON  PRIMARY 
( NAME = N'AdventureParkPrimary', FILENAME = N'D:\DATA\AdventurePark.mdf' , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%), 
 FILEGROUP [Data]  DEFAULT 
( NAME = N'AdventureParkData', FILENAME = N'D:\DATA\AdventurePark_1.ndf' , SIZE = 4896KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%), 
 FILEGROUP [Files2008] 
( NAME = N'AdventureParkFiles2008_1', FILENAME = N'E:\DATA\AdventurePark_2.ndf' , SIZE = 4896KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%), 
( NAME = N'AdventureParkFiles2008_2', FILENAME = N'E:\DATA\AdventurePark_3.ndf' , SIZE = 4896KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%), 
 FILEGROUP [Files2009] 
( NAME = N'AdventureParkFiles2009_1', FILENAME = N'E:\DATA\AdventurePark_4.ndf' , SIZE = 4896KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%), 
( NAME = N'AdventureParkFiles2009_2', FILENAME = N'E:\DATA\AdventurePark_5.ndf' , SIZE = 4896KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%), 
 FILEGROUP [Files2010] 
( NAME = N'AdventureParkFiles2010_1', FILENAME = N'E:\DATA\AdventurePark_6.ndf' , SIZE = 4896KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%), 
( NAME = N'AdventureParkFiles2010_2', FILENAME = N'E:\DATA\AdventurePark_7.ndf' , SIZE = 4896KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%), 
 FILEGROUP [FIles2011] 
( NAME = N'AdventureParkFiles2011_1', FILENAME = N'E:\DATA\AdventurePark_8.ndf' , SIZE = 4896KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%), 
( NAME = N'AdventureParkFiles2011_2', FILENAME = N'E:\DATA\AdventurePark_9.ndf' , SIZE = 4896KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%), 
 FILEGROUP [Files2012] 
( NAME = N'AdventureParkFiles2012_1', FILENAME = N'E:\DATA\AdventurePark_10.ndf' , SIZE = 4896KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%), 
( NAME = N'AdventureParkFiles2012_2', FILENAME = N'E:\DATA\AdventurePark_11.ndf' , SIZE = 4896KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%), 
 FILEGROUP [Files2013] 
( NAME = N'AdventureParkFiles2013_1', FILENAME = N'E:\DATA\AdventurePark_12.ndf' , SIZE = 4896KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%), 
( NAME = N'AdventureParkFiles2013_2', FILENAME = N'E:\DATA\AdventurePark_13.ndf' , SIZE = 4896KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%), 
 FILEGROUP [Files2014] 
( NAME = N'AdventureParkFiles2014_1', FILENAME = N'D:\DATA\AdventurePark_14.ndf' , SIZE = 4896KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%), 
( NAME = N'AdventureParkFiles2014_2', FILENAME = N'D:\DATA\AdventurePark_15.ndf' , SIZE = 4896KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)
 LOG ON 
( NAME = N'AdventurePark_log', FILENAME = N'L:\LOGS\AdventurePark_16.ldf' , SIZE = 4896KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

ALTER DATABASE [AdventurePark] SET COMPATIBILITY_LEVEL = 100
GO

IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [AdventurePark].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO

ALTER DATABASE [AdventurePark] SET ANSI_NULL_DEFAULT OFF 
GO

ALTER DATABASE [AdventurePark] SET ANSI_NULLS OFF 
GO

ALTER DATABASE [AdventurePark] SET ANSI_PADDING OFF 
GO

ALTER DATABASE [AdventurePark] SET ANSI_WARNINGS OFF 
GO

ALTER DATABASE [AdventurePark] SET ARITHABORT OFF 
GO

ALTER DATABASE [AdventurePark] SET AUTO_CLOSE OFF 
GO

ALTER DATABASE [AdventurePark] SET AUTO_CREATE_STATISTICS ON 
GO

ALTER DATABASE [AdventurePark] SET AUTO_SHRINK OFF 
GO

ALTER DATABASE [AdventurePark] SET AUTO_UPDATE_STATISTICS ON 
GO

ALTER DATABASE [AdventurePark] SET CURSOR_CLOSE_ON_COMMIT OFF 
GO

ALTER DATABASE [AdventurePark] SET CURSOR_DEFAULT  GLOBAL 
GO

ALTER DATABASE [AdventurePark] SET CONCAT_NULL_YIELDS_NULL OFF 
GO

ALTER DATABASE [AdventurePark] SET NUMERIC_ROUNDABORT OFF 
GO

ALTER DATABASE [AdventurePark] SET QUOTED_IDENTIFIER OFF 
GO

ALTER DATABASE [AdventurePark] SET RECURSIVE_TRIGGERS OFF 
GO

ALTER DATABASE [AdventurePark] SET  DISABLE_BROKER 
GO

ALTER DATABASE [AdventurePark] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
GO

ALTER DATABASE [AdventurePark] SET DATE_CORRELATION_OPTIMIZATION OFF 
GO

ALTER DATABASE [AdventurePark] SET TRUSTWORTHY OFF 
GO

ALTER DATABASE [AdventurePark] SET ALLOW_SNAPSHOT_ISOLATION OFF 
GO

ALTER DATABASE [AdventurePark] SET PARAMETERIZATION SIMPLE 
GO

ALTER DATABASE [AdventurePark] SET READ_COMMITTED_SNAPSHOT OFF 
GO

ALTER DATABASE [AdventurePark] SET HONOR_BROKER_PRIORITY OFF 
GO

ALTER DATABASE [AdventurePark] SET  READ_WRITE 
GO

ALTER DATABASE [AdventurePark] SET RECOVERY FULL 
GO

ALTER DATABASE [AdventurePark] SET  MULTI_USER 
GO

ALTER DATABASE [AdventurePark] SET PAGE_VERIFY CHECKSUM  
GO

ALTER DATABASE [AdventurePark] SET DB_CHAINING OFF 
GO

 

Need a quick restore of a database with the data spread across multiple disks onto a new server, here’s a simple script I wrote. Just replace name and location and you’re ready to go.

USE master
GO

--ALTER DATABASE AdventurePark
--SET SINGLE_USER
----This rolls back all uncommitted transactions in the db.
--WITH ROLLBACK IMMEDIATE
--GO

RESTORE DATABASE [AdventurePark] FROM  DISK = N'\\Teletraan\sqlbackup$\AdventurePark\FULL_BU20150101_000001.bak' WITH  FILE = 1,  
MOVE N'AdventureParkPrimary'     TO N'F:\SQL AdventurePark\AdventurePark.mdf',  
MOVE N'AdventureParkData'        TO N'D:\ARCHIVEDATA\AdventurePark_1.ndf',  
MOVE N'AdventureParkFiles2008_1' TO N'D:\ARCHIVEDATA\AdventurePark_2.ndf',  
MOVE N'AdventureParkFiles2008_2' TO N'D:\ARCHIVEDATA\AdventurePark_3.ndf',  
MOVE N'AdventureParkFiles2009_1' TO N'D:\ARCHIVEDATA\AdventurePark_4.ndf',  
MOVE N'AdventureParkFiles2009_2' TO N'D:\ARCHIVEDATA\AdventurePark_5.ndf',  
MOVE N'AdventureParkFiles2010_1' TO N'D:\ARCHIVEDATA\AdventurePark_6.ndf',  
MOVE N'AdventureParkFiles2010_2' TO N'D:\ARCHIVEDATA\AdventurePark_7.ndf',  
MOVE N'AdventureParkFiles2011_1' TO N'D:\ARCHIVEDATA\AdventurePark_8.ndf',  
MOVE N'AdventureParkFiles2011_2' TO N'E:\ARCHIVEDATA\AdventurePark_9.ndf',  
MOVE N'AdventureParkFiles2012_1' TO N'E:\ARCHIVEDATA\AdventurePark_10.ndf',  
MOVE N'AdventureParkFiles2012_2' TO N'E:\ARCHIVEDATA\AdventurePark_11.ndf',  
MOVE N'AdventureParkFiles2013_1' TO N'E:\ARCHIVEDATA\AdventurePark_12.ndf',  
MOVE N'AdventureParkFiles2014_1' TO N'D:\SQL DATA\AdventurePark_13.ndf',  
MOVE N'AdventureParkFiles2014_2' TO N'D:\SQL DATA\AdventurePark_14.ndf',  
MOVE N'AdventurePark_log' TO N'F:\SQL LOGS\AdventurePark_15.ldf',  
NOUNLOAD,  REPLACE,  STATS = 10
GO