Setup another database for my application

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.
zolfAsked:
Who is Participating?
 
MikeOM_DBACommented:
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
 
Haris DulicCommented:
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
 
zolfAuthor Commented:
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
WEBINAR: 10 Easy Ways to Lose a Password

Join us on June 27th at 8 am PDT to learn about the methods that hackers use to lift real, working credentials from even the most security-savvy employees. We'll cover the importance of multi-factor authentication and how these solutions can better protect your business!

 
Haris DulicCommented:
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
 
zolfAuthor Commented:
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
 
Haris DulicCommented:
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
 
zolfAuthor Commented:
can you provide me the link or if you have the script to do those tasks
0
 
Haris DulicCommented:
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
 
zolfAuthor Commented:
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
 
Haris DulicCommented:
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
 
zolfAuthor Commented:
This link was not helpful
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.