Solved

Setup another database for my application

Posted on 2014-10-26
14
114 Views
Last Modified: 2014-11-04
Hello there,

I have a server with windows server 2003 on it. I have my application which is using sql server 2008. This server is used by many users and as my users are increasing I have seen drop in performance because the sql server 2008 is providing service to all different users(Web site, desktop application, reports). Now I want to setup another server with its own sql server 2008 and shift some of the users(website and reports) to this new server. My problem is how can I every night at a particular time update this new server's DB. So the next morning the website/reports users will see yesterdays data up to date. Please help me. I have been reading on the web but I am little confused they talk about mirroring and replication.
0
Comment
Question by:zolf
  • 5
  • 5
14 Comments
 
LVL 15

Expert Comment

by:Haris Djulic
ID: 40405723
I assume that you already have some procedures in place for updating those tables required for web and reporting, so what you can do is setup new server and add old server as linked server and then on the new server start those procedures with data source based on your old server by using linked server... or you can complete those procedures on the old server and later copy them to new server...
0
 

Author Comment

by:zolf
ID: 40405727
Thanks for your comments.I have 3 application as i mentioned. the main application(where the users are entering data), the website and the report application. All of them are using the same database. my goal is to reduce the workload on this main server and shift the website and report to another server with its OWN DB. this new server does not have to have data to to minute. It can have one day old data on it. So I want some way to update the database on this new server every night. so next morning the website users and the report users can use this server.
0
 
LVL 15

Expert Comment

by:Haris Djulic
ID: 40405729
Basically, you can prepare those tables on the primary server and then copy/transfer them to new server by using something like this

drop table server2.repo.dbo.table1

insert into server2.repo.dbo.table1
select * from server1.core.dbo.table1
0
 

Author Comment

by:zolf
ID: 40405753
I want to transfer the full database from my primary server to this new server and most important it has to be done automatically in the night when no one is using the application
0
 
LVL 15

Expert Comment

by:Haris Djulic
ID: 40405781
You have to take into consideration size of the DB and the time required to move the file(s)  between two servers.. There are batch scripts which can do the job of automatic backup, transfer and restore ...
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

Author Comment

by:zolf
ID: 40405811
can you provide me the link or if you have the script to do those tasks
0
 
LVL 15

Assisted Solution

by:Haris Djulic
Haris Djulic earned 80 total points
ID: 40405819
No scripts but have the links ;)

http://www.howtogeek.com/50295/backup-your-sql-server-database-from-the-command-line/

serverfault.com/questions/522363/backing-up-database-fully-from-remote-server-and-restoring-to-another-remote-se

etc...
0
 

Author Comment

by:zolf
ID: 40405888
Thanks for those links. Can you please help me to write the script.Lets take this script and elaborate

I have one server with IP - 192.168.0.2 and I take full backup on the D drive(D:\backup) of this server every night.
The new server IP is 192.168.0.3 and I have setup sql server 2008 in this server and want to restore its db with the backup which i have on that 192.168.0.2(D:\backup). How will the belwo script look like.

@ECHO ON

set backupscript=c:\path\backup.sql
set restorescript=c:\path\restore.sql

set livestore=\\server\share
set devstore=\\server\share

set liveserver=server\inst
set devserver=server\inst


sqlcmd -E -S%liveserver% -i "%backupscript%"
copy "%livestore%\*" %devstore%
sqlcmd -E -S%devserver% -i "%restorescript%"

Open in new window

0
 
LVL 15

Expert Comment

by:Haris Djulic
ID: 40405909
Hi,

taking the credits into consideration and maybe for you to understand and learn the script you can check this link:

http://www.codeproject.com/Articles/110908/SQL-DB-Restore-using-PowerShell
0
 

Author Comment

by:zolf
ID: 40406029
This link was not helpful
0
 
LVL 29

Accepted Solution

by:
MikeOM_DBA earned 420 total points
ID: 40422317
After copying over the database one time, why not use  Transactional Replication?

Also you may want to consider Micro$oft's: SQL Server Replication

:p
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Backing up an SQL Transaction Log 11 34
MS SQL - Rotating Values in SQL 9 47
Error running stored procedure 11 14
Sql Query 4 0
This script can help you clean up your user profile database by comparing profiles to Active Directory users in a particular OU, and removing the profiles that don't match.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now