Solved

Setup another database for my application

Posted on 2014-10-26
14
112 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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Mssql SQL query 14 28
MSSQL Frequency of Years From Days Field 2 16
Test a query 23 18
affinity mask in sql server 1 6
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how the fundamental information of how to create a table.

746 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

13 Experts available now in Live!

Get 1:1 Help Now