DB migration
1 Moving DB to production environment:- Go live (or) deployment (or) DB launch
2 DB refresh: - After DB has moved to production environment but facing some issues .so it has to move from production back to testing server.
3 Consolidation (or) virtualization: - grouping the entire data base in a single server.
Virtualization: logical server for a physical server but act as independent physical server.
PCI— protective & confidential information.
Sensitive data In DB refresh measures
1) DB scratching
2) Security exception
Environment: SQL grid to replication
SQL from
4 Moving DB’s from old hardware to a new hard ware (end of life (EOL))is 5 years for hw
5 Moving DB’s from one location to another location
Data center means place where all the servers are located.
Database dependents:
A) Logins
b) Jobs
c) Objects
Packages—DTS (2000) & SSIS (2005)
2) Steps of migration: to control the users
Exec sp_help_revlogin: this sp will not copy the roles /privileges
2) Script out all the jobs
3) Script out all the objects Sp, users, schemes, roles
4) Packages through import & export 2005exportfile fileimport 2005
If it is B/W same versions we can use imports &exports wizard
DTSDTS SSISSSIS
But in up-gradation there is a tool package migration wizard.
DTS SSIS
5) back up DB on source server
6) transfer the basic files to destination server
MS X copy, robo copy etc...
7) restore the DB on destination server replication depends on network replication difficult to get data on demand
Replication is a complicate process so instead we can create package whenever needed w can run the packages .
8) run all the script to copy the jobs, logins &objects
9) Click the Db owner ,recovery model compatibility, size.
Normally db owner will change from source to destination.
10) Find the orphaned user sp_change_nservs_login’report’
11) Find the orphaned user sp_change_nservs_login’report’
To script out logins run query Exec sp_help_revlogin (user, defined)
It will give all login details then copy the data in a file
N/w path \\server\path....
To change the db owner
Exec sp_change dbowner ‘sa’
Up-gradation:
Migration It is done b/w editions up-gradation It is done b/w version
Up-gradations are two types
1) in-place up-gradations
2) Side–by-side up-gradation
In-place up-gradations: in this both source & destination are in same machine
Disadvantages:-
1) In this if the server stuck between 2005 and 2008 compatibility server can not work until the problems are solved.
2) There must be compulsory down time.
Side–by-side approach:- It is same as migration
Before doing up-gradation we have run the tool upgrade adviser
Error:
1) DTS package store in 4 types
a) Sys data packages
b) File.dts
c) .vbs
2) To edit packages we have to install tool called SQL server 2000 dts design components’
3) No dts packages in yours …………………..
Error has to be done before up-gradations, after up-gradation & anytime.
4) DTS packages are depreciated in SQL server 2000.
5) DTS ---SSIS
How many type of Suspect states?
When’ll ur db move into a suspect state?
There are 3 reasons for the suspect to occur.
a) Hard Ware Failure
b) Corruption of the data in the mdf file or ldf file
c) Unable to perform the recovery with in an ample of time.
I) Hard Ware Failure: How to resolve the situation?
1) Replace the old hard ware with new hardware
2) Push ur db into emergency state.
What is the use of emergency state?
It’ll allow you; to perform the activities i.e the db will be online.
3) Backup the tail log with no_recovery cmd: Backup log dbname to disk =’ ‘ with no_recovery /no_truncate.
4) Restore the recent FULL Backup with no_recovery.
5) Restore the DIFFERENTIAL Backup with no_recovery.
6) Restore the all the TRANSACTION Backup with no_recovery.
7) Restore the LAST TRANSACTION Backup with recovery.
8) Sp_resetstatus ‘dbname’
II) Corruption of the data in the mdf file or ldf file:
Whenever we have the turn pages, the data will be corrupted.
1) Replace the existing db will all the backups
2) Restore the recent FULL Backup with no_recovery.
3) Restore the DIFFERENTIAL Backup with no_recovery.
4) Restore the all the TRANSACTION Backup with no_recovery.
5) Restore the LAST TRANSACTION Backup with recovery.
6) Sp_resetstatus ‘dbname’
III) Unable to perform the recovery with in an ample of time.
If we perform the recovery on a set of db’s and if there is a db which is still is recovery state even after an ample of time then that db will be in suspected state.
How to resolve the above problem?
Restart the server again & run the cmd sp_resetstatus ‘dbname’
Where will you get find the reason for which the db is SUSPECT STATE?
From the logs, we will get the reason
By Recovery means III type
By Hard ware means II type
By Corruption means I type
REPLICATION
Replication: - We have 3 servers
Publisher: - it is a primary server & source db’s.
Distributer: - it is a main server, which will take care of replication. It’ll act as an intermediate b/w publication & subscription db’s
Subscription: - it is a destination server & destination db’s.
What type of data can be replicated /moved from 1 to another server?
1) Complete data can be replicated
2) Selected no, of rows can be replicated
3) Selected no. of columns can be replicated
Articles:-mostly all agents will run in distributer server. It’s like a JOB.
Agents: - Mostly all agents will run in distruster servers. It’s like a job
Example:-When do we use the concept of replication in real time
a) Super market
b) Reservation center
c) SQL server –oracle or wise versa.
Replication topologies:-
We have to identify the type of the topologies.
Topologies:-
1) Publisher, subscriber with local distributer:
We need only 2 server i.e. publisher subscriber server.
Local distributer: - Distributer is a part of publisher server only.
2) publisher ,subscriber with remote distributer:
We need 3 servers here.
Remote distributer: - additional server called distributer is added.
3) Central publisher, multiple subscriber:
Real time:-super market, tickets reservations.
4) Multiple publisher, central subscriber:
ETL:-they will collect all the data from different & maintain the data.
5) Hybrid:-A combination of any of the above 2 typologies is called as HYBRID.
What is the main intention of using replication?
Ans) Failover is not required, and all the users can access the data.
Types of replication:
1) Snapshot replication
2) Transactional replication à Trancational with Update Subscriber (In SQL 2005, we have some enhancements for transactional replication)
à Peer to peer
3) Merge replication
One way of data flow: - The data can be flown publisher to subscriber
Two Way of data flow: The data can be flown from publisher to subscriber & subscriber to publisher also.
1) Snapshot replication:-
There us an agent created in distributer db that is called snapshot agent .this same structure is replicated into source db. It will initialize the schema on sent it will identify the selected data i.e. bulk data from p db to distributer db .
it is a single time replication...
When can we replication /go for snapshot replication?
When ever we don’t want the changes done on the publisher server to be replicated in the server & when ever we want to move the data which is bulk in size.
2) Transactional replication:-
We use this type of replication mostly. Transactional means, the data modified updated in the p db, then with the concept of transactional replication we can update/move the changes done in p db into s db.
Log reader agent:-
To scan the log file of the entire db & identify the changes done on the replicated objects
&these will be moved to distributor db & from here these will be moved to subscriber db.
We have to schedule in such a way that the log reader agent works for every 1 min.
Log reader agent:
It is located in publisher db only but not in distributer db.
3) Merge:-
It is a 2 way kind of replication.
In this type, we can perform the tasks like updating the data & modifying the data present in both the server I.e. publisher & subscriber servers.
For example:-
If we have to add 2 rows in publisher db & later if we add 3 rows in subscriber then both the db’s will MERGE & after that we will sec the result of 5 rows.
We can use the concept of Merge in case of adding or deleting the data. But updating or modifying the data is not advisable.
Conflict resolution priority:
When ever we are struck with the wrong Input between the publisher & subscriber db’s then we will use this concept.
We don’t use the concept of MERGE due to the above problem.
Transactional with updatable subscription:-
It act as a two way dataflow .when ever we push the data from publisher to subscriber, we can update the data present in subscriber and then, the change date will be flown to the publisher db. So it’s a 2 way dataflow.
Options:-
a) With immediate: - By using this options the updated data present in the subscriber db will immediately flown to the publisher db.
b) Queued:- By using this options , the updated date in subscriber db will move in the form of a queue into the publisher db.
Queue reader agent :- This agent will identify the updated data in subscriber db & then the data will be flown /moved to publisher db in a line –by-line or queue from .it is present in distributer server, but it will collect the data from subscriber server.
Log sipping Replication
No new db Creates new DB in sys, DB Distributor
No need to change any table All replicated table should have primary key
Creates T-Logs, Copy transaction log to distention
and restore them Need to deal with identity column triggers
Destination DB would be in stand by mode or no
recovery mode Creates new sp 3
User would not be able to connect during restore Latency should be in sec
-------------------------------------- Sends truncations
-------------------------------------- User can connect to destination DB’s
LOGSHIPPING:
High availability:-
Prevent downtime by maintaining two servers which are in sink with each other .Both the servers must be in sink ,as if 1 servers is failed ,then we can use the other server meantime.
Types of high availability:-
1. Log –shipping:-
How to make /update the changes made in server 1 into server 2 i.e. synchronizing the server1 with server2.
For this, we use log-shipping concept. All the log backups must be taken for every 15 mins in server1 & we need to restore into server2
What are the components used in log –shipping?
1) primary server:
a) Local path: - All the backup files will be generated into these locations.
b) Shared path: - In order to access the data by other servers, we need to store the data into these locations.
Backup:-
2) Secondary server:-
No recovery:-The server 2 must be in restoring state i.e. No_recovery, so that the users can’t access the server2.else the LSN # will not be in order.
Stand by: - it can allow the access to run the cmd’s sever while the restoring cmd is running.
What is the mechanism of log –shipping?
1 Backup Job: - We will take the backup of server 1.
2 Copy Job: - We will copy the data from server1 into server2.
3 Restore Job: - we have to restore the coped data in server2.
What is the concept of failover?
Handover the load to the next available servers.
What are the types of failover?
1 Automatic: - The system i.e. window or SQL server will identify the failure and it will automatically hand over the load to the next available server.
What are the advantages, if the automatic failover is ENABLED?
1 The data loss will not be there
2 There will be no downtime i.e. time taken will not be there.
2 Manual Failover:- If there is any failover, then server1 i.e. primary to server2 i.e. secondary server.
What are the disadvantages, MANUAL FAILOVER?
1 THERE WILL BE HUGE DOWTIME
2 DATA WILL BE LOST
What are the steps to perform for failover?
1 Take the T- log backup of the primary db with no –recovery.
2 Restore db dbname with recovery
3 All he backups taken on primary server should be applied / restored on the secondary server.
4 Disable &drop all the JOB’s
5 Reconfigure log-shipping by keeping the 2ndserver as primary server
Disadvantages of log-shipping.
1 huge data loss
2 downtime is more
3 The data present is primary &secondary server is not in SYNC, they seem to be in SYNC.
After changes of log-shipping
After Role change:
1. We need remove current maintained plan available on current primary server.
2. Detach the secondary database on current secondary server and take the Transanctionlogfile and datafile backup related to the secondary database.
3.remove the database on secondary database on current secondary server.
4.remove the maintenance plan which is related to log shipping on current secondary server.
5. create new maintenance plan on current primary server for log shipping (options for destination database is Create New database on secondary server)
6. Check the dts package on current primary
In order to overcome the above disadvantages, a new concept is enhanced, it’s called Db-mirroring.
What is difference b/w log-shipping &DB-Mirroring?
Log shipping db mirroring
1 - Many 1 ) one ----one
Primary – second primary secondary
WARN standby 2) hot stand --by
JOBS used here supports MANUAL failover 3) no usage of JOBs
Stand by option can enabled 4) DB can’t be put in stand by, it has to be always in mode
In log-shipping the servers 1&2 are in SYNC, by performing the backup, copy & restore JOB’s, but in case of DB-mirroring we don’t use JOB’s instead we use network which will copy, restore into the server 2.
And in this process, the db’s will be in SYNC and hence we can over come the disadvantages of log- shipping.
MIRRORING
Can we enable automatic failover in DB-mirroring?
Yes, it can be enabled
What are the advantages if automatic failover is enabled?
1 auto failure detection
2 We can know the time taken to detach failover.
3 data loss will not be there ,as both the servers all always is SYNC
4 no downtime &minimal downtime
components of DB –mirroring;-
We have 2 servers & we name then as principal & mirror servers
We have to perfume FULL & LOG backup in principal server for mirror server, we need to resolve with NO-recovery
Witness server :-it is like a many for server with extra functionalities like it will initiate the AUTO FAILOVER .in order to initiate the auto failover ,the within server has to perform heart beat
What are the 2 functionality of witness server?
1) Heart beat: - pinging the server .every 15 sec it will ping.
We can change the ping time also
2) Quorum: - communication b/w the server. & will maintain the communication b/w the 2 servers.
What is the port used to establish the mirroring?
Ans) 5022
Endpoint name:-By default “mirroring “we can change/rename the endpoint name
Unsent log Portion ((opp) Vise versa) : - (We can’t read the unsent data). It is located in the principal server. When ever the mirror db is down, the data will be stored in unsent log portion & when ever the mirror is ONLINE, it will restore task & hence both server will be in SYNC.
What are the MODES of DB-MIRRORING?
1) SYNCHRONOUS: - keep the db’s in FULL SYNC. A transaction is performed in principal server & it will be committed in servers 2.The server 2 is committed & it will send ACK to server 1 & then it will send ACK to user, by this ,the user has to WAIT .(always commit changes at both the principal & mirror)
2) ASYNCHRONOUS: - There the user does not need to wait for commit the changes at the principal & then transfer them to the mirror.
1) HIGH AVAILABILITY MODE: - SYNC (+ )witness, Disadvantages:-performance is low.
11) HIGH PERFORMANCE MODE: - Asynchronous i.e. performance is GOOD.
Disadvantages: - 1 auto failover is not done here
2 There is no use of witness server
3 we go with HIGH performance mode
111) HIGH SAFETY MODE: - SYNC, without witness. We have to maintain quorum, if we want sync.
By default, the mirroring will be in HIGH SAFETY Mode only, next day we have to change it to HIGH PERFORMANCE.
Since there is no witness server, and we have 2 servers only .if there is any failure in any 1 of the server, the other server will also get failed as there is no other server in order to use the concept of quorum, for the data to be in SYNC .
(FULL is only allowed. Simple & BULK backup are not allowed primary &secondary should be with same name log backup must be there.)
DB-mirroring configuring steps:-
1) Check out the type of recovery of the db in order to perform db mirroring FULL - recovery must be there
2) Take a FUILL BACKUP, followed by a log BACKUP of the db.
3) Go to start àrunàtype \\servername \d$ for files transferring form primary to secondary servers.
All the backups’ data must be pasted into the secondary server from primary server.
4) Restore with no_recovery (FULL back up &log backup )
5) Click on the db name properties-> mirroring->will get a wizard ànext àwill ask for witness server à next primary server details must be given à click next à mirror server s details must be given, before that we need to connect, so click connect.
Server accounts:- As both the servers are is same domain, we need to fill any default. Just like on next
We will get the summary. Read &click FINISH.
We can configure the db mirroring from 2005, service pack 1.
Commands:-
If we want the mirror to be in suspend
1) alter db dbname set partner suspend /resume
2) If we want to remove db mirroring à alter db dbname set partner OFF
3) We want to overcome the failover I.e. automatically bringing up the 2nd server à alter db dbname set partner failover
4) when the db mirroring in high performance & if we want to bring up the server 2 immediately à
Alter db dbname set partner failover with face –allow –data loss
How will you change the mode?
A) HIGH PROTECTIVE à alter db dbname set pointer safely =’FULL’
B) HIGH PROTECTION TO HIGH PERFORMANCE àAlter db dbname set partner safely=’OFF’
We have to RUN all the cmds, in primary server as the secondly server is in recovery mode
Security:-
Three level of security
1) Server level
2) Db level
3) Object level
1. Server level:-connecting from logical account.
2. Windows logins:-using server name to connect.
3. SQL server: - separate username & pwd .
Window is the user accounts stored in active directory.
SQL server login are stored in syslogin in master db.
When do we use these logins?
The processor of validating the user account s are called authenticating
Two types of authentications
Window authentication: - Checks only in active directory
Mixed mode: - These owe active directory & syslogins.
Create logins: - Right click ànew login à name à server role
Sys admin’s –any steps you want to do in SQL.
Server admin: he can install, configure, he can control the server (drive space, account policy)
Security admin: can all to monitor any blocking, can kill any process.
Commands: =-
1) For granting db to user.
2) Exec sp_addrde member ‘wename’ , ‘rdename’
Access the server à server role
Acess the db àdba role
Creating of logins script
Exec sp_addlogin ‘loginname’ , ‘pwd’ , defaultdb,default language’.
Server role (granting the server role)
Exec sp_addsrvrdemember ‘login name’,’role name’.
Diskadmin: We have the control of storage drives when ever add the space.
Db creator: - Creates, alter ,restore the data base along with backup.
Bulkadmin: - when ever the bulk operations are going on , he can run the bulk recovered.
Every login mapping with user account.
Db _ security admin: - We can do anything on db, creating grant control the db level security.
In order to control the object, we have 3 options;
1) Grant – permission
2) Revoke – withdraw
3) Deny –completely deny.
For stored procedure:-We use “execute” key cmd: Grant execute on sp_help_revlogin to test users
For index:-We use alter & drop
For tables: We can perform delete, update, insert, select, drop, alter..Etc. Cmd: Grant insert on employee to user
What are the principal?
It is entry which is waiting for the permissions in order to access the securities. Logins, user accounts are some principals.
What are permissions?
The privilege to access the securable.
How can we give permissions to the below?
Server logins
Db database
Grant object
Revoke
Deny
What are securable?
For which, we are providing the security.
Principal’s principles securable
Logins server logins
User’s db db
Groups grant objects
Revoke
Deny
What can “dbadmin do?
He can run all the cmds & edits the cmds.
Control the jobs:-
SQL agent’s operator role
SQL agent user role
SQL agent reader role
Target servers role
What are the difference b/w schema’s role members?
Schemas: - It is a predefined set of values
Role: - In additional, if the user requirement is more then we can choose role
Login will be mapped with 1 user account with I n a db?
Instead we can group some set of users account and can map 1 login to that group.
Maintains plan:-
It is a collection of take/jobs either on a single db or on a collective db’s.
Go to managementmaintains planright click on it maintain plan wizardnext give detailsmaintains plan wizard next given details
What are the maintenance tasks?
1) check db integrity
2) shrink db
3) recognize index
4) update statistics
5) clean up history
6) execute SQL server agent
7) rebuild index
8) Back up (full)
9) Backup (diff)
10) Backup (T log)
if the segmentation is low i.e. 5 to 10 % then we can perform RECOGNIZE.
we don’t use the “shrink db”.&we can’t choose recognize index &rebuild index at a time i.e. same time.
Up-gradation:- changing /upgrading the version to new version is CALLED UP-GRADATION.
ADVANTAGE OVER 2000 FROM 2005
1) db mirroring concept is applicable
2) Maintaining stronger & security is high
3) Better development solution
4) Better business development solution
Methods of upgrading
1) In- place upgrade:-
It will automatically upgrade on instance of sql server 2000 for 7.0 to SQL server 2005. We need not copy the data. The server will be OFFLINE, so it requires downtime.
Advantages:-
1) easiest way to upgrade
2) it’s a automate & process
3) No additional Hardware requires
4) There is no need to change the name.
Disadvantages:-
1) We can’t upgrade signal database, we have to upgrade all db’s
2) The instance will be OFFLINE; hence downtime is more i.e. very complex to roll back
3) We can’t COMPARE
2) Side–by-side:- we have to manually move the data file & other object to new instance. A new instance is created & the old instance runs in parallel to the old legacy db.
Advantages:-
1) Since the old db is ONLINE, there will be no downtime.
2) We can move how many or what ever db’s we want.
3) It is a softy method.
4) For production environment, we use this method only.
Disadvantages:
1) u may need additional h/w ,
2) manual interval is required
3) More time is required.
How to RUN the “upgrade adviser wizard”?
Software server’s redistupgrade adviser setup next
The db’s (data transforming server) are no longer existing in 2005 & 2008 ,there will be a problem in upgrading the dts from 2000 to either 2005 or 2008.
How to you upgrade the dts from 2000 to either 2005 or 2008?
using the package wizard ,we can upgrade the dts.
