Remote server backup of sqlserver

Go to services.msc and loginn as administrator for sqlserver services


map the drive of the other server to your database server

10.11.233.20\d$

once mapped ,run folowing command in sqlserver query prompt:


EXEC xp_cmdshell 'net use D: \\ip adress of remote server\d$ /user:username password'

eg.

EXEC xp_cmdshell 'net use D: \\10.11.233.20\d$ /user:Administrator atpl@123'


now you can schedule the backup using following command

DECLARE @MyFileName varchar(50)
SELECT @MyFileName = (select 'D:\SQLBACKUP\m2wservices_tr_'+replace((replace((replace((convert(varchar(50),GetDate(),20)),'-','')),':','')),' ','') +'.bak')
BACKUP DATABASE [m2wservices_tr] TO DISK=@MyFileName WITH NOFORMAT, NOINIT, SKIP, REWIND, NOUNLOAD, STATS = 10

to delete the entry for a drive
EXEC XP_CMDSHELL 'net use D: /delete'

JOINS ORACLE

SQL> desc emp
Name Null? Type
----------------------------------------------------- -------- -----------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)




SQL> desc dept
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
DEPTNO NOT NULL NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)

SQL> desc salgrade
Name Null? Type
----------------------------------------------------- -------- --------
GRADE NUMBER
LOSAL NUMBER
HISAL NUMBER



equijoin

select e.empno,d.dname
from emp e,dept d
where e.deptno=d.deptno;


non-equijoin

select e.ename,e.sal,j.grade
from emp e,salgrade j
where e.sal between j.losal and j.hisal;

outer joins

select e.empno,d.dname
from emp e,dept d
where e.deptno(+)=d.deptno;

select e.empno,d.dname
from emp e,dept d
where e.deptno=d.deptno(+);

selfjoin

select worker.ename||' '||'works for'||' '||manager.ename
from emp worker,emp manager
where worker.empno=manager.mgr;

create new user in oracle with separate datafiles and tablespace

create tablespace myatpl datafile 'D:\app\Administrator\oradata\vxmldb\myatpl01.dbf '
size 200m autoextend on next 100m maxsize unlimited;

create user myatpl identified by myatpl123 default tablespace myatpl;

grant resource,connect to myatpl

ALTER USER myatpl QUOTA unlimited ON myatpl;

Data warehouse

one example which could make this term clear, suppose a engineering student collects notes from
various sources like libaray books ,class notes, friends notes etc
and then make a perfect copy of it from which he could get the whole concept,
which will help him making the understanding of concept .

here the integration of all the notes from various sources can be thought of as data warehouse

#############################################################################################


The storage area for processed and integrated data across different sources
which can be operational data or external data
A data ware house allows its users to extract required data,for business analysis
and strategic decision making


conceptualy:A data warehouse is a home for 'second hand' data that originates
in either other corporate applications,or some data source externa;
to your comany

Formaly:A data ware house is a stand alone repositoy of information,integrated from several,
possibaly heterogeneous operational databases.



definition by Ralph Kimball :
A warehouse is a copy of transaction data specifically
structured for query and analyisis.
#######################################################################################
Chatrasal's def
dataware house is data collected from different source databases,data marts, reports,etc.
which are summarized and maintained in multidimensional form .

mysql clustering basics

#ndbcluseter engine is coming with mysql server inside mysql cluster


#pushes data to n/w to processes called datanodes and not stores anything on mysql servers

# create table........ENGINE=NDBCluster;

# Engine NDBCluster ...does it in your n/w

# mysqlserver ------->TCP/Ip-------->Datanodes(data lives here)$$$$most imp part of mysql-cluster

# innodb tables data and myisam data is still going to mysql server

# datanodes are ndbd processes-------like mysqd processes ----one m/c can have more than one datanode i.e more than one ndbd processes

#how records of table going to be store

#new entry-----> application---->mysql server(use ndbcluster engine)----->data nodes

#magic happens in data nodes supose we having two datanodes
*************data stores redundantly
*************stores two time to make sure data is available if somethngs happens n1....>>>>>>.....<<<<<<....n2

#datanodes tell mysql server that thesese are ready

# ndbcluster engine is transactional

#many mysql servers can access the data from data nodes at same time.

#how datanodes are storing the data>>>>?????

partionaing and replicas

######################################################################################
##
#1#-----------partition 1---------->>>>>> Datanode 1(id-1)-------->>>>make replica of partion 1 to Datanode 2(id-2)
#2#
#3#
#4#
as whole it is node gooup
###
#5#-----------partition 2------->>>>>>>>>>> Datanode 2(id-2)--------->>>make replica of partion 2 to Datanode 1(id-1)
#2#
#7#
#8#
no of partions = no of datanodes
hashing mechanism
#################################################################################

if we have more than one node group remember that each gp has have data that is half of one gp and half in other

#if one partener in gp goes down other takes it responsibilty


# cluster confgiguration contains ipadress


# load is eqaly divided amongst datanodes

#when one gp goes down clustering ends




#######################$$$

NODE TYPES


1 sqlnodes ======== MYSQL SERVER

2 data nodes

3 management node ----------------holds configuration-----every other node on cluster needs it-------1st thing to be started
--------------------monitoring logs
4 API------sql nodes are API nodes but otherwise is not mandatory

mysql replication

#steps to do replicatio in mysql

replication of master server on slave server

step 1
configure master
must enable binary logging and also configure a unique server ID.
this can be done by setting follwing parameters in my.cnf file

[mysqld]
server-id=2
log-bin=mysql-bin

take backup of the databases or tables of which you want to set replication

create user with following command (this will be used by slave ,so rember the password and username :) )

CREATE USER 'repl'@'%' IDENTIFIED BY 'slavepass';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';


restart mysql by followig commmand

service mysql restart

mysql>FLUSH TABLES WITH READ LOCK;
take the follwing and write them down somewhere (you will need them later)

mysql>SHOW MASTER STATUS;

+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 333 | | |
+------------------+----------+--------------+------------------+

step 2

configure slave

put following enteries in my.cnf


[mysqld]
server-id=2


export the backup which you have taken on master


restart the mysql services

on mysql prompt run following command

mysql>CHANGE MASTER TO
MASTER_HOST='192.168.163.130', ##ip of master
MASTER_USER='repl',
MASTER_PASSWORD='slavepass',
MASTER_LOG_FILE='mysql-bin.000007',
MASTER_LOG_POS=333;

mysql>start slave



its done..:)



if u want to replicate a spesific table add following in the server my.cnf

replicate-do-table=mani.abc

mysql Installation on linux

Manualy create a text file named my.cnf with following cotents and place this in /etc


[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid


#########useradd -G {group-name} username if gp is not createed



if mysql is already install as default with centos installation then remove it

cd /var/lib/mysql
rm -rf *
rpm -qa | grep -i mysql
rpm -e





cd /var/run/

mkdir -p mysqld

chown -R mysql:mysql mysqld/

cd /tmp

chmod 777 *.rpm

rpm -Uvh *.rpm

cd /var/lib/mysql

mysql_install_db (this is require to to get the default databases with mysql i.e test and mysql)

/usr/bin/mysqld_safe & (this is required as to up the server)


tail -100f /var/log/mysqld.log (to make sure that mysqld works corectly)


service mysql status
service mysql stop

ps -ef | grep -i mysql
kill -9

ps -ef | grep -i mysql (to see the id of mysql)

service mysql restart

rpm -qa |grep -i mysql (to sse all the installed rpms)